3 simple rules to get time storage right

I think that everybody here loves programming, however there is one specific topic that yields both a very high level of pain in the ass and a quite elevated frequency of occurrence. I'm talking about representing time.

The topic has been covered a lot, yet I still hear some misconceptions running around so it's always good to repeat those things from time to time. Things like the Zune bug are a constant reminder that time representation is hard. Of course if they used an existing and tested lib, this wouldn't have happened.

Rule #1: always use a well-tested lib, for everything related to date manipulation

But this doesn't give any answer on what to store and how to store it. Let's give a few definitions

• Time offset - how many hours do you need to add or remove in comparison to the universal time (UTC)?
• Time zone - a geographic area in which the time offset follows the same rules (e.g. Europe/Paris has a winter time offset and a summer time offset, and this applies to all of metropolitan France)
• Wall clock time - the time your user reads on his wall clock

If you pick the offset or the zone, you can usually find the other one and thus find an exact moment in time. The only exception I see is if you know the wall clock time and the time zone but DST happens. This produces a 1h overlap during which time is ambiguous.

Rule #2: to reference an exact moment, you need the wall clock time accompanied by either the time zone or the time offset

How do you pick? It depends on what you're looking for. If you're looking to store an exact moment for sure, you will need to store the time offset. Otherwise you'd get the DST overlap.

But suppose that you're writing an alarm clock. In the evening the user will set the alarm clock for a precise time in the morrow, let's say 7am. If you store that wall clock time (tomorrow at 7am) with the time offset of today, what happens when DST comes in or out? The user will get up one hour too late or too early. In this case, it's specifically important to store the time zone instead of the offset.

Rule #3: choose to use either the offset or the zone depending on your user's intent

You're going to tell me, what about always storing dates in UTC or using timestamps instead? Well it's exactly as if you chose to use the offset.

As of how to apply all that in practice, the topic is pretty wide as it differs for each language. However, there is a few things that I'd like to point out.

Most languages or databases have distinct types for "date", "time" and "date/time". Usually "date/time" has some kind of awareness of the time zone and/or offset. By example PostgreSQL has an explicit time zone management and conversion tools (on a per-value basis) while MySQL handles it in the most possibly confusing way (on a per-connection basis). Again, choose wisely depending on the intent.

Date and time manipulation is also pretty hard, due to the cyclical nature of time representation on one hand and due to the numerous exception on the other hand. Apply the rule #1 here, always seek a library to do that job. By example in Python, dateutil's relativedelta is one of my favorite tools, as it speaks wall clock time and not computer time. Example:

from pytz import timezone, utc
from datetime import datetime

now = datetime.utcnow()\
    .replace(tzinfo=utc)\
    .astimezone(timezone('Europe/Paris'))

in_3_months = relativedelta(months=3)

It increased the current date by 3 months. To do this manually, either you work on the "date" representation which means that you have to increase the year if needed, either you work on the timestamp and you have to know the length of the 3 next months. In either cases, it's a pain to do by hand.

This outlines how important it is to be able to express transformations in human terms and have a library to do the job of getting the correct result.

In other words, time is usually intended to be read by humans and the abstraction that humans have put over time is very very leaky. There is a lot of exceptions to account for and yet your dates will have to be mapped back and forth to this abstraction. If you're going to do that properly, you need to choose carefully which tools and models are going to represent your human's intents in order to get results that translate accurately the specifications.