5 ms·

There is only one simple rule to follow: Always use UTC when storing date/time in databases, do conversion to local time while reading and from local time while

by js4all 14y ago

There is only one simple rule to follow: Always use UTC when storing date/time in databases, do conversion to local time while reading and from local time while writing.

kondro 14y ago
And what about the daylight savings time issue specified in the article?
- talaketu 14y ago
  The article does not clearly specify the problem to be solved. But it general, it would seem the author is concerned about choosing a data representation for date and time and place of a future event that stable under changes to the local timezone. In other words, he wants a flaccid designator. UTC is clearly not suitable for this. On the other hand, no-one is looking to add 8 months to a localtime and have it make sense. This kinds of scheduling decisions will always be supervised by humans. And the "Chile" example just goes to show you should be prepared to update a schedule. Airlines do this all the time. Food for thought.
Aloisius 14y ago
Except that will fail if you're doing any kind of appointment calendaring if the date of DST changes after you've made the appointment (which it does at the whim of governments). When I schedule an appointment for 5pm on November 4th in San Francisco, I expect it to stay 5pm on November 4th regardless of what the offset from UTC happens to be on that date. If for some reason, the California decides PST starts on the 5th after I've made my appointment, I do not want to show up an hour late. Worse, if I schedule a repeating appointment for November 4th at 5 pm, then I do not want to show up an hour late or early every year depending on what date DST falls on. This is why calendaring software often stores in local time or local time + Olson location (e.g. America/Los_Angeles) or time zone id (US-Western).
- dredmorbius 14y ago
  Yeah, that does sort of throw a wrench into it. You'd have to indicate that this is "locally specified time", and that in the event of any DST / local presentation rules changes, the UTC time should be adjusted to keep the local presentation constant. The obverse problem would be, say, specifying astronomical events in such a way that they're accurately presented when the time comes for them. In this case, the UTC time would be definitive, but the local presentation would change if time presentation rules changed. Yeah, it's a mess.
- ars 14y ago
  That's why I always distinguish two types of times: A point in time, and the name of a time. A point in time should be stored as a timestamp (unixtime) and converted to local at the point of presentation. A name of a time should be stored as a datetime column with no timezone and should not be adjusted by timezone. The hard part is when you combine them: You have a name of a time (an appointment) and you want to correlate that with a point in time to match with someone else's calendar. The only way to do that is store the datetime plus the name of the timezone area (not the offset, the name).
  - URSpider94 14y ago
    The author's point is that even that is not enough, if the rules of the timezone change between when you enter the event into the database and when the event arrives.
rvkennedy 14y ago
I always store it as a UTC double-precision Julian day value (not Julian Day Number - that would be an integer). This has the advantage of being able to represent small fractions of a second, but having differences that are easily comparable by eye - each whole number represents a day.

by js4all 14y ago

There is only one simple rule to follow: Always use UTC when storing date/time in databases, do conversion to local time while reading and from local time while writing.

kondro 14y ago

And what about the daylight savings time issue specified in the article?

talaketu 14y ago
The article does not clearly specify the problem to be solved. But it general, it would seem the author is concerned about choosing a data representation for date and time and place of a future event that stable under changes to the local timezone. In other words, he wants a flaccid designator. UTC is clearly not suitable for this. On the other hand, no-one is looking to add 8 months to a localtime and have it make sense. This kinds of scheduling decisions will always be supervised by humans. And the "Chile" example just goes to show you should be prepared to update a schedule. Airlines do this all the time. Food for thought.

Aloisius 14y ago

Except that will fail if you're doing any kind of appointment calendaring if the date of DST changes after you've made the appointment (which it does at the whim of governments). When I schedule an appointment for 5pm on November 4th in San Francisco, I expect it to stay 5pm on November 4th regardless of what the offset from UTC happens to be on that date. If for some reason, the California decides PST starts on the 5th after I've made my appointment, I do not want to show up an hour late. Worse, if I schedule a repeating appointment for November 4th at 5 pm, then I do not want to show up an hour late or early every year depending on what date DST falls on. This is why calendaring software often stores in local time or local time + Olson location (e.g. America/Los_Angeles) or time zone id (US-Western).

dredmorbius 14y ago
Yeah, that does sort of throw a wrench into it. You'd have to indicate that this is "locally specified time", and that in the event of any DST / local presentation rules changes, the UTC time should be adjusted to keep the local presentation constant. The obverse problem would be, say, specifying astronomical events in such a way that they're accurately presented when the time comes for them. In this case, the UTC time would be definitive, but the local presentation would change if time presentation rules changed. Yeah, it's a mess.
ars 14y ago
That's why I always distinguish two types of times: A point in time, and the name of a time. A point in time should be stored as a timestamp (unixtime) and converted to local at the point of presentation. A name of a time should be stored as a datetime column with no timezone and should not be adjusted by timezone. The hard part is when you combine them: You have a name of a time (an appointment) and you want to correlate that with a point in time to match with someone else's calendar. The only way to do that is store the datetime plus the name of the timezone area (not the offset, the name).
- URSpider94 14y ago
  The author's point is that even that is not enough, if the rules of the timezone change between when you enter the event into the database and when the event arrives.

rvkennedy 14y ago

I always store it as a UTC double-precision Julian day value (not Julian Day Number - that would be an integer). This has the advantage of being able to represent small fractions of a second, but having differences that are easily comparable by eye - each whole number represents a day.