> Is there something that I am missing (e.g. some spec requirement)?
I think the source of this confusion lies in the sql standard. The
temporal types
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
On 17.6.2011 15:23, Prowler wrote:
Hello all,
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
--
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.
