Bug #32577 Getting epoch time from datetime/timestamp near DST end
Submitted: 21 Nov 2007 15:40 Modified: 12 Mar 2008 16:44
Reporter: Scott Noyes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Mark Matthews CPU Architecture:Any

[21 Nov 2007 15:40] Scott Noyes
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.
[21 Nov 2007 16:09] Eric Bergerson
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
[21 Nov 2007 16:18] Eric Bergerson
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 :)
[21 Nov 2007 16:51] Valeriy Kravchuk
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?
[21 Nov 2007 17:14] MySQL Verification Team
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)
[21 Nov 2007 23:02] Mark Matthews
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".
[21 Nov 2007 23:47] Eric Bergerson
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
[28 Nov 2007 21:50] Bugs System
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
[28 Nov 2007 22:08] Bugs System
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
[19 Dec 2007 13:30] Eric Bergerson
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.
[19 Dec 2007 14:15] Mark Matthews
> 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.
[19 Dec 2007 14:35] Eric Bergerson
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?
[19 Dec 2007 14:40] Mark Matthews
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.
[31 Dec 2007 14:49] Carl Trusiak
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
[31 Dec 2007 19:54] Mark Matthews
> 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).
[4 Jan 2008 19:35] Carl Trusiak
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
[4 Jan 2008 20:07] Mark Matthews
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?
[8 Jan 2008 17:29] Mark Matthews
The real fix for this (in the server) is documented as a feature request -http://forge.mysql.com/worklog/task.php?id=3744
[5 Feb 2008 0:00] Bugs System
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".
[9 Mar 2008 0:00] Bugs System
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".
[12 Mar 2008 15:49] Eric Bergerson
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.
[12 Mar 2008 16:44] Mark Matthews
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.
[12 Mar 2008 17:40] Eric Bergerson
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
[8 Aug 2008 15:03] Den Orlov
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