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

  • 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

So how do I store timezones along with timestamps?

Impossible promise: our Timestamp type can represent a Datetime

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

  • 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

  • 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




Journalist, ex software engineer

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

BDD Addict Newsletter October 2019

Making use of route helpers in Rails

Exploiting Software -ATTACK PATTERN — Part 1

Huge List of Database Benchmark (2019)

Two Colliders Enter, One Collider Leaves: OnCollisionEnter vs OnTriggerEnter

[LeetCode]#2000. Reverse Prefix of Word

Five Months of Gamedev with Elm

Hive City progress in September demonstrating basic character movement

Deploy Strapi to Heroku

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

More from Medium

Getting People to Love Queues With Java, Python, and JavaScript

Let’s solve the error of GROUP BY clause and contains nonaggregated column in MySQL

Is python slow or fast?

Validating data flow from your MinIo s3 object storage modules.