Timestamp value stored in database changes when timezone is changed

[Prashant Bhat]

Hi Thomas,

Timestamp value stored in database changes when timezone is changed.
Is this an expected behaviour or a bug in H2?
To recreate this, I used Asia/Singapore as the system timezone and
created the following test table.

CREATE TABLE TEST( ID BIGINT PRIMARY KEY, CREATEDON TIMESTAMP);
INSERT INTO TEST VALUES(1, '2007-01-01 00:00:00');
SELECT * FROM TEST;
It shows '2007-01-01 00:00:00.0' properly.

Now when I change the timezone to 'America/New_York' and restart the
database and SELECT * FROM TEST; shows '2006-12-31 11:00:00.0'!

I tried to do the same thing with Derby database in embedded mode
using ij tool. It doesn't change the date even after changing the
timezone. (I followed the same procedure as I did for H2)

If I take a backup in one timezone and restore it in another, it works
fine without any change in the dates(because it uses parseDateTime
from DateTimeUtils, I think). But only when the db is extracted and I
change the timezone, the date values are different from the original
one.

Your suggestions as to solve this, will be very helpful.

Thanks and Regards,
Prashant

PS: I'm using latest H2 library and Java6 on Ubuntu704. and I'm
starting H2 Server using the command 'java -classpath .:./h2.jar
org.h2.tools.Server -tcpAllowOthers true -webAllowOthers true'

[tsukasa]

I think this is not a bug, AFAIK it happens in other databases as
well. I would like to have a switch on the database or something as
easy as that to overcome this problem. In Chile there is only one time
zone for all the country, but even on this case, some users doesn't
set their timezone correctly on windows.

[tsukasa]

Ups! well it is actually not that big of a deal for me, now that I
realize, I can format all the dates and it won't be so much time of
work. Im already calling special methods every time with a date since
a couple of weeks, there is only left the final step :)
BTW, i only checked with a small database of 3mb but it is behaving
indeed faster in querys than hsqldb for me. Good job! It is faster and
without a so unclear memory requirement... many thanks :)

[Thomas Mueller]

Hi,

In H2, dates are converted from the local time zone to GMT before
storing them in the database files. When you log in using another
timezone, then the dates are converted again from GMT to the local
time zone (which is different than the original time zone in your
case).

When creating a backup, the SQL statements that are generated are in
local time zone and without timezone information. So when you restore
the database (run the SQL script), dates are 'automatically converted'
to the new time zone because the time zone information is missing in
the script.

I ran your tests with some other databases (PostgreSQL, HSQLDB,
Derby), and they don't seem to convert the dates to GMT before
storing. But maybe I made some mistake when testing it. So H2 does in
fact behave differently than most other databases currently.

It is possible to change the way H2 behaves: always store the local
time to disk, and interpret dates as local time when reading. Is this
what you suggest? I will think about how to implement this without
breaking backwards compatibility.

I think this problem does not have very high priority, as usually
people don't change the timezone, and when using backup / restore,
this does not occur. Still I like to implement it in the best possible
way.

Thomas

[tsukasa]

Hi,

You seem to be talking to the other guy so excuse me :)

> It is possible to change the way H2 behaves: always store the local
> time to disk, and interpret dates as local time when reading. Is this
> what you suggest? I will think about how to implement this without
> breaking backwards compatibility.

you could still only store a long but of a date of gmt zone with the
same day, month and year of the original. Also when retrieving you can
create a Date not with the long, but with the day, month and year of a
Date created with that long and gmt. That would overcome all this
intrincancies right? (hopefully I know what am talking about ja). A
global setting for a db would be ok for me, other alternative is a
DATE_IGNORETIMEZONE datatype ja I don't know about that.

> I think this problem does not have very high priority, as usually
> people don't change the timezone, and when using backup / restore,
> this does not occur. Still I like to implement it in the best possible
> way.

well, not a life or death issue at least for me.

> Thomas