Date APIs and their impossible promises

  • “Timestamp” or “Instant”: a moment on Earth. Almost everyone should use UTC (Coordinated Universal Time) — not in a timezone.
  • “Date” or “Calendar date”: a wishy-washy concept. We read dates in news articles and publish dates in annual reports. Store them in a format very different from a timestamp — not in UTC, and not in a timezone.

Impossible promise: our API can handle timestamps in local time

First, let’s clear up confusion. UTC, Coordinated Universal Time, is not a timezone. UTC is a standard for pinpointing moments in time. UNIX timestamps (integer “seconds since the epoch”) fit this description.

  • Microsoft SQL Server hasDATETIME2 for UTC timestamps and DATETIMEOFFSET for the misguided idea, “timestamp in an offset” — but oops, offsets aren’t timezones.
  • Oracle has TIMESTAMP. It can be used for UTC. Then there’s TIMESTAMP WITH TIME ZONE (stored as a local-time timestamp and an arbitrary timezone) and as almost an admission of defeat, TIMESTAMP WITH LOCAL TIMEZONE (which stores values in something called the “database timezone” — which can be UTC, even though UTC isn’t a timezone — and displays values in another timezone).
  • PostgreSQL is simply backwards. Its TIMESTAMP is shorthand for TIMESTAMP WITHOUT TIME ZONE … which actually means, “datetime” (“timestamp in a timezone but we don’t know which one”). Always write TIMESTAMP WITH TIME ZONE because — prepare for your brain to melt —WITH TIME ZONE forces the value to always be UTC. (Remember: UTC isn’t a timezone!)
  • MySQL gets it right. TIMESTAMP values are always UTC. MySQL has a DATETIME type for developers with less-common requirements.
  • In Python, never store a tzinfo in your datetime.datetime value. Attach the “UTC” timezone [sic — UTC is not a timezone] only in the one line of code prior to converting to the user’s timezone for display. (In Django, set USE_TZ=False immediately upon creating any project.)
  • In Java, use java.time.Instant.
  • In C, use gmtime and not localtime.
  • In JavaScript … sigh. JavaScript Date does store UTC as it should, but JavaScript made another impossible promise:

Impossible promise: our API hide timezones from users

A JavaScript Date stores a UTC date. So far so good.

So how do I store timezones along with timestamps?

The same way you store units alongside numbers: in two separate places in memory.

Impossible promise: our Timestamp type can represent a Datetime

Date math and timestamp math are very different.

What’s the difference between a timestamp and a datetime?

Most date APIs have a “datetime” type. Don’t confuse it with a timestamp.

  • To calculate number of hours between datetimes, you need timestamps. (Daylight savings means you don’t know how many hours are between 1am and 3am.)
  • You can’t convert a future datetime to a timestamp. (Timezones are chosen by politicians; the conversion math depends on laws that haven’t been written yet.)
  • You can’t pass datetimes between two programs using a standard storage format. (ISO8601 can’t store timezones or even indicate “naive datetime”.)
  • Datetimes look and feel like timestamps.

Impossible promise: our API can magically parse any date

I could write a long list of broken parsers here. Better is to write a list of parsers that can magically parse any date:

  • Go’s time parser packs plenty of power in one string of settings and five paragraphs of documentation. It doesn’t cover everything; but what it does handle, it handles well.
  • POSIX strptime behaves as advertised.

In summary

Storing moments in time? Use timestamps.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Adam Hooper

Adam Hooper

Journalist, ex software engineer