Hi all, This is a very good and accurate answer to questions about timestamp and timestamp tz data type uses that occur repeatedly, as doubts and as portability queries from other dbms.
I propose that this explanation, a summary or similar be added as a special note to the documentation on the timestamp data type in the website. Maybe this reduce the never ending threads over this subject. regards, Dario El 08/10/10 07:49, Rami Ojares escribió: > 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. -- 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.
