MySQL's datetime and time-zones?

[cowwoc]

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

[James Sutherland]

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

[cowwoc]

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


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

[cowwoc]

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


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

[christopher delahunt]

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@...>
To: "Eclipselink-Users" <eclipselink-users@...>
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@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[cowwoc]

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" <[e-mail]>
To: "Eclipselink-Users" <[e-mail]>
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
[e-mail]
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
[e-mail]
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[christopher delahunt]

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@...>
To: "Eclipselink-Users" <eclipselink-users@...>
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@...>
> To: "Eclipselink-Users" <eclipselink-users@...>
> 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@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> 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@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[cowwoc]

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" <[e-mail]>
To: "Eclipselink-Users" <[e-mail]>
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" <[e-mail]>
> To: "Eclipselink-Users" <[e-mail]>
> 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
> [e-mail]
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> [e-mail]
> 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
[e-mail]
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
[e-mail]
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[cowwoc]

        I just ran some tests and got surprising results...

1) If I use EclipseLink to set/get Calendars from the database without changing the system time-zone I get back the values I saved. If I retrieve the column value as a Timestamp using plain JDBC I get back the same value. If I retrieve the column value using MySQL client I get the same value. All this confirms is that all three mechanisms see the same value.

2) If I save a calendar using EclipseLink, change the system time-zone, then retrieve it back I get the wrong value. According to http://dev.mysql.com/doc/refman/5.0/en/timestamp.html "If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable."

What I find surprising about this behavior is that "select @@time_zone" returns UTC from EclipseLink, JDBC and MySQL client. I don't see why changing the system time-zone should be affecting the data at all in this case.

My main goal in this entire discussion is to find a way to store timestamps in the database in a manner that is independent of the client time-zones. Clearly this isn't working yet. Any ideas?

Gili

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

[cowwoc]

I think I got it...

I tried overriding setParameterValueInDatabaseCall() for MySQLPlatform to add this special behavior:

if (parameter instanceof Timestamp)
{
        statement.setTimestamp(index, (Timestamp) parameter, Calendar.getInstance(TimeZone.getTimeZone("GMT")));
}

Notice I added a Calendar instance that indicates that GMT time-zone should be used, but this didn't help. In fact, my tests revealed that this extra argument is totally ignored by MySQL. It seems that the MySQL driver always uses the default time-zone of the EclipseLink JVM when interpreting the timestamp.

So if I understand it correctly, here is what is going on:

- EclipseLink converts Calendar to Timestamp that is meant to be interpreted at UTC
- MySQL always interprets Timestamp at default time-zone
- As a result, MySQL ends up storing the wrong values in the database

Seeing as MySQL always ignores the Calendar parameter I would suggest the following fix: EclipseLink should construct the Timestamp so it is defined relative to the default time-zone instead of UTC. I filed a new bug report: https://bugs.eclipse.org/bugs/show_bug.cgi?id=258895

Gili



        I just ran some tests and got surprising results...

1) If I use EclipseLink to set/get Calendars from the database without changing the system time-zone I get back the values I saved. If I retrieve the column value as a Timestamp using plain JDBC I get back the same value. If I retrieve the column value using MySQL client I get the same value. All this confirms is that all three mechanisms see the same value.

2) If I save a calendar using EclipseLink, change the system time-zone, then retrieve it back I get the wrong value. According to http://dev.mysql.com/doc/refman/5.0/en/timestamp.html "If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable."

What I find surprising about this behavior is that "select @@time_zone" returns UTC from EclipseLink, JDBC and MySQL client. I don't see why changing the system time-zone should be affecting the data at all in this case.

My main goal in this entire discussion is to find a way to store timestamps in the database in a manner that is independent of the client time-zones. Clearly this isn't working yet. Any ideas?

Gili

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

[cowwoc]

I finally managed to solve this problem!

Reading http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html and
http://bugs.mysql.com/bug.php?id=15604 contained the answer.

[3 Feb 2006 23:11] James Berry:

"I argue that Connector/J 3.1.12 is broken for any case except when the
database timezone is the same as the local java vm timezone, because no matter what else
you do, the connector will always offset the ultimate data into the local timezone"

This discussion thread ends with a patch that adds the "useLegacyDatetimeCode" driver parameter. Setting this parameter to false (the default in the next major version) fixes the bug. Phew!

I suggest you document this prominently in EclipseLink's documentation somewhere. Please note that this driver parameter was only added in version 5.1.6 and the latest version as of this post is 5.1.7.

Gili


I think I got it...

I tried overriding setParameterValueInDatabaseCall() for MySQLPlatform to add this special behavior:

if (parameter instanceof Timestamp)
{
        statement.setTimestamp(index, (Timestamp) parameter, Calendar.getInstance(TimeZone.getTimeZone("GMT")));
}

Notice I added a Calendar instance that indicates that GMT time-zone should be used, but this didn't help. In fact, my tests revealed that this extra argument is totally ignored by MySQL. It seems that the MySQL driver always uses the default time-zone of the EclipseLink JVM when interpreting the timestamp.

So if I understand it correctly, here is what is going on:

- EclipseLink converts Calendar to Timestamp that is meant to be interpreted at UTC
- MySQL always interprets Timestamp at default time-zone
- As a result, MySQL ends up storing the wrong values in the database

Seeing as MySQL always ignores the Calendar parameter I would suggest the following fix: EclipseLink should construct the Timestamp so it is defined relative to the default time-zone instead of UTC. I filed a new bug report: https://bugs.eclipse.org/bugs/show_bug.cgi?id=258895

Gili

cowwoc wrote:

        I just ran some tests and got surprising results...

1) If I use EclipseLink to set/get Calendars from the database without changing the system time-zone I get back the values I saved. If I retrieve the column value as a Timestamp using plain JDBC I get back the same value. If I retrieve the column value using MySQL client I get the same value. All this confirms is that all three mechanisms see the same value.

2) If I save a calendar using EclipseLink, change the system time-zone, then retrieve it back I get the wrong value. According to http://dev.mysql.com/doc/refman/5.0/en/timestamp.html "If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable."

What I find surprising about this behavior is that "select @@time_zone" returns UTC from EclipseLink, JDBC and MySQL client. I don't see why changing the system time-zone should be affecting the data at all in this case.

My main goal in this entire discussion is to find a way to store timestamps in the database in a manner that is independent of the client time-zones. Clearly this isn't working yet. Any ideas?

Gili

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

[Len Edmondson]

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


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

[James Sutherland]

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.


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

[Len Edmondson]

Here's the work around I'm using.  Recognizing that java.util.Date, java.sql.Date, java.sql.Timestamp are all essentially longs, and what I call universal dates, and that Oracle and MySql DATE and TIMESTAMP types are essentially tuples (y m d h m s etc. ) and are regional dates.  There is a fundamental mismatch.  A region or zone's time rules are required to convert back and forth between regional and universal.  So my solution is to create use a different type, a regional time, and write a converter for it.  Since EclipseLink does not provide support for a regional date time, we use a regional object which Oracle will understand (oracle.sql.DATE), and "tunnel" it all the way through the EclipseLink layer. In the code below, we are converting from regional to regional date, so no zone information is needed.  The complexity comes from coding and decoding to Oracles byte array format described in http://www.orindasoft.com/public/Oracle_JDBC_JavaDoc/javadoc815/oracle.sql.DATE.html.  In this way, we have a what you see is what you get relationship with the database without conversion surprises.

      Len

import java.sql.Timestamp;

import oracle.sql.DATE;

import org.eclipse.persistence.mappings.DatabaseMapping;
import org.eclipse.persistence.mappings.converters.Converter;
import org.eclipse.persistence.sessions.Session;

public class RegionalDateTimeConverter implements Converter {

        public Object convertDataValueToObjectValue(Object dataValue, Session session) {
                if (dataValue == null){
                        return null;
                }
                byte[] d = DATE.toBytes((Timestamp) dataValue);
                // y m d h m s
                return new RegionalDateTime (
                                ((d[0]&0xFF)-100)*100 + (d[1]&0xFF)-100,
                                d[2],
                                d[3],
                                d[4]-1,
                                d[5]-1,
                                d[6]-1
                );
        }

        public Object convertObjectValueToDataValue(Object objectValue, Session session) {
                if (objectValue == null){
                        return null;
                }
                RegionalDateTime dateTime = (RegionalDateTime) objectValue;

                byte[] d = new byte[7];
                d[0] = (byte) (dateTime.getYear() / 100 + 100);
                d[1] = (byte) (dateTime.getYear() % 100 + 100);
                d[2] = (byte) dateTime.getMonth();
                d[3] = (byte) dateTime.getDate();
                d[4] = (byte) (dateTime.getHour() + 1);
                d[5] = (byte) (dateTime.getMinute() + 1);
                d[6] = (byte) (dateTime.getSecond() + 1);

                return new DATE(d);
        }

        public void initialize(DatabaseMapping mapping, Session session) {
        }

        public boolean isMutable() {
                return false;
        }
}

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