Bug #50348 | mysql connector/j 5.1.10 render the wrong value for dateTime column in GMT DB | ||
---|---|---|---|
Submitted: | 14 Jan 2010 21:53 | Modified: | 20 Aug 2015 20:30 |
Reporter: | shaoxian yang | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Connector / J | Severity: | S1 (Critical) |
Version: | trunk | OS: | Any |
Assigned to: | Filipe Silva | CPU Architecture: | Any |
Tags: | dateTime column, DB in GMT zone, JVM timezone different |
[14 Jan 2010 21:53]
shaoxian yang
[14 Jan 2010 22:44]
shaoxian yang
contains test file, sql scripts and executing scripts.
Attachment: bug_test.zip (application/x-zip-compressed, text), 3.52 KiB.
[29 Jan 2010 7:55]
Tonci Grgin
Hi Shaoxian and thanks for your report. These kind of things can be tricky to grasp as nothing's clear and straight forward. But I'll do my best.
[29 Jan 2010 12:03]
Tonci Grgin
Test case for our test framework, working in JDK 1.5.
Attachment: TestBug50348.java (text/x-java), 4.00 KiB.
[29 Jan 2010 12:04]
Tonci Grgin
Shaoxian, as I said, it took whole morning... o Set remote MySQL server to GMT o Start JVM with -Duser.timezone=America/Chicago on CET box (MySQL TZ id 92) mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | GMT | GMT | +--------------------+---------------------+ mysql> select * from bug50348; +----+---------------------+---------------------+ | ID | AD_END_DATE | CREATED_TIMESTAMP | +----+---------------------+---------------------+ | 1 | 2010-12-20 23:59:59 | 2010-01-29 10:27:09 | +----+---------------------+---------------------+ Test case produces: From DB end: 2010-12-21 05:59:59.0 From DB created: 2010-01-29 16:27:09.0 JVM converted TS for GMT: 2010-12-20 17:59:59.0 What puzzles me is this: I expected c/J to convert the date to same value as JVM does (see test case) but it's actually on the "other" side (+6). Now, this might not be a bug but intentional behavior. In any case it needs better documenting. Mark?
[29 Jan 2010 18:43]
shaoxian yang
Hi Toni, It sounds like you and I are on the same page and getting the same test result. Hopefully, we soon know what the reason behind this. Thanks. Shaoxian
[22 Jun 2010 0:04]
Gisella Saavedra
Not just server in GMT. java.sql.Timestamp, when retrieving such a field, has an internal value (long Epoch) incorrect.
[4 Feb 2011 14:28]
Tonci Grgin
Using same test case against latest c/J sources still show a bug for me: o Set remote MySQL 5.1.31 to GMT, check the settings. +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | GMT | GMT | +--------------------+---------------------+ o Set local (CET box) JVM to -Duser.region=US -Duser.language=en -Duser.timezone=America/Chicago o INSERT literal value into DB (2010-12-20T23:59:59.000). Get second value as current_timestamp from server (...`CREATED_TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP..., obviously in GMT) o Time of the test is around 15:00 CET (14:00 GMT, 08:00 CST) o Calculate JVM converted values for comparison: String tzdateTimeString = "2010-12-20T23:59:59.000"; String tzdateTimeString2 = "2011-02-04T14:50:50.000"; String tzstring = "GMT"; java.util.Date date = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS"); sdf.setTimeZone(TimeZone.getTimeZone(tzstring)); date = sdf.parse(tzdateTimeString); Calendar cal = Calendar.getInstance(TimeZone.getTimeZone(tzstring)); cal.setTime(date); java.sql.Timestamp tztimestamp = new java.sql.Timestamp(date.getTime()); System.out.println("JVM converted TS for GMT: " + tztimestamp.toString()); date = sdf.parse(tzdateTimeString2); cal.setTime(date); java.sql.Timestamp tztimestamp2 = new java.sql.Timestamp(date.getTime()); System.out.println("JVM converted NOW TS for GMT: " + tztimestamp2.toString()); o Select the data: 1) useTimezone, JDBC compliant shift and noTimezoneConversionForTimeType set to TRUE INSERTED DB value: 2010-12-20T23:59:59.000 From DB, INSERTED value: 2010-12-20 23:59:59.0 From DB created TS (server TZ, GMT!): 2011-02-04 13:59:48.0 JVM converted TS for GMT: 2010-12-20 17:59:59.0 JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0 2) useTimezone and JDBC compliant shift set to TRUE, noTimezoneConversionForTimeType = false INSERTED DB value: 2010-12-20T23:59:59.000 From DB, INSERTED value: 2010-12-20 23:59:59.0 From DB created TS (server TZ, GMT!): 2011-02-04 13:59:48.0 JVM converted TS for GMT: 2010-12-20 17:59:59.0 JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0 3) useTimezone = true, JDBC compliant shift = false, noTimezoneConversionForTimeType = true INSERTED DB value: 2010-12-20T23:59:59.000 From DB, INSERTED value: 2010-12-21 05:59:59.0 From DB created TS (server TZ, GMT!): 2011-02-04 19:58:01.0 <<< Here, 7 hours + JVM converted TS for GMT: 2010-12-20 17:59:59.0 JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0 4) useTimezone = true, JDBC compliant shift & noTimezoneConversionForTimeType = false INSERTED DB value: 2010-12-20T23:59:59.000 From DB, INSERTED value: 2010-12-21 05:59:59.0 From DB created TS (server TZ, GMT!): 2011-02-04 20:01:21.0 JVM converted TS for GMT: 2010-12-20 17:59:59.0 JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0 5) ALL options OFF: INSERTED DB value: 2010-12-20T23:59:59.000 From DB, INSERTED value: 2010-12-20 23:59:59.0 From DB created TS (server TZ, GMT!): 2011-02-04 14:03:32.0 << GMT value JVM converted TS for GMT: 2010-12-20 17:59:59.0 JVM converted NOW TS for GMT: 2011-02-04 08:50:50.0
[20 Aug 2015 20:30]
Daniel So
Added the following entry to the Connector/J 5.1.37 changelog: "When the time zone on the MySQL server was configured to 'GMT' but the client was in a different time zone, Connector/J would make wrong adjustments for event timestamps when working with the server."