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

[cowwoc <cowwoc@xxxxxxxxxxxxxxxx>]

The column type is "datetime" and no it does not retain any time-zone
information. The SQL statement I am seeing uses the local timezone. I then
opened up mysql client and issued a query myself and sure enough the
returned time is also in the local time-zone. Please note that I configured
mysql to use UTC timezone as mentioned here:
http://stackoverflow.com/questions/309203/how-to-store-a-javautildate-into-a-mysql-timestamp-field-in-the-utcgmt-timezone

I am expecting EclipseLink to:

1) Never use Date.getHour() and other deprecated methods because they will
return the time relative to the current time-zone and according to
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4031021 Date is meant to
only be used relative to UTC.

2) Either: Use Calendar.get(field) to retrieve the hour/minute/etc
information and persist *that* into the database. That is, if I chose to
apply a non-UTC timezone, EclipseLink should persist it accordingly.

Or: Always persist the time in UTC.

But it should never persist the time relative to the current time-zone
unless that's the TimeZone the Calendar is configured with.

- Right now I am trying to persist 1:00pm EST. My code first converts this
relative to UTC, then sends it on to EclipseLink. When I issue a query using
MySQL client I expect to see the UTC time in the database, not in the local
timezone.

Gili


Christopher Delahunt wrote:
> 
> Hello Gili,
> 
> Just to clarify, what is the database type you are persisting the calendar
> object into, and does it keep the timezone info?
> 
> If you are using a Timestamp type in the database (one without timezone
> info for instance) EclipseLink will pull out the timestamp info from the
> calendar.  As timestamps are just wrappers on the long value underneath,
> this is timezone independent anyway.  Unless you have turned binding off,
> the timestamp passed to the driver will not have any timezone information
> - though if you have logging showing the SQL being used, it will display
> the timestamp as in the server timezone since that is a java default.
> 
> When you read back (refresh) from the database, your calendar object would
> not have the timezone preserved, and so the time would appear to be
> shifted into the default server time-zone.  ie 3pm pst would print off as
> 6pm est for me.  
> Is this what is occuring for you?
> 
> 
> Best Regards,
> Chris
> 
> ----- Original Message -----
> From: "Cowwoc" <cowwoc@xxxxxxxxxxxxxxxx>
> To: "Eclipselink-Users" <eclipselink-users@xxxxxxxxxxx>
> Sent: Monday, December 15, 2008 12:18:05 o'clock PM (GMT-0500)
> America/New_York
> Subject: Re: [eclipselink-users] MySQL's datetime and time-zones?
> 
> 
> I think I found a bug. It looks like EclipseLink is persisting Calendar
> wrong. My property getter returns a Calendar relative to the UTC time-zone
> and it issues an SQL insert command relative to the *local* time zone.
> 
> Please verify you can reproduce this on your end.
> 
> Thank you,
> Gili
> 
> 
> James Sutherland wrote:
>> 
>> EclipseLink does not store the timezone in the database by default. In
>> JDBC Timestamps are normally written as a java.sql.Timestamp, which has
>> no
>> concept of a timezone.  You should get back the exact timestamp you
>> write,
>> irregardless of what timezone you are in, Timestamp has no timezone.
>> 
>> EclipseLink does have extended support for store the timezone in the
>> database on Oracle.  It has support for both TIMESTAMPTZ and TIMESTAMPLTZ
>> fields.
>> 
>> I do not believe MySQL supports storing the timezone in the database, it
>> does have a session level timezone, which will be used to convert the
>> timestamp value to and from UTC.  If you are having issues with your
>> timezone, you could try setting the connection timezone using, 
>> 
>> SET time_zone = :timezone;
>> 
>> You could set this server property using an EclipseLink postConnect
>> SessionEvent.
>> 
>> If you are having timezone issues, you could also try using a Converter
>> on
>> your mapping (@Convert).
>> 
>> There is some info on JPA and timezones here,
>> http://en.wikibooks.org/wiki/Java_Persistence/Basic_Attributes#Timezones
>> 
>> 
>> 
>> 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--tp21006801p21017865.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
> 
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 

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