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

[cowwoc <cowwoc@xxxxxxxxxxxxxxxx>]

My code always persists Calenders with the UTC time-zone. When I ask my code
to persist 1:00pm EST, it first converts it to 6:00pm UTC then passes it
back to EclipseLink. When I check the datebase, I expect 6:00pm to be
displayed but instead I am seeing 1:00pm. I checked my code and I'm sure
it's passing 6:00pm UTC to EclipseLink but when the SQL insert is issued I
see 1:00pm as well as if I issue a SQL query against the database myself (I
see 1:00pm again).

I hope that clarifies what I mean. If not, feel free to ask again and I'll
try explaining differently ;)

Thanks,
Gili


Christopher Delahunt wrote:
> 
> Hello Gili,
> 
> I am still not sure of the problem.  If you persist 1:00pm EST, what is
> the time displayed in the database and what are you expecting to be
> displayed?  If the database timezone is pst, I would expect it shows the
> time as 10:00 am pst.  
> 
> Regards,
> Chris
> 
> 
> ----- Original Message -----
> From: "Cowwoc" <cowwoc@xxxxxxxxxxxxxxxx>
> To: "Eclipselink-Users" <eclipselink-users@xxxxxxxxxxx>
> Sent: Monday, December 15, 2008 1:02:18 o'clock PM (GMT-0500)
> America/New_York
> Subject: Re: [eclipselink-users] MySQL's datetime and time-zones?
> 
> 
> 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.
> 
> _______________________________________________
> 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--tp21006801p21019254.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.