Intuitively, I would agree with Noel, here. The unix timestamp (plus nano seconds) seems to be a reliable storage format. Instead of going through all the date time hassles by operating on strings, it would be possible to defer any string / calendar representation up to the moment when it is really needed, e.g. for user display, or for date time arithmetic. Most SQL predicates work just the same on timestamp as on standard strings, e.g. = > < !=, and thus BETWEEN, IN, etc.
On the other hand, there are UTC timestamps that cannot be represented via a unix timestamp, e.g. 31 December 1998 23:59:60 (a leap second). Thomas's suggestion with the string representation (ISO 8601, I assume?) looks interesting at first, although even then, you will probably need another internal representation that does not impair search performance. How does Apache Derby represent timestamp in storage? And while we're at it, to make things more interesting, have you ever considered supporting both the SQL standard TIMESTAMP WITHOUT TIMEZONE (your current implementation) and TIMESTAMP WITH TIMEZONE data types? :-) Am Dienstag, 6. Mai 2014 08:25:01 UTC+2 schrieb Noel Grandin: > > 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 <noelg...@gmail.com > > <javascript:><mailto: > noelg...@gmail.com <javascript:>>> 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
