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

[James Sutherland <jamesssss@xxxxxxxxx>]

I'm not sure of any JDBC drivers that support the new
PreparedStatement.setDate/Time/Timestamp methods that take the Calendar for
the timezone, but agree that we should support these, although we may need
some platform option to enable the support.  Please log an enhancement
request for this feature.  Also, if you know of any databases/JDBC drivers
that support this correctly other than Oracle, please include these as well.


edmonl wrote:
> 
> I find that javj.util.Date is not mappable to Oracle DATE column. In my
> parlance, Java Date is universal and Oracle DATE is regional. Work around
> this by defining a data type containing integers for year, month, day,
> hour, minute, second.  Use a converter to oracle.sql.DATE using context
> TimeZone, which just tunnels through EclipseLink to JDBC.
> 
> The java.util.Date type known to EclipseLink and so gets special
> treatment. All Dates are converted to Timestamp in EclipseLink and
> ultimately converted to regional form using the default timezone.
> 
> To correct this deficiency and provide proper support, I propose that we
> allow a Calendar, returned by a Converter to be carried forward unchanged
> and when it's time to bind variables on a JDBC PreparedStatement, we have
> the parameters we need for setDate(int, java.sql.Date, Calendar) as below.
> 
> PreparedStatement.setDate (parameterIndex, new
> Date(calendar.getTimeinMillis()), calendar);
> 
> I'm saying the the exisiting conversion code below loses information,
> namely the timezone.
> 
> in Helper.java
> public static java.sql.Timestamp timestampFromCalendar(Calendar calendar)
> {
>         return timestampFromLong(JavaPlatform.getTimeInMillis(calendar));
>     }
> 
>        Leonard
> 
> 
> 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
>>> 
>> 
>> 
> 
> 


-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/MySQL%27s-datetime-and-time-zones--tp21006801p21667358.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.