Re: [eclipselink-users] MySQL's datetime and time-zones?

[cowwoc <cowwoc@xxxxxxxxxxxxxxxx>]

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--tp21006801p21026710.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.