There has been talks about this issue before.
I suggested that h2 should store timestamp as a long value signifying
milliseconds since Unix Epoch.
Most compact representation of time and leaves no room for
misinterpretation.
For the time being I would suggest route B.
And do all your timezone calculations in java because it seems to get
the timezone issues right.
That's what I do.
If you want timezone aware functions into H2 you can easily create your
own functions that can
utilize java's inherent power to deal with timezones.
- Rami
4.10.2010 17:34, Ray kirjoitti:
Hi!
My project need to support mutiple time zone.But I noticed one
strange thing about H2 that on timezone changing the queried timestamp
was not equaled to inserted one.
Environment:H2 1.2.142,JRE 1.6.0_17,Windows 7 64bit
Here is the scenario:
1.If OS time zone is GMT +8:00(China)
One car accident happened at 2000/1/1 08:00:00 GMT +8:00
3.Now change OS time zone to GMT +6:00(User fly to India)
Expected timestamp queried from H2 supposed to be 2000/1/1 06:00:00
GMT +6:00.
But rather it was 2000/1/1 08:00:00 GMT +6:00.
By searching forum said that H2 will convert and save in GMT format
which should be 2000/1/1 00:00:00 GMT(But H2 console always shows
2000/1/1 08:00:00).
H2 seems doesn't store any timezone infomation.
Such behavior seems break the data consistency and create dependency
on envionment.
Currently I figure out two solutions but would like to hear how
people solve such problem:
Solution A:Enforce application using GMT by setting
System.setProperty("user.timezone","GMT");
So OS timezone won't affect database.The queried timestamp is
always equaled to inserted one.Only need to convert to user timezone
on presentation.
Solution B:Totally abandon TimeStamp type by using Long type instead
and time is represent as 20000101000000 and add another column for
timezone.
I am a little skeptical about solution A . If H2 using different JRE
which has different DST correction.Will the queried timestamp still be
the same as inserted one if some DST changed before timestamp ?
Solution B doesn't have such problem(time is always the perceived
one(presentation state)) .But the query across different timezone have
to seperate to different sql(Solution A don't have such problem).
Eg:Query all car accident within past 24 hours of all country
select * from accident where start>'20000102235959' and
end<'20000101000000' for China,
select * from accident where start>'20000102215959' and
end<'19991231210000' for India.
But in contrary if query don't have to consider timezone,it's more
efficient(Solution A need multiple sql now).
Eg:Query all car accident happened in june of all country
select * from accident where start>'20000601000000' and
end<'20000630235959'
Can anyone enlighten some lights on this issue? It's appreciate!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
