Timestamps are currently stored in H2 in GMT.
Is this still documented somewhere? If yes, where? Actually, this is
no longer the case. Now timestamps are stored in the local timezone.
The reason is to make database files portable. This is like most
databases work.
I can't remember anymore where and when I read it.
But good to know.
A follow-up question, though.
What does it exactly mean to store timestamp with timezone?
Let me elaborate.
A point in time can be very nicely represented as seconds (or
milliseconds or nanoseconds etc.) from the Unix Epoch in GMT timezone.
If do you store it as a string in some predefined format and together
with it a pointer to some timezone definition (which has to contain the
timzone offset, dst, dst switch over datetimes...) ?
To me it would seem that the most portable format is the first one I
mentioned that has no ambiguities and is the most compact format available.
The issue of timezone only arises when someone wants to format the time
to a string reprentation
and when someone wants to parse a timestamp from a string representation.
As I mentioned when using prepared statements one is in full control
over that.
But when one deals with timestamp literals (which are string
representations)
then one needs to take into account the timezone.
Now if I start the h2 jvm with a different default timezone then I
suppose that when reading timestamps
they are formatted to string literals using the timezone that was
associated at insert/update time.
Where does the timestamp initially (at insert time) get it's timezone?
I'm quessing from jvm's default timezone.
Now if I start the jvm with a different timezone and update the
timestamp is the timezone updated?
Maybe it is, maybe it isn't...Does it matter?
I quess not.
How can I choose the timezone with which to associate the timestamp?
I can't because defining the timezone in a literal would create a
monster format.
And timezone info in java.sql.Timestamp is deprecated and invalid.
So it seems that the reason for including the timezone with the
timestamp serves those who do not care (or understand)
timezones and want to have their timestamp string literals look the same
even if their server's administrator happens to change the timezone.
Hmm..maybe this does not have any adverse effects.
But it does seem a bit obscure and redundant.
And seems to serve only those who do not want to be bothered with
timezones but not those who actually need to work with them.
Anyway..I will try to develop my ideas a bit further.
- rami
I think the default timezone and locale would be great additions to add as
properties to the connection url.
Why don't you set the timezone and locale in the environment settings?
Yes. That came to my mind only a bit later...
Then the db user could decide per connection and not per database the
timezone his app is currently operating in.
Could you describe what problem you want to solve exactly? I'm not
aware of a problem currently.
I have to still think about how to deal ideally in situations where one
has clients working with the same time info in different timezones.
So let's leave this issue for a moment.
Cheers,
Rami
--
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.
