[eclipselink-users] [SOLVED] (Was: MySQL's datetime and time-zones?)

[cowwoc <cowwoc@xxxxxxxxxxxxxxxx>]

I finally managed to solve this problem!

Reading
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html
and
http://bugs.mysql.com/bug.php?id=15604 contained the answer.

[3 Feb 2006 23:11] James Berry:

"I argue that Connector/J 3.1.12 is broken for any case except when the
database timezone is the same as the local java vm timezone, because no
matter what else
you do, the connector will always offset the ultimate data into the local
timezone"

This discussion thread ends with a patch that adds the
"useLegacyDatetimeCode" driver parameter. Setting this parameter to false
(the default in the next major version) fixes the bug. Phew!

I suggest you document this prominently in EclipseLink's documentation
somewhere. Please note that this driver parameter was only added in version
5.1.6 and the latest version as of this post is 5.1.7.

Gili


cowwoc wrote:
> 
> I think I got it...
> 
> I tried overriding setParameterValueInDatabaseCall() for MySQLPlatform to
> add this special behavior:
> 
> if (parameter instanceof Timestamp)
> {
> 	statement.setTimestamp(index, (Timestamp) parameter,
> Calendar.getInstance(TimeZone.getTimeZone("GMT")));
> }
> 
> Notice I added a Calendar instance that indicates that GMT time-zone
> should be used, but this didn't help. In fact, my tests revealed that this
> extra argument is totally ignored by MySQL. It seems that the MySQL driver
> always uses the default time-zone of the EclipseLink JVM when interpreting
> the timestamp.
> 
> So if I understand it correctly, here is what is going on:
> 
> - EclipseLink converts Calendar to Timestamp that is meant to be
> interpreted at UTC
> - MySQL always interprets Timestamp at default time-zone
> - As a result, MySQL ends up storing the wrong values in the database
> 
> Seeing as MySQL always ignores the Calendar parameter I would suggest the
> following fix: EclipseLink should construct the Timestamp so it is defined
> relative to the default time-zone instead of UTC. I filed a new bug
> report: https://bugs.eclipse.org/bugs/show_bug.cgi?id=258895
> 
> Gili
> 
> 
> cowwoc wrote:
>> 
>> 
>> 	I just ran some tests and got surprising results...
>> 
>> 1) If I use EclipseLink to set/get Calendars from the database without
>> changing the system time-zone I get back the values I saved. If I
>> retrieve the column value as a Timestamp using plain JDBC I get back the
>> same value. If I retrieve the column value using MySQL client I get the
>> same value. All this confirms is that all three mechanisms see the same
>> value.
>> 
>> 2) If I save a calendar using EclipseLink, change the system time-zone,
>> then retrieve it back I get the wrong value. According to
>> http://dev.mysql.com/doc/refman/5.0/en/timestamp.html "If you store a
>> TIMESTAMP value, and then change the time zone and retrieve the value,
>> the retrieved value is different from the value you stored. This occurs
>> because the same time zone was not used for conversion in both
>> directions. The current time zone is available as the value of the
>> time_zone system variable."
>> 
>> What I find surprising about this behavior is that "select @@time_zone"
>> returns UTC from EclipseLink, JDBC and MySQL client. I don't see why
>> changing the system time-zone should be affecting the data at all in this
>> case.
>> 
>> My main goal in this entire discussion is to find a way to store
>> timestamps in the database in a manner that is independent of the client
>> time-zones. Clearly this isn't working yet. Any ideas?
>> 
>> Gili
>> 
>> 
>> cowwoc wrote:
>>> 
>>> Hi,
>>> 
>>> What's the implication of using EclipseLink to store dates in MySQL
>>> "datetime" columns? By the looks of it EclipseLink stores the date using
>>> the current time-zone. Shouldn't EclipseLink converts dates to UTC
>>> before passing them on to MySQL? Otherwise, what will happen if the
>>> server time-zone changes?
>>> 
>>> Thank you,
>>> Gili
>>> 
>> 
>> 
> 
> 

-- 
View this message in context: http://www.nabble.com/MySQL%27s-datetime-and-time-zones--tp21006801p21027227.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.