One more clarification question about timezones.
If I select a timestamp then does the associated timezone travel to client.
I mean according to which timezone is the following string created
resultSet.getString("MY_TIMESTAMP_COLUMN");
Is it according to the associated timezone stored in the database or
according to client jvm's default timestamp?
I think that if you say
resultSet.getTimestamp("MY_TIMESTAMP_COLUMN");
then surely the associated timestamp info is lost because
java.sql.Timestamp does not contain timezone info.
And how about in other languages if you use eg. the postgres ODBC driver?
- rami
On 13.5.2010 22:11, Rami Ojares wrote:
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.
