This post aims at pointing out some of the difficulties that arise when writing software and dealing with dates and times, and with storing them in databases.
Databases and time zones
One of the problems which usually arise when storing date+time in a database, is that it might then come out of it in a different time zone, which can be sort of a nightmare especially if you are writing software which schedules events or performs similar tasks.
mySQL date and time type is
datetime, which knows nothing about time zones. So, when a date+time is stored there,
it’s up to you to “remember” the the time zone you used. Let’s try an example on my computer (the database is MariaDB, but that’s the same), which is configured with the Europe/Rome (Olson) time zone (CET/UTC+1 in the winter, CEST/UTC+2 during summer):
As you can see, the default time zone used is the local one, that is the one of the machine on which the server is running. Let’s set the session to use UTC instead:
All fine here, but let’s try storing a date/time into a table_
See the problem? When changing session time zones, you still get the same time when reading data. That is, if a client sets a different session TZ than another, the same date will not be actually the same! Let’s say a web application enters a business meeting at 17:24 using the local time zone, and the user from a mobile phone when reading it sees 15:24: the business meeting would fail.
The trick here is to always set the session time zone to UTC (+00:00). This is still prone to difficult-to-find errors, of course.
PostgreSQL is much better, as it offers a
timestamp with time zone type, which should always be used. When storing time in it, you need to pass the time zone, and this gets stored in the server, so TZ information is always kept and carried around. UTC should always be preferred (for reasons we will soon see), but with PostgreSQL you have the guarantee that a stored time will always be that one, regardless on how the database is accessed, which is a huge plus.
Why UTC should always be preferred
Aside: we already introduced UTC as a name earlier. For the ones who don’t know about it, it’s Universal Time Coordinated. The UTC Wikipedia page has full explanations. In practice, the UK uses UTC (GMT, Greenwich Mean Time) in the winter, and UTC+1 (BST, British Summer Time) in the summer.
Let’s say we have a web application which collects times at which a movie meeting begins on TV. We will make a few examples in Perl.
The user enters a movie which begins at 02:00 local time (Europe/Rome), which is almost always fine, but not really always:
Ops, we get:
This happens because this time does not exist: that night, after 01:59:59 there was directly 03:00:00, because that night, in Europe/Rome, we changed time from CET (UTC+1, winter) to CEST (UTC+2, summer).
But there’s worse, try this:
This code doesn’t even throw an error, it just shows this scary awesomeness:
😱😱😱 How the f–k could this happen?!? Pretty simple, actually. When going back to winter time, it’s not a matter of time which does not exist, but a matter of time existing twice: that night, after 02:59:59 the time in Europe/Rome went back to 02:00:00, thus duplicating an entire hour. So, two different UTC times are the same Europe/Rome time.
If you only need a date, forget about the time
It’s tempting for software developers to always stick to date+time types, even when having to deal with dates (such as birth dates). This is not recommended.
One typical approach for a birth date is to set it as:
which would even be fine for us in Europe, as it would convert to 01:00 or 02:00 when converted to local time. However this would cause a change of date when converted, say, to America/Montreal zone, showing the birth happening one day before.
Also, one might incur in a non-existing time problem like the one mentioned earlier, if dealing with local times instead of UTC. Say for instance:
In 1975 (in Italy at least) the change to Daylight Saving (summer) time happened in late May instead of March, and at midnight instead of 2am. I found about this by chance, but there may be a lot of other edge cases.
So the solution is just: use just dates when dealing with dates. Databases have the fields, and programming languages have that support in the libraries.
Time in the future
There is a lot of ongoing political talk regarding wether Daylight Saving (summer time) should continue to exist, and it might actually be dropped in Europe in the upcoming years.
Time zones themselves are at risk of extinction - because they just are not so handy - even though that won’t happen soon (should it ever happen). Different countries think in different ways: China, for instance, has a single time zone even being one of the biggest countries in the Word; on the other hand, the USA has some 6 different time zones.
There are also complications in sight. When humans will colonize space, there will be a series of new time zones: at least one per planet (being Mars the first one, of course), if not more. Conversions are not going to be as easy as adding or subtracting hours, because planet rotation (and therefore duration of a day) is different when you change planet. However, UTC will probably still be used as the reference: it’s Universal, after all.