11 ms·

Storing Date/Times in Databases

46 points by brainless 14y ago

AngryParsley 14y ago
In this case Chile decided (on March 4th) that instead of going forwards to DST at midnight March 14th, they will delay that to April 4th. It's instances like these where I wish people would push back and say, "No, we're not going to change our clocks." I was in Chile in late March. Cell phones showed different times depending on which tower they were talking to. I'm guessing some cell providers patched their systems in time and others didn't.
- jonhohle 14y ago
  After having lived in Arizona for four years, I can attest to dropping daylight saving time as being awesome. That twice a year scramble through the house to update clock - unheard of. That terrible day in spring when an hour is taken from your day - non-existent. I can't think of one negative regarding ignoring switching - except that you have to recalibrate to other people's offsets twice a year.
  - Someone 14y ago
    One disadvantage that I personally experienced: extreme time disorientation when flying Minneapolis-LA with a changeover in Phoenix, the Sunday morning (departure from Minneapolis at 6 AM or thereabouts) that DST started. On top of that, I did not know at the time that Arizona doesn't do DST. Because departure and arrival times are in local time zones, flight times were 'randomly' cut short/long, I adjusted my watch four times (one of them to the non-existent Arizona+DST) on a single day, etc.
jandrewrogers 14y ago
Daylight savings and time zone shenanigans are among the most pointless and frustrating intrusions of government fiat into software. Even if you mitigate the complications by converting internal representation to vanilla UTC and epoch time, you still have to deal with the fact that many clients are a complete mess when it comes to correctly communicating the intended time. The cost of dealing with this greatly exceeds whatever marginal value is hypothetically being realized by adjusting the clock every several months. If there was a global standard for how this is handled it would be one thing but in practice the adjustment of clocks is arbitrary and somewhat random by country. It is a waste of resources.
- Retric 14y ago
  I don't think the total time spent by developers dealing with Daylight savings time comes anywhere near the time spent by people adjusting their clocks. Assuming there is a minor benefit per person there are 7 BILLION people and it adds up. If, 1 billion people spent on average 10 minutes 2 times a year for 50 years ~1.9 million man years was spent adjusting clocks.
- fendale 14y ago
  Depending on how far north (or south) you live, daylight saving is arguably worth the hassle. Where I live, in winter, without daylight saving it would be dark until 9.30am or after in the mornings, meaning all the kids need to go to school in the dark, which is more dangerous. Then at the peak of summer, it would probably still be daylight until close to midnight. The closer you are to the equator, the less swing there is between the number of hours of daylight between summer and winter. Even between the very north of the UK and the very south there is an appreciable difference.
  - smackay 14y ago
    Being from the very far north of the UK mainland the changes in time were always welcome. We still went to school when it was cold and rainy but at least it wasn't dark (though it was on the return trip). In the summer being able to play outside until 10.30pm was awesome. I wonder what the psychological differences would be if daylight saving was abandoned and whether there might be a real economic impact. I spent quite a bit of time up in the Pacific NW of the USA and a sunny day in February lifted everybodies spirits. I hesitate to say that the work that got done was better but certainly the mood at work was a lot more congenial.
  - Beanis 14y ago
    If you live up north, adjust the local schedule instead of the clocks. If kids are waiting in the dark, move the start time to later in the day. If it is light out at midnight shift your schedule so you go to sleep after 12:00am. If you are living your life based on the position of the sun, the numbers on the clock are going to mean something a little bit different everyday anyway. Time is supposed to have a relatively consistent meaning, and we jump through a lot of hoops to give it an inconsistent meaning. Schedules are much more fluid and easy to change with little external side-effects. People will need to learn to partially decouple their routine from the numbers on the clock, but that seems like a much simpler/cheaper problem to solve than our current time mess.
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.
sicxu 14y ago
I would not go with the approach of storing timezone and formatted date/time in two seperate char columns. You need to really seperate date/time information into two parts, the absolute time and it's presentation. Usually, you only store the absolute time in database and control timezone at session/user level. You always apply timezone when you present date/time information and always convert date/time to absolute time when you store it.
- benmmurphy 14y ago
  +1. i think the only time you need to store timezones is when you don't have an absolute point in time. say you are storing a time without a date. ie: 5:00pm. you need a timezone associated with that time somehow.
  - sicxu 14y ago
    Yes. When you schedule a recurring event, such as daily reminder, you will have to store timezone. In that case, you are storing a scheduling specification, not a real date/time. You don't have the complete information, such as year/month, etc.
- obtu 14y ago
  You missed one of his requirements, which is he wants be able to compute "three months after" on the dates he stores, which is a timezone-sensitive calculation. This sort of requirement is commonly found in calendaring. That means he does need to store symbolic information for each date or datetime stored.
  - sicxu 14y ago
    You need timezone for sure. You just don't have to store it along with every date/time value. You can store it as user preference for example. What if you are scheduling a meeting between a U.S developer and an India developer?
    - obtu 14y ago
andjones 14y ago
This article seems to be picking on date / time manipulation within PHP. I don't see a database in use. The title should say something about PHP not handling date/time correctly. This is nothing new. I'm not positive if MySQL, PostgreSQL, Oracle, or similar would perform the same date and time manipulation correctly. However, I know from personal experience that MySQL handles date/times more consistently than PHP. I'm curious to see if someone with more knowledge than myself can chime in on the issue.
- Gigablah 14y ago
  From the article: "Databases rarely handle timezones, daylight savings time and rule changes correctly, so avoid the database specific functionality all together." The article isn't "picking on" PHP, it's highlighting that timezone rules are constantly changing (which is not a language-specific problem) and details how to handle them (e.g. using an extension that's updated more frequently).
  - gaius 14y ago
    Date/time handling code is pretty tricky - not leveraging what's in your platform is going to suck up a lot more time than you think. As for "databases rarely..." the answer to that is not to hand-roll your own wheel in PHP but to get a better database.
- fdr 14y ago
  > I'm not positive if MySQL, PostgreSQL, Oracle, or similar would perform the same date and time manipulation correctly. However, I know from personal experience that MySQL handles date/times more consistently than PHP. Postgres boasts some very pedantically excellent timestamp and timezone support. If there is a bug, it will be rectified in all possible haste once reported. For example, from http://www.postgresql.org/docs/9.2/static/functions-datetime.html http://www.postgresql.org/docs/9.2/static/functions-datetime...: The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican. PostgreSQL releases before 8.0 did not follow the conventional numbering of centuries, but just returned the year field divided by 100.
kondro 14y ago
In an educated, globalised economy, daylight savings time is utterly ridiculous. If you want to have an extra hour of daylight, wake up an hour earlier.
- deleted 14y ago
  [deleted]
chris_wot 14y ago
What amazes me is that Microsoft only stores the start and end of DST for each zone. They don't store the historical timezone changes. And DST changes - frequently. Unix has had this for years. Microsoft have had close to a decade and a half to get this fixed - but have never done so. Even though Exchange and calendaring software is entirely screwed most times DST changes, they still haven't done anything about it. I'm curious whether anyone has written DST software for Windows that is based on tzinfo?
Tharkun 14y ago
You have to make the distinction between absolute time and relative time. You use relative time for appointments/calendars and any other kind of "people oriented" time. Absolute time is everything else. Relative time is a sticky mess. I think The Doctor referred to it as wibbly wobby timey wimey stuff -- and it really is. Just store this crap as a string along with the user's location. Don't bother storing the timezone, because DST changes every other day and some retard or another is always adding/removing timezones. When it comes to appointments and the like, you only ever have to bother with conversions when sharing it with users in a different location/timezone. That's when you perform the relevant magic. Not before. Not ever. When it comes to absolute time, things are lot easier. Use UTC. Always. No exceptions. 'nuff said.
jbarham 14y ago
The headline feature Django 1.4 is built-in default support for time zones: https://docs.djangoproject.com/en/1.4/releases/1.4/#support-for-time-zones https://docs.djangoproject.com/en/1.4/releases/1.4/#support-... Essentially timestamps are stored in UTC but automatically converted to the user's time zone at the interface/template level, which requires adding just a couple of lines in your base template. It's very nicely implemented.
gbog 14y ago
The main gotcha with datetime + timezone saved in database fields is that such a value is mutable, and therefore can't be indexed. I didn't see this in the article. Moreover, you should store fixed facts in a database. So you have to choose between "this happened at that tick on a universal clock" and "when this happened wall time at this place was x". The former is a well defined mathematical value you can build on, the later are strings with very few useful usage. I have been bitten more than once. Now my rule is to always store universal time without timezone.

46 points by brainless 14y ago

In this case Chile decided (on March 4th) that instead of going forwards to DST at midnight March 14th, they will delay that to April 4th. It's instances like these where I wish people would push back and say, "No, we're not going to change our clocks." I was in Chile in late March. Cell phones showed different times depending on which tower they were talking to. I'm guessing some cell providers patched their systems in time and others didn't.

jonhohle 14y ago
After having lived in Arizona for four years, I can attest to dropping daylight saving time as being awesome. That twice a year scramble through the house to update clock - unheard of. That terrible day in spring when an hour is taken from your day - non-existent. I can't think of one negative regarding ignoring switching - except that you have to recalibrate to other people's offsets twice a year.
- Someone 14y ago
  One disadvantage that I personally experienced: extreme time disorientation when flying Minneapolis-LA with a changeover in Phoenix, the Sunday morning (departure from Minneapolis at 6 AM or thereabouts) that DST started. On top of that, I did not know at the time that Arizona doesn't do DST. Because departure and arrival times are in local time zones, flight times were 'randomly' cut short/long, I adjusted my watch four times (one of them to the non-existent Arizona+DST) on a single day, etc.

jandrewrogers 14y ago

Daylight savings and time zone shenanigans are among the most pointless and frustrating intrusions of government fiat into software. Even if you mitigate the complications by converting internal representation to vanilla UTC and epoch time, you still have to deal with the fact that many clients are a complete mess when it comes to correctly communicating the intended time. The cost of dealing with this greatly exceeds whatever marginal value is hypothetically being realized by adjusting the clock every several months. If there was a global standard for how this is handled it would be one thing but in practice the adjustment of clocks is arbitrary and somewhat random by country. It is a waste of resources.

Retric 14y ago
I don't think the total time spent by developers dealing with Daylight savings time comes anywhere near the time spent by people adjusting their clocks. Assuming there is a minor benefit per person there are 7 BILLION people and it adds up. If, 1 billion people spent on average 10 minutes 2 times a year for 50 years ~1.9 million man years was spent adjusting clocks.
fendale 14y ago
Depending on how far north (or south) you live, daylight saving is arguably worth the hassle. Where I live, in winter, without daylight saving it would be dark until 9.30am or after in the mornings, meaning all the kids need to go to school in the dark, which is more dangerous. Then at the peak of summer, it would probably still be daylight until close to midnight. The closer you are to the equator, the less swing there is between the number of hours of daylight between summer and winter. Even between the very north of the UK and the very south there is an appreciable difference.
- smackay 14y ago
  Being from the very far north of the UK mainland the changes in time were always welcome. We still went to school when it was cold and rainy but at least it wasn't dark (though it was on the return trip). In the summer being able to play outside until 10.30pm was awesome. I wonder what the psychological differences would be if daylight saving was abandoned and whether there might be a real economic impact. I spent quite a bit of time up in the Pacific NW of the USA and a sunny day in February lifted everybodies spirits. I hesitate to say that the work that got done was better but certainly the mood at work was a lot more congenial.
- Beanis 14y ago
  If you live up north, adjust the local schedule instead of the clocks. If kids are waiting in the dark, move the start time to later in the day. If it is light out at midnight shift your schedule so you go to sleep after 12:00am. If you are living your life based on the position of the sun, the numbers on the clock are going to mean something a little bit different everyday anyway. Time is supposed to have a relatively consistent meaning, and we jump through a lot of hoops to give it an inconsistent meaning. Schedules are much more fluid and easy to change with little external side-effects. People will need to learn to partially decouple their routine from the numbers on the clock, but that seems like a much simpler/cheaper problem to solve than our current time mess.

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.

sicxu 14y ago

I would not go with the approach of storing timezone and formatted date/time in two seperate char columns. You need to really seperate date/time information into two parts, the absolute time and it's presentation. Usually, you only store the absolute time in database and control timezone at session/user level. You always apply timezone when you present date/time information and always convert date/time to absolute time when you store it.

benmmurphy 14y ago
+1. i think the only time you need to store timezones is when you don't have an absolute point in time. say you are storing a time without a date. ie: 5:00pm. you need a timezone associated with that time somehow.
- sicxu 14y ago
  Yes. When you schedule a recurring event, such as daily reminder, you will have to store timezone. In that case, you are storing a scheduling specification, not a real date/time. You don't have the complete information, such as year/month, etc.
obtu 14y ago
You missed one of his requirements, which is he wants be able to compute "three months after" on the dates he stores, which is a timezone-sensitive calculation. This sort of requirement is commonly found in calendaring. That means he does need to store symbolic information for each date or datetime stored.
- sicxu 14y ago
  You need timezone for sure. You just don't have to store it along with every date/time value. You can store it as user preference for example. What if you are scheduling a meeting between a U.S developer and an India developer?
  - obtu 14y ago

andjones 14y ago

This article seems to be picking on date / time manipulation within PHP. I don't see a database in use. The title should say something about PHP not handling date/time correctly. This is nothing new. I'm not positive if MySQL, PostgreSQL, Oracle, or similar would perform the same date and time manipulation correctly. However, I know from personal experience that MySQL handles date/times more consistently than PHP. I'm curious to see if someone with more knowledge than myself can chime in on the issue.

Gigablah 14y ago
From the article: "Databases rarely handle timezones, daylight savings time and rule changes correctly, so avoid the database specific functionality all together." The article isn't "picking on" PHP, it's highlighting that timezone rules are constantly changing (which is not a language-specific problem) and details how to handle them (e.g. using an extension that's updated more frequently).
- gaius 14y ago
  Date/time handling code is pretty tricky - not leveraging what's in your platform is going to suck up a lot more time than you think. As for "databases rarely..." the answer to that is not to hand-roll your own wheel in PHP but to get a better database.
fdr 14y ago
> I'm not positive if MySQL, PostgreSQL, Oracle, or similar would perform the same date and time manipulation correctly. However, I know from personal experience that MySQL handles date/times more consistently than PHP. Postgres boasts some very pedantically excellent timestamp and timezone support. If there is a bug, it will be rectified in all possible haste once reported. For example, from http://www.postgresql.org/docs/9.2/static/functions-datetime.html http://www.postgresql.org/docs/9.2/static/functions-datetime...: The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican. PostgreSQL releases before 8.0 did not follow the conventional numbering of centuries, but just returned the year field divided by 100.

kondro 14y ago

In an educated, globalised economy, daylight savings time is utterly ridiculous. If you want to have an extra hour of daylight, wake up an hour earlier.

deleted 14y ago
[deleted]

chris_wot 14y ago

What amazes me is that Microsoft only stores the start and end of DST for each zone. They don't store the historical timezone changes. And DST changes - frequently. Unix has had this for years. Microsoft have had close to a decade and a half to get this fixed - but have never done so. Even though Exchange and calendaring software is entirely screwed most times DST changes, they still haven't done anything about it. I'm curious whether anyone has written DST software for Windows that is based on tzinfo?

Tharkun 14y ago

You have to make the distinction between absolute time and relative time. You use relative time for appointments/calendars and any other kind of "people oriented" time. Absolute time is everything else. Relative time is a sticky mess. I think The Doctor referred to it as wibbly wobby timey wimey stuff -- and it really is. Just store this crap as a string along with the user's location. Don't bother storing the timezone, because DST changes every other day and some retard or another is always adding/removing timezones. When it comes to appointments and the like, you only ever have to bother with conversions when sharing it with users in a different location/timezone. That's when you perform the relevant magic. Not before. Not ever. When it comes to absolute time, things are lot easier. Use UTC. Always. No exceptions. 'nuff said.

jbarham 14y ago

The headline feature Django 1.4 is built-in default support for time zones: https://docs.djangoproject.com/en/1.4/releases/1.4/#support-for-time-zones https://docs.djangoproject.com/en/1.4/releases/1.4/#support-... Essentially timestamps are stored in UTC but automatically converted to the user's time zone at the interface/template level, which requires adding just a couple of lines in your base template. It's very nicely implemented.

gbog 14y ago

The main gotcha with datetime + timezone saved in database fields is that such a value is mutable, and therefore can't be indexed. I didn't see this in the article. Moreover, you should store fixed facts in a database. So you have to choose between "this happened at that tick on a universal clock" and "when this happened wall time at this place was x". The former is a well defined mathematical value you can build on, the later are strings with very few useful usage. I have been bitten more than once. Now my rule is to always store universal time without timezone.