Always Store Dates in UTC

It all went rather wrong in the early 2000s when we realised that the the last Sunday in October had two hours that both claimed to be 1am.

Willy Lott’s Cottage

In the mid 1990s the company I was working for made an intelligent GPS radio modem. It was a neat little device that you could put in a car and it could do some basic processing, even run a small touch screen, and could communicate remotely with a central server.

We think nothing whatsoever of this today – the smartphone that most of carry all the time is way more capable than anything we were dreaming of then. In the 1990s however the idea that you could communicate with a vehicle and that it could tell you where it was, that was pretty cool.

Government organisations, in particular, were keen on us. There were other solutions on the market that were cheaper to buy, but we had the lowest running costs. Sending data across the airwaves was expensive and we spent a lot of time and effort to make sure that it was done as usefully and efficiently as possible.

For various reasons, some of which were historic, the Product Manager decided that it should operate in local time (including daylight savings time). Unfortunately it was also local time that got send back to the server. To make matters worse, to save on over-air costs, we cut the data right back to just the time. We didn’t include timezone or daylight savings information.

That might sound stupid now, but back then the primary business requirement was very much operational: customers wanted to know where their fleet was at that moment. Historical reporting was distinctly secondary.

My first job at the company was to change that, to develop a historical reporting function. That’s when we started to notice problems.

When British Summer Time switches back to Greenwich Mean Time we say that “the clocks go back”, we get to 2am and then we put the clocks back to 1am. Our intelligent radio modems faithfully did this, when they got to 2am they reset the clock to 1am. The result was that, in our database, we had two sets of records both claiming to be between 1am and 2am and it could be impossible to tell which was which.

If you’re a business this is a pretty irritating, but it’s something you can live with. When your customers are The Police and they need to know for evidential purposes where a vehicle was at any given time, this is a serious problem.

It was an easy enough software fix, to make the device always send the base time, not daylight savings time. Rolling those updates out and dealing with the potential data problems caused by the switch was far from trivial.

Hot on the heels of that problem, we then sold a system to a country that had multiple timezones.

Fortunately we predicted the problem this time. We locked ourselves in a room and went through every scenario we could think of, every way of working it and the conclusion we came to was that the date and time should always be sent and always stored in UTC. That way you know the data is always valid, it’s an absolute, there’s no question.

The situation has changed a little now because data storage and communication is much, much cheaper. There’s no reason for us not to send all the qualifying data. Back then however every single bit mattered.

As a systems integrator however I still run into problems with times quite a lot. Customers often tell me that this system works in UTC whereas some other system works in local time. They’re usually wrong, under the covers most systems either fully qualify the date and time or use UTC and convert for display. It’s not always the case though and APIs are not always explicit about it either.

My advice is simple: never store or transmit (internally) an unqualified time that is not UTC. If you’re using local time always make sure that it is qualified with the timezone information (including any applicable daylight savings). Never let the base time get separated from its qualifying data. This is still an easy mistake to make when writing to a database or transmitting via an API. If, for some reason, you cannot transmit or store the qualifying information, convert to UTC.

In the .NET Framework CLR there are 2 DateTime types that you should be aware of: DateTime and DateTimeOffset. Microsoft have produced some guidance on when to use which, (but basically use DateTimeOffset if you have the qualifying data, use DateTime for UTC).