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.
A big reason for this is their datatypes. What is a datetime? A timestamp? ¯\_(ツ)_/¯
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.
Always handle and store datetimes/timestamps in the ISO 8601 format (with a UTC timezone).
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.
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