MySQL Bugs: #774: Double timezone conversion when using setTimestamp

Bug #774	Double timezone conversion when using setTimestamp
Submitted:	1 Jul 2003 9:00	Modified:	2 Feb 2005 16:32
Reporter:	Dmitry Andrianov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.0	OS:	Windows (Windows)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
When useTimezone=true option is used, timestamp timezone is converted twice and timestamp value becomes wrong.

To set timestamp column value PreparedStatement.setTimestamp is used which eventually result in PreparedStatement.setTimestampInternal. The latter basicaly does two things:

1. converts timezone of the Timestamp object using TimeUtil.changeTimezone
2. converts timestamp to a string using SimpleDateFormat

The problem is there are TWO timezone conversions. First occurs inside TimeUtil.changeTimezone and second is performed by SimpleDateFormat.format().

Here is example. Desktop uses MSK (Europe/Moscow) timezone while MySQL database server uses GMT. Options useTimezone and serverTimezone are set to "true" and "GMT" respectively.

Currently, there is "2003-07-01 18:00:59" in MSK timezone.

We use

  Timestamp ts = new Timestamp( new java.util.Date().getTime());
  stmt.setTimestamp(1,  ts);

stmt.setTimestamp calls

  stmt.setTimestampInternal(1, ts, TimeZone.getDefault());

this result in TimeUtil.changeTimezone call to convert "2003-07-01 18:00:59" from "Europe/Moscow" to "GMT" timezone. So timestamp becomes "2003-07-01 14:00:59" - four hours subtracted. That is correct.

The following lines use SimpleDateFormat to convert timestamp to a string. Take a look at getTimestampDateFormat which used to construct formatter:

    private synchronized SimpleDateFormat getTimestampDateFormat() {
...
            if (this.connection.useTimezone()) {
                this.timestampDateFormat.setTimeZone(this.connection
                    .getServerTimezone());
...

Note the setTimeZone which sets timezone of the formatter to "GMT". That means the formatter will convert specified time to GMT timezone. As expected, formatter returns "2003-07-01 10:00:59" and this string goes to the database.

Note there was 8 hours total subtracted from the current time although our timezone is only 4 hours apart.

How to repeat:

1. set local timezone to Europe/Moscow

2. Connect to the database using 
jdbc:mysql://HOST/DATABASE?user=USER&password=PASSWORD&useTimezone=true&serverTimezone=GMT

3. Execute some UPDATE/INSERT statement with
  stmt.setTimestamp(1, new Timestamp( new java.util.Date().getTime()) );

4. watch the query which actually send to the DB.

There is simplier way to see the bug. Just make the same timestamp value travel over the MySQL JDBC driver:

            stmt = conn.prepareStatement("SELECT ?");
            stmt.setTimestamp(1, new Timestamp( new java.util.Date().getTime()) );
            rs = stmt.executeQuery();
            while (rs.next())
            {
                System.out.println( rs.getTimestamp(1) );
            }

In the case local timezone differs from the server timezone, you will see the printed value differs from the real time. You can force server timezone to be different (GMT here) by using

            conn = DriverManager.getConnection("jdbc:mysql://HOST/DATABASE?user=USER&password=PASSWORD&useTimezone=true&serverTimezone=GMT");

Thanks again for your bug report. This is fixed for 3.0.9. Totally new date code is in 3.1.0 which also fixes this problem, and is _much_ more performant in multi-threaded environments.

I've had similar issues with getting the value in the db to be GMT.
Using "useTimezone=true" and "serverTimezone=GMT" produced a strange result though..

my experimentation revolves around the following date: 05-Apr-2004 13:01:51 GMT
which is the same as new Date(1081170111000).

inserting that date object into the database without "useTimezone" puts 15:01:51 into the db, which makes sense, since my local time is 2 hours ahead of GMT at that date. When i do use the useTimezone and serverTimezone above, and insert the same timestamp, the value in the db becomes 17:01:51 where it should be 13:01:51...

And another thing:
I don't think a JDBC driver is supposed to take the server timezone into consideration at all when using the setters/getters that take a Calendar object for timezone information, but the MySQL one does.. or maybe it doesnt really use it for anything, but nevertheless it still requires it to be set..

The JDBC spec itself isn't clear on the exact intent of the arguments with Calendar, or what to do when the database has timezone information, but does _not_ store it with DATETIME types.

There is actually an action item pending in the JDBC-4.0 experts' group to clarify the spec.

Our JDBC driver offers many different 'modes', you shouldn't use calendar arguments with useTimezone=true, if you _don't_ want the server timezone to be taken into account...The net effect of useTimezone=true is to apply the server timezone to all DATETIME values.