MySQL Bugs: #49759: java.util.Date gets a Timezone conversion applied in reads but not in writes

Bug #49759	java.util.Date gets a Timezone conversion applied in reads but not in writes
Submitted:	17 Dec 2009 8:04	Modified:	29 Apr 2015 12:02
Reporter:	Fred Janon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.10	OS:	Windows (XP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
- My Grails (Groovy/Java/Spring/Hibernate) application uses java.util.Date objects that are written in the mySQL DB in GMT time since my app converts the string into GMT time.
- The application JVM default timezone is set to 'GMT' using TimeZone.setDefaultTimeZone().
- The application runs on a WinXP with the timezone +8:00
- The 'mySQL server 5.0.45 Community' is configured to use the timezone 'GMT' and 'show variables' reports 'system_time_zone | W. Australia Standard Time' and 'time_zone | +00:00'.
- Using mySQL Query Browser, I see that the dates are GMT as expected in the table.
- Reading the dates back into the application they are offset -8:00 to what they were written.
- if I set the PC to GMT, the dates are written/read properly.
- if I use the exact same application but use the Postgres driver and DB, it works.
- I tried different options for the connection 'useTimezone=false' (which is the default), 'useTimezone=true', serverTimezone=GMT with no change
- Same issue with the 5.1.6 driver, since I originally detected the issue with 5.1.6

Issue:
- it seems that a timezone conversion is applied when reading the Date values from the DB but not when writing in the DB when the defaultTimeZone in the Java client is set to GMT.

Questions
- I don't know anything about the JDBC spec, but what I don't understand is why a timezone conversion is applied for java.util.Date or java.SQL.Date, which is UTC/GMT. No timezone conversion should ever happen on this data type.
- is there any option that I can pass to the driver to solve the problem? I don't want to have to set the host time to GMT, I can't in deployment and I would like to keep the defaultTimeZone to GMT in my Java app as well.

Thanks

How to repeat:
- set the host server to a timezone different from GMT
- set the mySQL server timezone to GMT
- from a client PC with a timezone not GMT, write a java.util.Date (it's GMT) in a table after setting the default TimeZone to GMT in Java
- check that the Date is exactly the same in the DB using the Query Browser
- read the Date back through Connector/J, it will be offset by a timezone: either the server one or the client one, the server and client machines are the same PC in my case, so I can't tell with timezone Connector/J uses. It should be the same as written.

Hi Fred and thanks for your report.

There might be a bug here as we've seen such troubles before. Thus we decided to completely avoid storing DATE information as a MySQL Date, but rather store a java "date" of milliseconds since epoc in a bigint20 field. So, best advice regarding this would be not to store a java Date as a Date, but to store the java Date.getTime() which returns the "long" value.

Now, there is a comprehensive Date test suite in our sources so if you'll just take some time and look into testsuite/simple/DateTest.java and see if it helps. If not, please attach complete test case so I can check.

Mark, could this be related to Bug#49700?

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".

Hi Fred,

This report isn't clear enough to confirm for sure the described behavior, however, it looks a lot like it is related to how and when you reset the reset the system's default time zone. I. e., if you call "TimeZone.setDefaultTimeZone()" before creating connections then all should work fine, but, if you change it after, then you should set the connection property "dynamicCalendars=true" in order to make the driver aware of these changes, otherwise a wrong Calendar instance would be used when creating your date values causing the conversion you are observing.

Thank you,