Timestamps with Timezones in Database Tables?: /home/Support/LabKey Support Forum

Timestamps with Timezones in Database Tables?

LabKey Support Forum (Inactive)

View Message

Timestamps with Timezones in Database Tables?

slangley

2012-11-08 10:38

Status: Closed

I read in a couple of old tickets, referenced below, where you decided not to use timezones with dates & timestamps. For us, we see an advantage with using timezones in that it makes data sharing simpler and more consistent. Would we run into any problems if we used timestamps with timezones in our external schemas? Any plans to support timestamps with timezones in the future directly in LabKey?

Thanks.

Scott


https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=8521&_docid=issue%3A8521

"Everyone decided that the server should be timezone-less. No timezone indicator returned from the server now."


https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=14706&_docid=issue%3A14706

"Using DatatypeConverter seems reasonable for now, and we can look at making parseDateTimeUS() handle this case if we want.

Here's the one thing to be careful of. We parse datetime w/ timezone, but we save datetime w/o timezone (in the current timezone of the server).

In this case we are parsing date w/ timezone. We should be careful not to inadvertently save this as a datetime. We don't want 2011-06-28+01:00 to become 2011-06-27 15:00:00, or something crazy like that. We should probably simply drop the timezone altogether and store 2011-06-28 (server tz). "

Matthew Bellew responded:	2012-11-09 08:58
This is a tricky area. * Java is timezone aware so in dates in code can easily be translated to another timezone for display for instance. * databases typically store datetimes without timezones So we simply translate datetimes to the server's timezone before writing to the database. This all works pretty consistently, and works for most uses. If you connect an external database with a column where the timezone is remembered with the column I expect we should display the "correct" time. However, since we don't carry the 'preferred' timezone around it will be translated into the server's timezone when formatted. It would be possible in the future to switch away from java.util.Date to java.util.Calendar which does remember a timezone, but it would require a bit of testing.

slangley responded:	2012-11-09 13:32
I'll add here the comments of our Andrew Nierman who would also like to weigh in: That may be true, but I’d say the “best practice” is to store dates in UTC w/ the time zone offset and let the client’s timezone setting translate it for them as needed. Here’s a decent SO “question” on this topic: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices and I’d definitely second the SO recommendation to use JodaTime over any of the Java Library time/date libraries.