

Storing Date/Times in Databases - brainless
http://derickrethans.nl/storing-date-time-in-database.html

======
jandrewrogers
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.

~~~
fendale
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.

~~~
Beanis
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.

~~~
phpnode
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?

~~~
Beanis
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.

------
sicxu
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
+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
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.

------
chris_wot
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?

------
js4all
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.

~~~
Aloisius
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).

~~~
ars
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
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.

------
kondro
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.

------
AngryParsley
_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
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
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.

------
Tharkun
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.

------
gbog
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.

------
andjones
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
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
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.

------
jbarham
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-...](https://docs.djangoproject.com/en/1.4/releases/1.4/#support-
for-time-zones)

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.

