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

[cowwoc <cowwoc@xxxxxxxxxxxxxxxx>]

James,

http://en.wikibooks.org/wiki/Java_Persistence/Basic_Attributes#Timezones was
indeed a useful read but shouldn't it give some recommendations on what to
do? It gives useful advice for Oracle but nothing for all other databases.
Ideally it should describe an approach that will guarantee database-neutral
and time-zone neutral ways to read/write TIMESTAMP so that you get back the
correct time even if you write it in one timezone and read it back in
another. At the end it reads: "It depends on if the Calendar is stored as
the GMT time, or the local time, and if the time zone was stored in the
database" but doesn't explain what will happen in each case.

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