| Bug #774 | Double timezone conversion when using setTimestamp | ||
|---|---|---|---|
| Submitted: | 1 Jul 2003 11:00 | Modified: | 2 Feb 2005 17:32 |
| Reporter: | Dmitry Andrianov | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S2 (Serious) |
| Version: | 3.0 | OS: | Microsoft Windows (Windows) |
| Assigned to: | Mark Matthews | Target Version: | |
[1 Jul 2003 11:06]
Dmitry Andrianov
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");
[15 Jul 2003 10:36]
Mark Matthews
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.
[2 Feb 2005 17:22]
Jesper Matthiesen
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..
[2 Feb 2005 17:32]
Mark Matthews
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.
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.