IS0 8601 Datetimes or Bust

If you create, store or share a datetime/timestamp that does not have a timezone, you are a bad person.

What do I mean?

11:59 PM, December 31, 2017

When will this time occur? Well, many times, over the course of a 24 hour span. You could fly around the world and experience this time occurring over and over and over. This will happen because 11:59 PM, December 31, 2017 is not an exact time, it is a kindaish time. A ballpark figure. Do you know what computers are not good at? Kinda-stuff.

5 - 2kinda = wtf

So what would be approps? Simply have a timezone associated.

11:59 PM, December 31, 2017 UTC

That time will come to pass exactly once. Ever. Computers like this sort of precision. And you don’t want to make the computers mad, do you?

Why Would Datetimes/Timestamps Be Missing Timezones?

Because, humans. If things go right, datetimes without a timezone really shouldn’t exist, but then again, nor should world hunger.

1 — Quite often, datetimes without timezones are just “out there”- in various data sets on the internet, being served from APIs, from devices, in databases -all existing without a timezone (often assuming a local timezone). You’ll see stuff like 2017-12-31 23:59:59.999 all the time and people, especially younger programmers, won’t think twice.

2 — When dealing with your data, RDBMS databases (MySQL, PostgreSQL and Oracle) have often been the point where the wheels come off.

A big reason for this is their datatypes. What is a datetime? A timestamp? ¯\_(ツ)_/¯

Just a few common RDBMS databases and their datetime datatypes

Confused yet? Good.

  • In MySQL, a datetime will use the local machine’s timezone for data storage, whereas a timestamp will be stored in UTC.
  • In PostgreSQL, a time (without timezone) and timestamp (without timezone) will use the local machine’s timezone for data storage, whereas a time with timezone, and timestamp with timezone will be stored in UTC.
  • In Oracle, a timestamp and timestamp with local timezone will both use the local machine’s timezone for data storage, whereas a timestamp with timezone will be stored in UTC.

Of course, you (or some other creature) can always override what the local timezone is. Fun!

Also note that based on what datatype and storage solution (and version LOL) you are using, the data may be returned with the local timezone, the inputted time zone, UTC, or without a timezone.

So you can lose a timezone here, or get the wrong time zone just by not fully understanding the datatype you’re storing your data with. You could also run into issues once you enter the cloud — say you have your database running in RackSpace in a datacenter in Virginia, but it's backed up in a datacenter in Chicago.. or you have a database hosted in AWS in Oregon but your compute cluster is in Ohio. Things. Can. Go. Wrong.

It’s simply Murphy’s law.

ISO 8601 to the Rescue!

ISO 8601 is the cream for what ails you. IMHO, the perfect ISO 8601 datetime representations is 2017–12–31T23:59:59.999Z. This example contains the date, time (with optional millisecond precision), and most importantly, timezone as “Z”. Z is shorthand for UTC. Unless a very specific use case dictates otherwise, you should always store datetimes/timestamps in UTC. And let me take that a step further, you should always handle and store datetimes/timestamps in the ISO 8601 format (with a UTC timezone). Are there exceptions? Sure. But you’re not one of them. And ISO 8601 requires a timezone, so by getting in the habit of using this format you’ll keep yourself from making an oops baby.

TLDR;

Always handle and store datetimes/timestamps in the ISO 8601 format (with a UTC timezone).

2017–12–31T23:59:59.999Z

Win.

Bonus

As pointed out by The Funk, using ISO 8601 datetime formats will also allow a stringified datetime to be lexically sorted. This is useful for when you’re using datetime as strings for folder structures, URIs, etc.

20151231T235959999Z
20160131T235959999Z
20160831T235959999Z
20161231T235959999Z
20171228T235959999Z
20171229T235959999Z
20171230T235959999Z
20171231T235959999Z

Which lexically sorts much better than this.

August 1st, 2016 12:59 PM
December 28th, 2017 12:59 PM
December 29th, 2017 12:59 PM
December 30th, 2017 12:59 PM
December 31th, 2015 12:59 PM
December 31th, 2016 12:59 PM
December 31th, 2017 12:59 PM
January 1st, 2016 12:59 PM