4 ms·

Storing as localtime+timezone is asking for bugs since most people shift timezone twice a year. That makes testing a real problem. Store times internally as UT

by jbert 17y ago

Storing as localtime+timezone is asking for bugs since most people shift timezone twice a year. That makes testing a real problem. Store times internally as UTC (time_t epoch is a good representation, or the DateTime object of your preference), convert as needed at the boundaries of your app for display/persistent storage purposes.

spuz 17y ago
I think the article was trying to point out that it is not possible to use UTC for everything. If for example you store in your database an event to occur in the distant future (e.g. opening ceremony of the 2016 Olympics) in UTC, then the time that that represents can change as rules about timezones, leap seconds and daylight savings change. Also there is the possibility that you actually want to store the timezone information at write-time, rather than assuming a timezone at read-time. For example if you have multiple records storing event times (say the working hours of all your global employees) then storing them only using UTC, you will not be able to retrieve the actual time in the timezone of each event individually. You can only assume a global timezone to apply to all events. When storing localtime+timezone, do as the article recommends. Store the timezone, not the offset from UTC. Store "New York time" not EST or EDT.
- jbert 17y ago
  > When storing localtime+timezone, do as the article recommends. Store the timezone, not the offset from UTC. Store "New York time" not EST or EDT. The problem is that that isn't well defined, as I point out here: http://news.ycombinator.com/item?id=919407 http://news.ycombinator.com/item?id=919407 There are two UTC times corresponding to each moment from 00:00->01:00 25th Oct 2009 "London Time". There are no UTC times corresponding to 01:00->02:00 29th March 2009 "London Time". > Also there is the possibility that you actually want to store the timezone information at write-time, rather than assuming a timezone at read-time. Whether a given time should be displayed in the viewer's localtime or another time is a display issue (I may want to switch between seeing my plane arrival time in localtime of the destination and localtime of my body clock). I don't think it should affect the internal storage format as without help from a strong type system (or some strong coding conventions) coders will intermingle the two types and miss errors in testing either due to geographical proximity or lack of testing over daylight savings boundaries. [All the above saud, the problem of changing DST parameters and future event times is problematic. I think it depends on what you want to be doing with such a time. Note that you'd have the opposite bug if you were storing pre-calculated sunrise time in various locales, so it's an issue of the semantic meaning of the timestamp.]
  - texel 17y ago
    I've dealt with this quite a bit, and you're right- it's difficult to deal with the conceptual impedance mismatch between the concept of a time, and its discrete meaning in persistent storage. I wrote a blog post about a month ago that talks about some issues surrounding the concept of "all-day" events which, by definition, are conceptually ambiguous. http://onehub.com/past/2009/9/28/the_joy_of_calendars/ http://onehub.com/past/2009/9/28/the_joy_of_calendars/ Ah, good times.

by jbert 17y ago

spuz 17y ago

I think the article was trying to point out that it is not possible to use UTC for everything. If for example you store in your database an event to occur in the distant future (e.g. opening ceremony of the 2016 Olympics) in UTC, then the time that that represents can change as rules about timezones, leap seconds and daylight savings change. Also there is the possibility that you actually want to store the timezone information at write-time, rather than assuming a timezone at read-time. For example if you have multiple records storing event times (say the working hours of all your global employees) then storing them only using UTC, you will not be able to retrieve the actual time in the timezone of each event individually. You can only assume a global timezone to apply to all events. When storing localtime+timezone, do as the article recommends. Store the timezone, not the offset from UTC. Store "New York time" not EST or EDT.

jbert 17y ago
> When storing localtime+timezone, do as the article recommends. Store the timezone, not the offset from UTC. Store "New York time" not EST or EDT. The problem is that that isn't well defined, as I point out here: http://news.ycombinator.com/item?id=919407 http://news.ycombinator.com/item?id=919407 There are two UTC times corresponding to each moment from 00:00->01:00 25th Oct 2009 "London Time". There are no UTC times corresponding to 01:00->02:00 29th March 2009 "London Time". > Also there is the possibility that you actually want to store the timezone information at write-time, rather than assuming a timezone at read-time. Whether a given time should be displayed in the viewer's localtime or another time is a display issue (I may want to switch between seeing my plane arrival time in localtime of the destination and localtime of my body clock). I don't think it should affect the internal storage format as without help from a strong type system (or some strong coding conventions) coders will intermingle the two types and miss errors in testing either due to geographical proximity or lack of testing over daylight savings boundaries. [All the above saud, the problem of changing DST parameters and future event times is problematic. I think it depends on what you want to be doing with such a time. Note that you'd have the opposite bug if you were storing pre-calculated sunrise time in various locales, so it's an issue of the semantic meaning of the timestamp.]
- texel 17y ago
  I've dealt with this quite a bit, and you're right- it's difficult to deal with the conceptual impedance mismatch between the concept of a time, and its discrete meaning in persistent storage. I wrote a blog post about a month ago that talks about some issues surrounding the concept of "all-day" events which, by definition, are conceptually ambiguous. http://onehub.com/past/2009/9/28/the_joy_of_calendars/ http://onehub.com/past/2009/9/28/the_joy_of_calendars/ Ah, good times.