Hi, H2 did store timestamps as UTC. Search for "timezone" in this group to see what problems this caused. With H2 version 1.4, this is no longer the case, and I believe this solves many issues. For example:
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. Because of such issues, H2 no longer stores UTC. The remaining issue is slow conversion between the internal format and java.sql.Date / Time / Timestamp, using java.util.Calendar. That can be solved. Once JDBC uses the Java 8 date time API, java.util.Calendar is no longer needed at all, and that's good. Daylight saving time changes is just one of the problems. Please note that timezone and daylight time saving information may change from time to time, even within the same region. Sometimes even retrospectively (for example, when storing a date in the future, and the rules change after that). When using java.util.Date or java.util.Calendar, there is usually a conversion between the local time and UTC needed, that means timezones come into play. If we store all timestamps in UTC, we need to convert to and from local timezone. The following can occur: if you open a database in a different timezone, the times (and dates) may be different. The same happens if the client doesn't have the same timezone as the server. Most people don't actually need to convert between timezones, they are just interested in the local time (as if no timezones and no daylight saving exists). > I just remember Thomas back in the day defending a position that when changing the timezone of the database the UTC value must change so that the local time stays the same. Yes, the local time must stay the same, otherwise there are all kinds of problems. How the database stores the timestamp value is an implementation detail. H2 doesn't store UTC any longer. > Most people probably bypass the Timestamp type and use Strings for deserialisation... In that case H2 doesn't use java.util.Calendar (any longer). H2 doesn't use java.util.Calender unless if the user uses java.sql.Date / Time / Timestamp. java.util.Calendar and Date does the timezone adjustments. With Java 8, with the new date and time API, those problems should be solved. Regards, Thomas On Tue, May 6, 2014 at 8:25 AM, Noel Grandin <[email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');> > wrote: > Hi > > Thomas, the problem you are talking about only exists when dealing with > date values without time, like "1982-01-01", not for Timestamp values. > > We could store timestamps internally as a long value, defined in the same > way as that System.currentTimeMillis is defined i.e. milliseconds since 1 > Jan 1970 GMT. > Then there are no daylight saving or midnight problems, because by > definition it is unambiguous. > > See here for a fuller explanation: > http://en.wikipedia.org/wiki/Unix_time > http://en.wikipedia.org/wiki/Coordinated_Universal_Time > > Regards, Noel. > > > On 2014-05-06 07:57, Thomas Mueller wrote: > >> Hi, >> >> There are various problems when using java.util.Data / java.sql.Date / >> Time / Timestamp and java.util.Calendar, mainly >> because of the daylight time saving changes. For example, you can't >> assume a certain date (if you don't care about the >> time) is "midnight", because in some timezones, for some days, midnight >> doesn't exist because it's the daylight time >> saving border. It's not a problem in most timezones, but it is in some. >> >> Regards, >> Thomas >> >> >> >> >> On Monday, May 5, 2014, Noel Grandin >> <[email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');><mailto: >> [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');> >> >> wrote: >> >> I'm curious - why do we not simply store date and time in UTC format? >> i.e. in milliseconds since 1 Jan 1970? >> >> That's pretty much a universal format these days, and then we can >> convert to whatever the local time zone is when we convert the value >> to string. >> If necessary, I could create an extra data type to represent time >> like this? >> >> I know I would use it, since that's how we represent all our data. >> >> -- >> 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 >> >> [email protected]<javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');><javascript:;>. >> To post to this group, send email to >> [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');><javascript:;>. >> >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> >> -- >> 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 >> [email protected]<javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');><mailto: >> [email protected]<javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> >> >. >> To post to this group, send email to >> [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');><mailto: >> [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');> >> >. >> >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > 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 > [email protected]<javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to > [email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');> > . > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
