I try to explain my point of view as clearly as possible.
DATE and TIME types have nothing to do with timezones.
Proof:
For Example in EU the DST boundaries are set to be 3am of last sunday of
march and october.
So if you have a date on those specific dates then that date is partly
in winter time and partly in summer time.
Thus it's timezone can not even in theory be determined.
This means that a date value say 2010-05-16 is the same date value
regardless of where you are
on the surface of planet earth (or in the universe for that matter).
It always means the sixteenth of may.
If the place observes a calendar that does not have a year, month day
concepts then
of course that value seems meaningless. So it is safe to say that the types
DATE and TIME are very much bound to the gregorian calendar system.
TIMESTAMP is a completely different type of animal.
TIMESTAMP refers to a point of time in the whole universe that can be
shared by all possible calendar systems.
And if we stick with the newtonian worldview (and do not consider the
consequences of Einstein's relativity)
that point of time happened at the same instant throughout the whole of
universe.
There are 2 things that need definition:
1) origin (the point from which the measurements are started)
2) the unit of measurement (in java's case milliseconds)
The standard is to set the origin to 1.1.1970 00:00:00.000 UTC.
And then express any other time as the distance (in milliseconds)
from the origin.
But there is one more extra requirement.
A type in database needs a literal representation.
It could be just the number expressing the distance from origin (the
amount of milliseconds)
but this would not be very handy when creating sql queries manually or
when representing those
values directly to users.
Better would be to use ISO 8601 standard already defined for us eg.
[YYYY]-[MM]-[DD]T[hh]:[mm]TZ
2000-01-01T01:00:00Z = 1st of january 1am in utc timezone
This is exactly the same value as
2000-01-01T03:00:00+02:00 = 1st of january 3am in finnish wintertime
timezone
The default literal should be in utc format.
But it could be also in the timezone of the server.
One could also have a setting where one could change the timezone in which
queries produce timestamp literals.
But I feel that all of these are just optional nice to have features.
What is truly needed is that all timestamps are points of time where both
the origin and unit of measurement are explicitly and statically defined
so that
all the fiddling related to timezones and literal representation is done
on the client side.
Now if someone wants to write a function say MONTH_NAME(timestamp) then
this method
obviously requires 2 more parameters: MONTH_NAME(timestamp, timezone,
locale)
There could be settings to set the default timezone and locale of the
server so that
these 2 arguments could be made optional.
I did not quite understand Thomas' talk about Date changing when moving
the database over
timezone boundaries. Maybe you meant that if the origin is set to first
of january 1970 in some other
timezone than utc (say in Helsinki winter time) and then the value is
read with the false idea that it
was saved in Moscow time then clearly the value would be misinterpreted.
But if everyone knows that the timepoints are always saved in utc time
then the possiblity of misinterpretation would vanish.
TIMESTAMP with TIMEZONE could be represented the same way.
The timevalue stored in utc but then using the timezone (stored in that
same cell in some format)
to determine the timezone of the literal representation.
I am a little bit against that kind of type because it is needless to
bundle 2 things into the same type.
However there is nothing theoretically wrong with it.
The associated timezone could be easily stored in an other column of
type varchar with notation
(+|-)HH:mm eg. +03:00
The person would get the timestamp and then get the timezone in which he
wants to show the times to the user
and in which he wants to interpret the string that the user inputs.
- Rami
8.10.2010 0:45, Thomas Mueller kirjoitti:
Hi,
I think in most cases it's better to *not* convert the date/time to
UTC (Coordinated Universal Time) before storing, because of following
problem: Let's say you store a birthday in the database. After that,
you close the database, and move the database file to a different time
zone. Then you open the database and read the birthday. If the
date/time is converted to UTC, then the birthday might change, which
is completely unexpected. This is an extreme example (you could say
"birthdays don't contain the time"). But also for other cases it's
more common to store the date/time in the local time zone, so that
moving a database file will not change the value. Please note the
problem is only moving the database file.
I agree in some cases it would be better to convert the date to UTC,
but this you could also do in the application.
I think most databases ignore the time zone by default when using the
"normal" data types DATE, DATETIME, or TIMESTAMP (that means, the
value doesn't change if you move a database file to a different time
zone). If you think this isn't true please tell me which database, and
provide a reproducible test case. I'm very interested to make H2
compatible with other databases (I know some databases support
TIMESTAMP WITH TIME ZONE; this is not what I talk about - H2 simply
doesn't support storing the time zone - I'm talking about the normal
DATE, DATETIME and TIMESTAMP data types).
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 [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.
