About Timestamps and Timezone
Prowler
We have recently had a problem with our applications due to users
changing the Timezone on their computers.
While investigating the issue, I had a look at how H2 actually stores
a Timestamp internally and found out the following:
It saves the time in local format by adding the time zone offest
See org.h2.util.DateTimeUtils.getTimeLocal()
and when it restores the time it changes it back to GMT by subtracting
the zone offset.
See org.h2.util.DateTimeUtils.getTimeGMT().
So here are my questions/remarks:
1) The code works perfectly fine as long as the users do NOT change
their Timezone
between server restarts. If someone shuts down our server
app(including the embedded H2),
then change the system Timezone then if I am not mistaken that
will produce the wrong result
when loading back the saved time value as the zone offset might be
different.
2) Since the actual time in Date objects is always represented in
GMT,
why is that conversion needed anyway? Would it not suffice to
simply store the GMT milliseconds directly?
Is there something that I am missing (e.g. some spec requirement)?
Please let me know what you think and if there is a possibility of a
fix or workaround
in case my assumptions are right.
Thanks for your time.
Best regards,
Panayiotis Vlissidis
Rami Ojares
have had some coverage in the standard which is neither correct nor sufficient.
Think about it. The great and extensive Calendar framework that java has
is still insufficient. Check out joda-time for a better alternative (a version of which was
considered as a new calendar version for java 7 and might become that in java 8).
Anyway the jdbc temporal types are based on java.util.Date that has been long ago
deemed unfit for any timezone calculations. And thus most of it's methods are deprecated.
Using temporal types does not only need timezone information but also the locale.
Eg. What is the first day of week?
This has been discussed on this list before and it has been stated that the only way to represent
time points unequivocally is to store them as milliseconds from unix epoch in the UTC.
Nanoseconds could be added there but then the value does not fit inside a long anymore.
Then those times can be converted to any timezone and locale one wishes.
Sometimes it happens inside server (within a query) and sometimes on the client (formatting) a time value.
Note: DATE and TIME types have nothing to do with the timezone. But they can clearly be affected by the locale when parsing and formatting.
My long-term suggestion would be to revise all time related types and functions using joda-time but that would cause problems
for those android folks because the jar is 500k and I know how H2 likes to stay lean and mean.
But a very good temporal implementation could be done with existing java.util.Calendar system too.
A word of consolation is that I don't think any database has a very clear and extensive temporal system.
But that is just my impression.
Here is the justification Thomas gave for the current approach
"Let's say you want to store a log file in the database. Of course you *could* convert the timestamps to UTC. In this case you would get different results depending on the timezone where you open the database file. But in many cases people prefer to *not* convert the timestamp. If you send a log file (that includes a timestamp column) to another country then the timestamp in the log file doesn't magically change. You don't *want* it to change. If it changes when you send the database file then that's unexpected and therefore confusing. Maybe not for you, but for others. That's probably why all databases I know don't convert timestamps when you send the database file to another time zone."
rami
Thomas Mueller
What version of H2 do you use?
> changing the Timezone on their computers.
It shouldn't be a problem any longer. Could you tell me from what
timezone to what new timezone the server was changed, and what date
was problematic, so I can reproduce the problem?
> it has been stated that the only way to represent
> time points unequivocally is to store them as milliseconds from unix epoch in the UTC.
That was how H2 stored dates in older version, but it was very
problematic (exactly because of problems when changing the timezone).
> Check out joda-time for a better alternative
While I agree the java.util.* date API is not nice, and the
implementation is slow, I think that Joda-time will not actually solve
the given problem. Also, java.sql.* still uses the java.util.* date
API.
Regards,
Thomas
Thomas Mueller
I forgot to say, if everything else fails:
- export the database to a SQL script using the old timezone,
- and import th database from the script using the new timezone.
Date / time / timestamps are exported using the local time (_not_
UTC/GMT) so this should always work.
Regards,
Thomas
Panayiotis Vlissidis
@Rami:
You are right about java's bad handling of Dates and Calendar and I am aware of JodaTime
but we need to stay as close to the standard as possible, plus we do not really care about the Timezone
to be stored. All we need is for the UTC time to be properly stored in/loaded from the database irrespectively of the
timezone and from then on we apply the correct DateFormat in order to display the values to the users.
@Thomas
We are currently at version 1.3.155 and from what I see in the trunk the code
still uses the ZONE_OFFSET although you have now promoted it to a constant.
I will try and produce a test case for you.
>> it has been stated that the only way to represent
>> time points unequivocally is to store them as milliseconds from unix epoch in the UTC.
>That was how H2 stored dates in older version, but it was very
>problematic (exactly because of problems when changing the timezone).
matter the Timezone used. The only difference is when displaying the dates, at which point is
where the application has to use the proper Timezone and Locale settings in order to display
the value properly to the user.
Panayiotis Vlissidis
Using local time is always subjective to the current system Timezone, right?
Whereas using GMT always, produces the same result. right?
If not, please kindly explain why not
Thanks
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Rami Ojares
> That was how H2 stored dates in older version, but it was very
> problematic (exactly because of problems when changing the timezone).
You gotta walk me through this so I understand.
If you have a timepoint in UTC then it does not matter in which timezone
you are.
It stays the same.
So if you move the server or change it's timezone, then it should not
have the least bit of difference to that timepoint in UTC.
Now, I do understand that when formatting the timestamp one needs to use
a timezone and locale.
And if the user does not define which timezone and locale he wishes to
use then a default must be used.
And if it is the default timezone and locale of the server which are
used as defaults then changing those defaults will change
the results of formatting the time from it's original bigint representation.
So if some users do not understand this and get into trouble, is that
reason enough to punish those who do understand this
and make their timezone aware applications even more difficult to implement?
So in the future if we would like to please both groups we could have
two different timestamps, with and without a timezone.
This we have already discussed.
But funnily the timestamp WITHOUT the timezone would store the timestamp
WITH the timezone and never be affected by
server or client defaults for timezone but always use the timezone in
which they were created which by the way would be defined
most probably by the server default of which the silly user would not be
aware of.
But the timestamp WITHOUT the timezone would not support giving
explicitly the timezone in temporal functions. It would always stay the
same.
We do start to get into real mess when we start comparing timestamp
WITHOUT timezone and timestamp WITH timezone...
So what we mean in this scenario is that timestamp WITHOUT timezone is
actually timestamp WITH-A-STATIC timezone, right?
- rami
Thomas Mueller
> The UTC time should always be the same no
> matter the Timezone used.
Well, and that's why it is problematic.
Let's say I'm currently in Switzerland, and store the timestamp
"1970-01-01 04:00:00" in the database. If the GMT/UTC value is stored
(GMT is basically UTC, at least in this context) then this is the long
value 10800000. So this value is stored in the database file.
Now the database file is opened on a computer in Australia. The value
10800000 is read from the database file, and displayed on the screen
as 1970-01-01 13:00:00.0.
Well, it's the same "time", of course. But it's kind of unexpected.
Specially if you happen to store things like a birthday. Suddenly a
person is born on another day...
Test case:
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Zurich"));
long t = java.sql.Timestamp.valueOf("1970-01-01 04:00:00").getTime();
System.out.println(t);
TimeZone.setDefault(TimeZone.getTimeZone("Australia/Melbourne"));
System.out.println(new java.sql.Timestamp(t).toString());
That's why H2 now uses the ZONE_OFFSET, but possibly this doesn't
always work correctly... I guess when the date stored is during the
daylight saving period (summertime). I will need to check. Maybe it
would be easier to store the actual string ("1970-01-01 04:00:00")
instead of a long...
Regards,
Thomas
Panayiotis Vlissidis
Your description is correct, but it only has to do with improper formatting of the value displayed to the user.
But the value is still correct. From then on, the application has to find another way to handle Timezones in such cases.
In the case you do store the local time though and the user starts changing the system Timezone
between application starts then you actually have corrupted data as the values of the database are altered.
Anyways, could you add a flag of some sort, so that we can define the way it will work?
Panayiotis Vlissidis
on Timezones and since java.util.Date specifically states that the value is ALWAYS GMT then the
developer should NOT expect the Timezone value stored in a Timestamp column.
I can see the following workarounds for handling timezones:
1) The application developer should use an extra column for storing the Timezone and apply any needed changes manually
2) The database could have a system property for the timezone( I think oracle works this way) and H2 apply it always
3) Have an extra composite custom H2 data type that can handle both timestamp and timezone.
What do you think?
Thomas Mueller
It seems I'm not able to explain the problem. Well, too bad. Another
example: you have an application that stores a list of birthdays. Do
you expect to get the same birthday for the same person irrespective
of the current timezone, right?
Regards,
Thomas
Panayiotis Vlissidis
It is just my opinion that the actual stored value should not be altered and
we should probably find a better way for handling the Timezone offset.
BTW, your example works when it comes to displaying the date but due to the fact that
it alters the data it renders useless the comparison methods(equals(), after(), etc.)
of timestamp. (and yes I know that I should not rely on them, but another programmer has
already coded this way and it is really really difficult to change it now and after some changing of the
system timezones it seems impossible to find what the correct data are).
Anyways, would you consider adding a way to set the timezone of a database as a property instead
of using the one provided by the system at run-time?
Thanks for your time
Panayiotis Vlissidis
one according to the SQL 92 spec (as far as I can understand at least).
For anyone interested see section 4.5 Datetimes and intervals
at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Excuse me for all the confusion that I may have caused
Regards,
Panayiotis
Rami Ojares
> Well, it's the same "time", of course. But it's kind of unexpected.
> Specially if you happen to store things like a birthday. Suddenly a
> person is born on another day...
If you are trying to represent a birthday you should use the DATE type.
DATE type is not dependent on timezone.
If on the other hand you are trying to represent the exact point in time
continuum
when you were born say 1973-16-05 23:15:00 (Helsinki time) and you want to
see what the time was at that point in time in Melbourne then the time
should
change and that point in time does happen on another day in Australia
than in Finland.
Had I been an australian and born at that same point in time I would
have my birthday
one day later.
So even though I do understand your reasons for the current
implementation and am even
sympathetic to the problems you mention I still think that the crux of
the matter is based on
misunderstanding and misusing of the temporal types.
On 17.6.2011 17:05, Panayiotis Vlissidis wrote:
> I also think that since the documentation of the Timestamp class does
> NOT make any reference
> on Timezones and since java.util.Date specifically states that the
> value is ALWAYS GMT then the
> developer should NOT expect the Timezone value stored in a Timestamp
> column.
I solved all my temporal problems just by using bigints instead
timestamp, date and time types.
All I needed to do on top of that was to define enough functions to
handle temporal tasks that work on
bigints. Functions that handle conversion between temporal types (if
needed) and bigints and that can take
timezone and locale as arguments if needed.
On 17.6.2011 17:05, Panayiotis Vlissidis wrote:
> 2) The database could have a system property for the timezone( I think
> oracle works this way) and H2 apply it always
I solved this issue by adding a DatabaseListener that sets the timezone
and locale to H2 jvm that I want.
On 17.6.2011 16:31, Rami Ojares wrote:
> So what we mean in this scenario is that timestamp WITHOUT timezone is
> actually timestamp WITH-A-STATIC timezone, right?
To be gruesomely accurate I would call these two types as
TIMESTAMP WITH_CREATIONTIME_TIMEZONE_DEFAULT and
TIMESTAMP WITH_RUNTIME_TIMEZONE_DEFAULT
That's what we are really discussing here. But we don't see that clearly
because this default timezone is used almost everywhere a timestamp
needs to be parsed and formatted.
Just showing the timestamp to the user needs to use timezone.
Therefore the users think that the time has changed but it was only the
default timezone that had changed.
On 17.6.2011 17:05, Panayiotis Vlissidis wrote:
> 3) Have an extra composite custom H2 data type that can handle both
> timestamp and timezone.
I am doing this already. One column (T1) has a bigint that represents
the timezone. Another column (T2) has a varchar containing the default
timezoneId Eg. 'Europe/Helsinki'.
Say I want to show the date of that timepoint in it's default timezone I say
SELECT DATE_STR(T1, T2)
FROM FOO
The fact that it is so easy to define functions in H2 makes it easy to
define the kind of temporal functions that do the deed.
On 17.6.2011 16:41, Thomas Mueller wrote:
> Maybe it
> would be easier to store the actual string ("1970-01-01 04:00:00")
> instead of a long...
Yes certainly that would make this datetime seem less changing because
it does not represent a universal point in time anymore.
It enumerates a year, a month, a day, an hour, a minute and a second
value. But it is not clear what point in time it represents
since it could be interpreted to be a time in Swiss timezone or in an
australian timezone or even in GMT timezone, right?
It represents a varchar that only changes when updated.
Remember: The value is not the same as one of it's representations.
- rami
Rami Ojares
> To be gruesomely accurate I would call these two types as
> TIMESTAMP WITH_CREATIONTIME_TIMEZONE_DEFAULT and
> TIMESTAMP WITH_RUNTIME_TIMEZONE_DEFAULT
Finally I have figured out what my problem is with the current system.
I don't find it wrong to store the default timezone of the server at the
time when timestamp is generated.
It just keeps the default behaviour of the timestamp more stable because
unless you specify it will
always be represented in the same default timezone.
But what I do protest is HIDDEN INFORMATION.
In this case it is the timezone associated with the timestamp.
Let's say I get a database with timestamps.
Then there is no way for me to know in which timezones those timestamps
are stored.
I just have to reverse engineer that by explicitly showing the time in
GMT and then comparing
it to how it is displayed without specifying a timezone.
Wouldn't you agree that the times would seem even more stable if H2
would always
store them and display them in GMT unless the user explicitly specified
a timezone
with which to parse/format a temporal literal?
Another possibility would be to define a persistent setting, say SET
TIMEZONE = '<java's timezone id>'
This would be the default timezone that the server uses to parse/format
temporal literals.
And since it would be a persistent setting it would only change when
explicitly changed and would thus not
be prone to changes in the underlying OS.
- rami
Rami Ojares
It says:
"Therefore, datetime data types that contain time fields (TIME and TIMESTAMP)
are maintained in Universal Coordinated Time (UTC), with an explicit or implied time zone part."
The timezone of TIME is not clearly defined. It would be understandable if you think
timezone the same as timezone offset. But invoke in java TimeZone.getDefault().toString()
and you will see that timezone is much more than just a simple offset.
Let's say we have time 13:00:00
Let's say it is in timezone Europe/Helsinki.
What is the time in GMT?
Well that depends on what is the date.
But since it is not defined we really can not say.
So TIME is not a point in time but rather a point within a day.
And that point in day does not change when taken to Australia.
It remains the same point in a day. 13:00:00.
Could be any day. One o'clock anyday, anywhere.
So I think we don't need to be stuck too much to a standard that makes logical errors.
- rami
Thomas Mueller
> If you are trying to represent a birthday you should use the DATE type.
> DATE type is not dependent on timezone.
The JDBC API uses java.sql.Time and java.sql.Date, and both extend
java.util.Date. So this is a problem, even if it might not be a
problem within H2 itself. See also
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6772689
In SQL, there are two data types: timestamps without timezone, and
timezone with timezone. H2 only really only supports timestamps
without timezone so far.
Regards,
Thomas