MySQL Bugs: #32577: Getting epoch time from datetime/timestamp near DST end

Bug #32577	Getting epoch time from datetime/timestamp near DST end
Submitted:	21 Nov 2007 16:40	Modified:	12 Mar 2008 17:44
Reporter:	Scott Noyes
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Mark Matthews	Target Version:
Triage:	D2 (Serious)

Description:
It is impossible to get the correct epoch offset from a datetime or timestamp field when
the time is in the overlap hour at the end of DST. This is similar to
http://bugs.mysql.com/bug.php?id=316 and http://bugs.mysql.com/bug.php?id=23774

How to repeat:
These statements should output values 3600 apart. The same problem occurs when storing
values in datetime and timestamp fields, 

mysql> SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00') -
3600));
+----------------------------------------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00') - 3600)) |
+----------------------------------------------------------------------------+
|                                                                 1194161400 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00')));
+---------------------------------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00'))) |
+---------------------------------------------------------------------+
|                                                          1194161400 |
+---------------------------------------------------------------------+

Suggested fix:
Datetime values should include the timezone, or timestamps should be stored as epoch
offsets, so that a datetime or timestamp represents an accurate single point in time.

I don't agree that the above description directly addresses the issue we had that led to
this bug report.

Specifically, it appears that when your server and connector are in a timezone that
supports day light savings time, there is no physical way to store java.util.Date objects
representing any time in the first hour between 1am and 2am before the clocks are rolled
back at 2am to 1am again on the 25 hour changeover day in the fall.

The reason for this appears to be that even if you associate a date with an argument in a
PreparedStatement, which means on the client side you do NO conversion of that date to a
clock-face date-time, the java connector does one internally before it transmits the date
over to the server.  As a result, the server can not differentiate between, say, the first
1:30am and the second 1:30am.  

The solution for this is not difficult.  Since all times can be properly represented in a
timestamp field, since it is in GMT, the connector needs to be updated to send over the
dates getTime() long value, instead of converting it on the client.  Then on the server
side, this long value can be converted to GMT.  This solution is independent of the
timezones that either the server or client are in, since the long value is a unique
number of milliseconds since an epoch, that is itself independent of any timezone.

We would be happy to discuss and help in any way to get this resolved as quick as
possible.

Thanks

Please Note:  The suggested solution above does not require any API changes or language
changes.  As such, I hope that makes a timely fix of this nature much more attractive :)

Thank you for a problem report. Please, specify some more details on how to repeat (server
version, OS, timezone settings etc). I was not able to repeat the behaviour described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00')
- 3600));
+----------------------------------------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00') - 3600)) |
+----------------------------------------------------------------------------+
|                                                                 1194129000 |
+----------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00'))
);
+---------------------------------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00'))) |
+---------------------------------------------------------------------+
|                                                          1194132600 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

I clearly see values 3600 seconds apart. What am I doing wrong?

mysql> SELECT VERSION();
+---------------------------------+
| VERSION()                       |
+---------------------------------+
| 5.0.44sp1-enterprise-gpl-nt-log |
+---------------------------------+
1 row in set (0.03 sec)

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| system_time_zone | Central Standard Time |
| time_zone        | SYSTEM                |
+------------------+-----------------------+
2 rows in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00') -
3600));
+----------------------------------------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00') - 3600)) |
+----------------------------------------------------------------------------+
|                                                                 1194161400 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00')));
+---------------------------------------------------------------------+
| UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP('2007-11-04 1:30:00'))) |
+---------------------------------------------------------------------+
|                                                          1194161400 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Eric,

I'd love to hear some ideas on how to do this, since as far as I can figure out, it's not
possible for a client to send a timestamp in GMT unless it re-writes it into a BIGINT and
uses the unixtime() functions, since even though you're correct that a TIMESTAMP is
stored internally in GMT, it's *always* represented in the session timezone when sent
to/from the client.

You should be able to deal with this issue *today* by setting the server timezone to GMT,
either with the server configuration, or by sending it with the "sessionVariables=..."
syntax that Connector/J supports, and then telling Connector/J to do the timezone
conversion with "useTimeZone=true".

Mark,
    It is not feasible for us to run the server in GMT. 

    The connector idea sounds great.  I would like to hear more, as I 
don't quite understand the approach.

    Likewise, I would love to discuss the approach I suggested.  

    When can we set up a time to discuss these issues?

    Thanks,

    Eric

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38756

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38758

To the best of my understanding, this patch does not fix the problem.  Since the patch
still relies on the connector sending a string representation of the date to the server,
any server that is in an "America/New_York" timezone will be incapable of storing the
correct date times for the second hour of the 25 hour day each year.

When the server goes to parse the date string received by the connector, since there is
no timezone information in the transmitted string, it can't differentiate between times
in the first hour between 1am and 2am and the second hour between 1am and 2am.

The ONLY way I can think of to fix this is to either:

1.) Send over the timezone information with the string, so it can be properly converted.

2.) Send over the long value, as an absolute moment in time, and generate the date using
the local server timezone on the server side.

I think the second option is the easiest.  The timestamp is already a long value
representing the absolute time, so no conversion is needed on the client side.  There
seems to be a mechanism in place in the connector to send over binary representations of
data, vs. string representations (I saw that in the code somewhere).  All that remains is
for the server to take that long representation when it is received, and format the date
in a form useful to it from that long representation using whatever calendar is available
to it.

This would be most helpful if applied to both datetime and timestamp server data types.

> To the best of my understanding, this patch does not fix the problem. 
> Since the patch still relies on the connector sending a string
> representation of the date to the server, any server that is in an
> "America/New_York" timezone will be incapable of storing the correct
> date times for the second hour of the 25 hour day each year.
> 
> When the server goes to parse the date string received by the
> connector, since there is no timezone information in the transmitted
> string, it can't differentiate between times in the first hour between
> 1am and 2am and the second hour between 1am and 2am.
> 
> The ONLY way I can think of to fix this is to either:
> 
> 1.) Send over the timezone information with the string, so it can be
> properly converted.
> 
> 2.) Send over the long value, as an absolute moment in time, and
> generate the date using the local server timezone on the server side.

Both of those options require changes to the *server*, which isn't going to happen any
time soon. The server doesn't let you send the timezone with the datetime/timestamp
values, and it doesn't recognize integral values as seconds since the epoch, it parses
them out into components of the timestamp.

The patch *does* fix your problem if you tell the driver to use GMT as the *session*
timezone, by passing in "sessionVaraibles=time_zone='+0:00'" as a configuration parameter
for the JDBC connection. GMT is the *only* oft-used, official timezone where time values
that span DST switchover don't have a "shared" representation when expressed in
timestamp/datetime form.

Mark,
  If that is all we have to do, then the fix may very well work.  But it seemed to me
that you had to also run your server in GMT for that to work.  Our server has been
running for a long time in "America/New_York", and we have tons of data.

We were under the assumption that we could not just start running our server in GMT,
without messing up our data.

1.) Does the server have to be in GMT?
2.) If so, what are the implications to our existing data of trying to change over to a
GMT based server?

Eric,

You won't be running your server in GMT, just the *session* that the JDBC driver is
using. In any case, neither option would touch your data, in so much as the value is
*stored* internally as seconds since unix epoch (GMT), it's just *always* represented in
the server (or session) timezone as appropriate.

Scott, I'm a co-worker of Eric's.  I've tested your fix using the Daily build from
12/20/2007 of the driver.  It still will not store or fetch this hour.  I set the session
to GMT and also to -5:00

Here is my test code:

import junit.framework.Assert;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

public class DateTimeBug
{
    private long earlier = 1194154200000L;
    private long later = 1194157800000L;
    SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
    TimeZone nyTimeZone = TimeZone.getTimeZone("America/New_York");
    Connection con;

    @Test
    public void demonstrateBug()
    {
        sdf.setTimeZone(nyTimeZone);
        try {
            cleanup();

            insertTime(1, "Earlier", earlier);
            insertTime(2, "Later", later);

            Record earlierRecord = getRecord(1);
            Record laterRecord = getRecord(2);

            showComparison(earlier, earlierRecord);
            showComparison(later, laterRecord);

            /** The bug is that the earlierRecord does not show the
             * same fastTime as the original earlier long value.
             * This query on the database demonstrates that the internal storage of
             * both of these dates is indentical insteaf of an hour different:
             *
             * select *, UNIX_TIMESTAMP(tDateTime) as fastDateTime,
UNIX_TIMESTAMP(tTimeStamp) as fastTimeStamp from test_date
             */
            Assert.assertEquals(later, laterRecord.fastDateTime);
            Assert.assertEquals(later, laterRecord.fastTimeStamp);
            Assert.assertEquals(earlier, earlierRecord.fastDateTime);
            Assert.assertEquals(earlier, earlierRecord.fastTimeStamp);

            System.out.println("Works");

        }
        catch (SQLException e) {
            e.printStackTrace();
            Assert.fail("Exception Occurred");
        }
    }

    private void showComparison(long originalTime, Record record)
    {
        System.out.println(record.description + " fastDateTime: " + record.fastDateTime +
" Original: " + originalTime);
        System.out.println(record.description + " fastTimeStamp: " + record.fastTimeStamp
+ " Original: " + originalTime);
    }

    private void cleanup()
            throws SQLException
    {
        Statement statement = getDatabaseConnection().createStatement();
        String sql = "delete from test_date";
        statement.execute(sql);
        System.out.println("SQL: " + sql);
    }

    public void insertTime(int id, String description, long time)
            throws SQLException
    {
        Timestamp ts = new Timestamp(time);
        String sql = "insert into test_date(id,description,tDateTime,tTimeStamp)
VALUES(?,?,?,?)";
        //System.out.println("SQL: " + sql);
        System.out.println(description + " date inserted " + sdf.format(new
Date(ts.getTime())));
        PreparedStatement preparedStatement =
                getDatabaseConnection().prepareStatement(sql);
        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, description);
        Calendar cal = Calendar.getInstance();
        cal.setTimeZone(nyTimeZone);
        preparedStatement.setTimestamp(3, ts, cal);
        preparedStatement.setTimestamp(4, ts, cal);

        preparedStatement.execute();
    }

    public Record getRecord(int id)
            throws SQLException
    {
        Statement statement = getDatabaseConnection().createStatement();
        String sql = "select id, description, tDateTime, tTimeStamp, " +
                "UNIX_TIMESTAMP(tDateTime), UNIX_TIMESTAMP(tTimeStamp) " +
                "from test_date where id = " + id;
        //  System.out.println("SQL: " + sql);
        statement.execute(sql);
        java.sql.ResultSet rs = statement.getResultSet();
        Record record = new Record();
        if (rs.next()) {
            record.id = rs.getInt(1);
            record.description = rs.getString(2);
            record.tDateTime = rs.getDate(3);
            record.tTimeStamp = rs.getTimestamp(4);
            record.fastDateTime = rs.getLong(5) * 1000;
            record.fastTimeStamp = rs.getLong(6) * 1000;
        }
        System.out.println(record.description + " Date Read : " +
sdf.format(record.tTimeStamp));

        return record;
    }

    public static class Record
    {
        int id;
        String description;
        Date tDateTime;
        Date tTimeStamp;
        long fastDateTime;
        long fastTimeStamp;
    }
    /*
Run with Timezone set to GMT
mysql> select * from test_date;
+----+-------------+---------------------+---------------------+
| id | description | tDateTime           | tTimeStamp          |
+----+-------------+---------------------+---------------------+
|  1 | Earlier     | 2007-11-03 21:30:00 | 2007-11-03 17:30:00 |
|  2 | Later       | 2007-11-03 21:30:00 | 2007-11-03 17:30:00 |
+----+-------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Run with TimeZone set to -5:00
mysql> select * from test_date;
+----+-------------+---------------------+---------------------+
| id | description | tDateTime           | tTimeStamp          |
+----+-------------+---------------------+---------------------+
|  1 | Earlier     | 2007-11-04 01:30:00 | 2007-11-04 01:30:00 |
|  2 | Later       | 2007-11-04 01:30:00 | 2007-11-04 01:30:00 |
+----+-------------+---------------------+---------------------+
2 rows in set (0.00 sec)

     */

    Connection getDatabaseConnection()
    {
        if (con == null) {

            try {
                Driver driver = (Driver)
Class.forName("com.mysql.jdbc.Driver").newInstance();
                con =
DriverManager.getConnection("jdbc:mysql://[host]/[db]?useLegacyDatetimeCode=true&useTimezone=true&sessionVariables=time_zone='GMT'",
                        "[user]", "[password]");
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        return con;
    }
}

Carl Trusiak
ctrusiak@sescollc.com

> Scott, I'm a co-worker of Eric's.  I've tested your fix using the Daily
> build from 12/20/2007 of the driver.  It still will not store or fetch
> this hour.  I set the session to GMT and also to -5:00

You've also told the driver to use the datetime code with the bug:

"DriverManager.getConnection("jdbc:mysql://[host]/[db]?useLegacyDatetimeCode=true&useTimezone=true&sessionVariables=time_zone='GMT'",
                        "[user]", "[password]");

You need to set "useLegacyDatetimeCode" to "false" as stated in the changelog for the
patch that fixes this bug.

Hopefully in the same changeset (as referenced in this bug), you'll notice the testcase
at the end that demonstrates the fix (and appears to be the nearly the same testsuite you
just posted).

Mark,

I changed the connection parameters to :
?useLegacyDatetimeCode=false&useTimezone=true&sessionVariables=time_zone='GMT'

And retested, I still get failure.  With GMT, the read dates are 11/03/2007 21:30 EDT

When set to UDT of -5:00 the dates are 11/04/2007 01:30 EST

I'm unable to get back the date 11/04/2007 01:30 EDT

Closer inspection of your code shows that you're using the form of setTimestamp() that
takes a Calendar? Why? 

I'll need some clarification to debug this further, since I'm not able to repeat your
results here, locally (I see two different, correct points in time seperated by one hour
in the database for our testcase, which seems to differ from yours only in the fact that
we don't use the prepared statement variant that takes Calendar arguments).

Are you running comparisons by "eye" in the database, or do you say that the testcase
written in Java doesn't work? I'm also not sure by what you mean when your testcase says
"Run with TimeZone set to -5:00", is that in JDBC, or in MySQL, or your MySQL session?

The real fix for this (in the server) is documented as a feature request
-http://forge.mysql.com/worklog/task.php?id=3744

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Mark, Scott...

From talking with Carl, it is clear that Mark's fix will work for the example problem
that Carl and Mark were testing.  However, as a practical fix, it will not work for us at
all. Since the fix involves a rather coarse adjustment to the session managed by the
connector, it will adjust the return values of all dates returned.  We would have to
re-write all of our persistence code, which is not practical.

In retrospect, we should have created our database in GMT timezone, instead of letting it
pick up the "America/New York" zone.

The only correct solution is the feature identified by Mark.  It is clear this was also
the conclusion of the Sql community at large, as it was included in Sql:2003.  Sql uses
clock-face time representation to manipulate dates.  Without including a timezone in that
clock-face description, there is no way to identify the specific moment in time being
described.

I have been informed that this feature was targeted to get into a 6.x version of MySQL. 
It seems to now be targeted at a 7.1+ version of MySQL.  

I urge you to reconsider and adopt this standard as soon as possible.  A 5.x version
would be wonderful.

Thanks.

This is fixed (as much as it can be) in 5.1.6, see the changelog notes with the patch
attached to this bug,or in the driver for more details.

Mark,
  I looked at the changelog, but honestly, don't have the time to figure out what the
implications of the changes are.  Can you summarize for me?  Is it simply the session
solution we have already discussed?  Is it the Sql:2003 compliant solution that is best? 
Something new, that is in between?

If this is not the Sql:2003 solution we all think is best, what can we do to rally for
moving this feature up to a near term release?

Thanks,

Eric

Looks that path introduced a couple of problems:

http://forums.mysql.com/read.php?39,219758,219758#msg-219758
http://forums.mysql.com/read.php?39,219757,219757#msg-219757

Den