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

[cowwoc <cowwoc@xxxxxxxxxxxxxxxx>]

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