Storing Date/Times in Databases(derickrethans.nl)
derickrethans.nl
Storing Date/Times in Databases
http://derickrethans.nl/storing-date-time-in-database.html
37 comments
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.
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.
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.
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.
That would be ideal, if everyone worked on GMT and just started work at different times, rather than the same time in a different timezone.
Technically we could all do that now anyway, just start using GMT rather than our local timezone :)
Technically we could all do that now anyway, just start using GMT rather than our local timezone :)
Can you imagine how much more difficult that would be to program for? Instead of (relatively) easy to parse timezone rules you have some kind of awful, locale specific agreement that that event you scheduled for 9am monday every week would actually occur at 8am half of the year?
I'm not sure if you are serious, but I don't understand what you think would be involved.
If you remove the time inconsistencies from a calendar, any decent calendaring app will solve the scheduling problem you are left with. Scheduling is a data entry problem. It is a problem we have with or without timezones and dst changes.
We actually used to do this at school, where in the winter we moved sports to be after lunch and then had afternoon lessions at night.
We basically had a summer and winter timetable.
We basically had a summer and winter timetable.
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.
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.
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.
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.
For one developer that thinks this is a bad thing, there are multiple millions of people that benefit from DST. Is it pain in the ass? Of course! Is it still necessary? Yes, in places outside the industrial world.
The governments could do a better job of keepung things predictable, but overall it is easier to teach a very small percentage of (hopefully) intelligent developers to deal with the complexity, than the uneducated masses.
The governments could do a better job of keepung things predictable, but overall it is easier to teach a very small percentage of (hopefully) intelligent developers to deal with the complexity, than the uneducated masses.
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.
+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.
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.
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.
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?
The date of the meeting mustn't depend on the display preferences of one of the participants. The meeting itself has to be anchored to a time zone.
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?
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?
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.
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).
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).
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).
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).
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.
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.
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.
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.
And what about the daylight savings time issue specified in the article?
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.
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.
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.
If you want to have an extra hour of daylight, wake up an hour earlier.
[deleted]
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.
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.
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.
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.
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.
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.
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.
I have been bitten more than once. Now my rule is to always store universal time without timezone.
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.
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.
> 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...:
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...:
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.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).
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).
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.
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-...
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.
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.
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.