Time Pitfalls

Disclaimer

  1. This article is based on my own experience, so I may have got some details wrong. Corrections are welcome in the comments.
  2. Most of the practical advice here is about recording past events. For a discussion of future dates, see this article.

TL;DR

  1. Store time in a format that unambiguously identifies the instant an event occurred.
  2. Avoid MySQL’s TIMESTAMP type. If you know a case where it is necessary—or even a good fit—please share it in the comments.
  3. Unix timestamps have their own flaws, and 00–59 is not always a sufficient range for the seconds field.
  4. “Time zone” can mean several different things. Know which one you need and use it accordingly.

The question

When I was interviewing developers, I asked a version of the same question: two computers, one in Minsk (UTC+3) and one in London (UTC+1 at the time of writing), run PHP’s time() function at exactly the same moment. Assuming both clocks are correct, what do you get if you subtract the London result from the Minsk result?

The answer I heard most often was 7,200. This article is about why the correct answer is different.

Identifying an instant

Let’s start with something that seems straightforward. I am writing these lines on September 14, 2017 at 14:46:02 and want to save that fact somewhere. Is 2017-09-14 14:46:02 enough? It is not: in Minsk, where I am writing, it refers to one instant; in London, the clock reads 12:46:02 at that same instant. London will not reach 14:46:02 for another two hours. We need a time zone.

In the usual sense, a time zone is an area that observes the same standard time. Minsk is UTC+3 and London is UTC. Some places also observe daylight saving time, which changes their offset from UTC. London was on daylight saving time when this article was published, so its offset was UTC+1, also called BST, or British Summer Time.

There is another meaning, too: a zone from the IANA tz database. It names a place whose local time and daylight saving rules apply across a particular area: for example, Europe/Minsk or Europe/London. Unlike a bare UTC offset, a tz database zone includes the history of changes to those rules.

So what should we attach to a local clock reading to make it unambiguous? UTC, UTC+1, BST, or Europe/London? Let’s consider 01:00 in London on October 29, 2016. Daylight saving time ends on this day, and that clock time occurs twice.

There are two cases:

  1. 2016-10-29 01:00:00, immediately after 2016-10-29 00:59:59.
  2. 2016-10-29 01:00:00, immediately after 2016-10-29 01:59:59, when clocks were put back an hour.

The two entries need different qualifiers even though their clock readings match. Europe/London alone cannot distinguish them. The first falls under daylight saving time, so it is UTC+1 or BST. The second is standard UK time: UTC.

When you record local time, include its current UTC offset:

  1. 2016-10-29 01:00:00 UTC+1 (or 2016-10-29 00:00:00 UTC)
  2. 2016-10-29 01:00:00 UTC

An offset turns a local clock reading into an unambiguous instant. When you need to display that instant for someone in London, use the tz database to look up the offset that applied there at the time:

<?php
$time = new DateTimeImmutable('2016-10-29T00:00:00+00:00');
$londonTimeZone = new DateTimeZone('Europe/London');
$londonTime = $time->setTimezone($londonTimeZone);

var_dump($londonTime->format('Y-m-d H:i:s (P)'));

string(28) "2016-10-29 01:00:00 (+01:00)"

This, however, does not:

<?php
$notLondonTime = new DateTimeImmutable(
    '2016-10-29T00:00:00+00:00',
    new DateTimeZone('Europe/London')
);

var_dump($notLondonTime->format('Y-m-d H:i:s (P)'));

string(28) "2016-10-29 00:00:00 (+00:00)"

That is expected. When the time string already includes a time zone, PHP ignores the DateTimeZone argument. It also distinguishes a fixed offset from a tz database zone:

<?php
$time = new DateTimeImmutable('2016-10-29T00:00:00UTC+00:00');
$londonTime = $time->setTimezone(new DateTimeZone('Europe/London'));
$notLondonTime = new DateTimeImmutable(
    '2016-10-29T00:00:00UTC+00:00',
    new DateTimeZone('Europe/London')
);

var_dump($londonTime, $notLondonTime);

object(DateTimeImmutable)#3 (3) {
  ["date"]=> string(26) "2016-10-29 01:00:00.000000"
  ["timezone_type"]=> int(3)
  ["timezone"]=> string(13) "Europe/London"
}
object(DateTimeImmutable)#4 (3) {
  ["date"]=> string(26) "2016-10-29 00:00:00.000000"
  ["timezone_type"]=> int(1)
  ["timezone"]=> string(6) "+00:00"
}

Look at timezone_type and timezone: PHP knows whether it was given a fixed offset or a tz database zone whose clock changes it must take into account.

In short, record an instant with its current offset; use the tz database when you need to show it in a person’s local time. Just remember: offsets are not always whole hours. Iran and India use +3:30 and +5:30 respectively (aside from daylight saving time), while Nepal uses +5:45.

Unix timestamps

That leads us to Unix timestamps, which is what time() returns: the number of seconds since 1970-01-01 00:00:00 UTC. It is simply an integer representation of an instant in UTC.

Back to the interview question: at one particular instant, does the number of seconds since UTC midnight on 1 January 1970 differs between Minsk and London? Of course not. A Unix timestamp describes the same instant everywhere. The difference is 0.

MySQL’s date and time types

MySQL has two date-and-time types (setting aside an INT that could hold a Unix timestamp): TIMESTAMP and DATETIME. Both accept and return YYYY-MM-DD HH:MM:SS, or the familiar Y-m-d H:i:s in date() and DateTime::format().

The difference lies in how they are stored:

Their storage requirements differ as well:

Type< 5.6.4≥ 5.6.4
TIMESTAMP4 B4 B + 0–3 B
DATETIME8 B5 B + 0–3 B

Since MySQL 5.6.4, time-related types can include up to six fractional-second digits. The optional fractional part uses the extra 0–3 bytes. DATETIME was also reduced from 8 bytes to 5, which makes the storage argument for TIMESTAMP even less compelling.

The real problem with TIMESTAMP is that MySQL converts incoming values according to its time_zone. You can configure it globally with default_time_zone, change it at runtime with SET GLOBAL time_zone = timezone, or set it for one session with SET time_zone = timezone. The last two options require loading time-zone information into MySQL.

By default, the setting is SYSTEM. When MySQL starts, it derives system_time_zone from the operating system’s time zone. Now imagine an application that always uses UTC and sends UTC date-time values to MySQL. At first, both MySQL and the server use UTC, so MySQL stores the Y-m-d H:i:s values unchanged. Later, an administrator switches the server to Europe/Minsk so the logs and cron jobs show local time, then restarts MySQL. MySQL now assumes that incoming values are UTC+3 and subtracts three hours before storing them. At that point, the data is a mess: some rows were shifted on insert and some were not, and there is no way to tell them apart.

To see this in action, follow this demo.

The same issue exists with Europe/London: MySQL’s time zone can depend on whether it started during daylight saving time or standard time. And time-zone rules change more often than you might think. Look at Minsk’s history: since 1979, almost every decade has brought a change.

My conclusion is simple: avoid TIMESTAMP. Spend the extra 1–4 bytes per value, depending on the version, and use DATETIME. Make the application write UTC by calling setTimezone('UTC') before storing format('Y-m-d H:i:s'). The offset is not stored explicitly, but the application always makes it zero and treats database values as UTC. That is safer than TIMESTAMP and more convenient than an INT Unix timestamp. Anyways, store time in a format that identifies an instant unambiguously.

Atomic and astronomical time

We tend to think of a day as 86,400 seconds long, but that is not quite right. The Earth’s rotation is gradually slowing, so a solar day is getting longer. The difference is tiny, but it adds up. Rather than defining a second as 1/86,400 of an Earth rotation, we use an atomic standard: 9,192,631,770 periods of radiation from a transition between two hyperfine levels in the ground state of caesium-133.

As days get longer, atomic-clock midnight slowly drifts ahead of the corresponding astronomical position. To correct that drift, UTC occasionally gains a leap second. When the difference approaches 0.6 seconds, a second is added at the end of 30 June or 31 December: 23:59:59 is followed by 23:59:60. So 2016-12-31T23:59:60Z is a valid time.

Unix timestamps do not account for leap seconds: the leap second and the first second of the following day share a timestamp. PHP’s DateTime and DateTimeImmutable, which use Unix timestamps internally, ignore them too:

<?php
var_dump(
    (new DateTimeImmutable('2016-12-31T23:59:59Z'))->getTimestamp(),
    (new DateTimeImmutable('2016-12-31T23:59:60Z'))->getTimestamp(),
    (new DateTimeImmutable('2017-01-01T00:00:00Z'))->getTimestamp(),
    (new DateTimeImmutable('2017-01-01T00:00:01Z'))->getTimestamp()
);

int(1483228799)
int(1483228800)
int(1483228800)
int(1483228801)

<?php
var_dump((new DateTimeImmutable('2016-12-31T23:59:59Z'))->modify('+1 second'));

class DateTimeImmutable#2 (3) {
  public $date => string(26) "2017-01-01 00:00:00.000000"
  public $timezone_type => int(2)
  public $timezone => string(1) "Z"
}

In practice, remember two things about Unix timestamps and time():

  1. time() does not always reflect elapsed time. During a leap second, echo time(); sleep(1); time(); can return the same value twice.
  2. Do not assume timestamps always increase. Under some conditions, including leap seconds, NTP can move the clock backwards.