Sorry for being unclear, let me retry: In Middle-earth, the user enters 2020-10-10 00:00, which the server (based in Elbonia, but aware about the Middle-earth's TZ) correctly translates to x = new java.sql.Timestamp(1234568000L) and stores via ps.setTimestamp(1, x). For a TIMESTAMP column, MySql uses seconds since the Epoch, so it stores 1234568. H2 stores it as the server's local datetime, e.g., 2020-10-10 12:30.
When now Middle-earth's DST rules change, H2 works correctly, and MySql is off. But when Elbonia's DST rules change instead, the reverse is true as the stored local Elbonia's datetime corresponds with e.g. 1234568+3600, which is 2020-10-10 01:00 in Middle-earth. So what I'm claiming: There are also cases when storing seconds since the Epoch is correct and storing local datetime is not. This happens only when multiple timezones are in use, but it happens. No matter how you store the data, there's no perfect solution. Assuming multiple timezones and DST rules changes, the problem can be avoided only by avoiding java.util.Date and all java.sql.* types as they all are UTC based. I'm not sure if I'm clear now... I hope I'm not talking non-sense. Regards, Martin. On Wed, May 7, 2014 at 6:35 PM, Thomas Mueller <[email protected] > wrote: > Hi, > > Sorry I don't understand. > > PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) gives you >> the milliseconds since the Epoch without any timezone. It may be the result >> of converting some future date in Middle-earth. Now the in Elbonia residing >> server stores it as its local time and after the Elbonia's government >> changes the DST rules, you'll be off (while storing the UTC works). >> > > No, storing the UTC is exactly what _would_ cause a problem. If the local > time is stored instead (whatever x.toString() prints), then there is no > problem. This is what H2 1.4.x is doing (it doesn't store the string, but a > number that represents the string). > > Do you recommend using strings for working with dates? Or is there a >> better way? >> > > You can use String or Timestamp in H2 1.4.x. > > Regards, > Thomas > > -- > 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. > -- 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.
