[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] MySQL's datetime and time-zones?
|
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
