Storing Date/Times in Databases

http://derickrethans.nl/storing-date-time-in-database.html

by brainless • 14 years ago

46 36 14 years 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• 14 years 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• 14 years 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• 14 years 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• 14 years 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• 14 years 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• 14 years 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.

Beanis2• 14 years 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.

IgorPartola• 14 years ago

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.

js4all• 14 years 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.

kondro2• 14 years ago

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

talaketu• 14 years 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• 14 years 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• 14 years 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• 14 years 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).

rvkennedy• 14 years 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.

sicxu3• 14 years 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• 14 years 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.

sicxu3• 14 years 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.

obtu2• 14 years 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.

sicxu3• 14 years 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?

andjones• 14 years 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• 14 years 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• 14 years 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• 14 years 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...:

    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.

kondro2• 14 years 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.

chris_wot• 14 years 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• 14 years 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• 14 years 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-...

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• 14 years 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.

Storing Date/Times in Databases

Related Stories