Let's assume somebody in Elbonia (doesn't exist, I know) today stores
a timestamp in the future, 2020-05-03 10:00:00, in H2. H2 would then
convert that to UTC, let's say that's 2020-05-03 06:30:00. When
storing that value, the rules are that this is during the daylight
saving time of Elbonia. One year later, the Elbonia government decides
to not use daylight saving time any longer, and they change the
timezone by 30 minutes. Now, a user of H2 opens the database. Because
of the changed rules, the conversion is no longer the same, and the
displayed value is 2020-05-03 11:30:00, not any longer 10:00:00. I
think that's weird. I would call it a bug.
Let's go over your example carefully.
A string "2020-05-03 10:00:00" is inputted and interpreted in the
timezone of Elbonia.
I would rather describe the result of parsing this string as a long say
2340000000L (NB. I just typed a random integer here)
This is completely unequivocal (if we ignore the occasional leap seconds).
It is milliseconds since UE in UTC.
This will never change.
It does not matter if Elbonia had at that point DST or what.
The transformation from string "2020-05-03 10:00:00" to long 2340000000L
was completely determined by the Timezone information (say
implementation of java.util.TimeZone) at the time.
If it turns out later that the timezone in year 2020 was different and
there was no DST in effect or maybe Elbonia decided to change their
offset completely then it is true that the conversion was done with
inaccurate information.
Therefore IANA's timezone database (olsen) has also historical
information going back maybe in some cases even hundred years.
SimpleTimeZone does not use historical info but one can create easily
enough a TimeZone implementation that utilizes that information.
Going further into history with accurate timezone information is a
fool's game because most people did not put so much effort into accurate
tracking of time say 500 years ago when Europe slowly switched to
Gregorian calendar.
And obviously going into future with timezone information is unpredictable.
But I defer let's deal with your use case.
Usually when someone creates a temporal value far into future it is a
date not an exact point in time (especially not in some timezone because
no one knows yet what the timezone will be in the future).
If someone wishes to point to an exact point in time in the future then
that someone would use UTC so as to get rid of the obscurity.
Usually temporal values made into future are dates (meaning the time
component is omitted).
And dates never have a timezone. Or if you want to talk of a date in a
timezone you need 2 timepoints, the start and the end of the date in
that specific timezone.
So all date and time types should never have anything to do with timezones.
But timestamps MUST have a timezone associated so that we can represent
them as a string unequivocally.
To sum it up:
In your use case the user should input his future timepoint in UTC so
that it would be unequivocal and accurate.
If he wants to point to an undetermined timepoint in the future (a
timepoint that will be in Elbonian timezone "2020-05-03 10:00:00" also
in year 2020)
then for those values I would suggest using the string type and just
storing the string. And maybe storing in another column the name of
timezone that is associated with this timepoint.
And then when year 2020 comes around and he knows what the Elbonian
timezone is at that time then he could parse the timepoint into
milliseconds in UTC.
Storing timepoints in a coordinate system that is fixed makes the
interpretation of data so much simpler.
- Rami
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
