The Blog of Ian Mercer.

DateTime values should always be stored in UTC

Developers are sometimes confused about this issue but the fact of the matter is that you should always store Datetime values in your database in UTC and almost never in a local time zone.

Canyon de Chelly
Ruins

Canyon De Chelly, Az/NM border: One of the many places where naive use of local time will cause you problems!

Creative Commons Licensephoto credit: JBColorado

You should use UTC because:-

​1) There are no ambiguous or missing times in UTC time. Any timezone with daylight savings time has a spring-forward gap and a fall-back repeated hour. That messes up all manner of calculations: How many hours is it from 2:05AM to 2:07AM? In UTC time it's always 2 minutes but in local time it could be 2 minutes or it could be 1 hour and 2 minutes or it could be 'error no such time'.

​2) All your times sort correctly even if they were entered by users in different timezones. So, user A commenting on user's B comment on user C's photo doesn't have their comments listed in the wrong order even if user C is in Europe, user A is in Japan and user B is in the USA. And user D gets to see the whole thread in whatever timezone they are in because you convert UTC to local on the way out in the user's local timezone.

​3) Daylight savings time rules change from time-to-time. To be able to reliably say how many hours it is from DateTime A to DateTime B you would need to store every historical variation in the DST rules in order to be able to calculate that correctly, and even then because of (1) you couldn't calculate it in every case because there would be ambiguities.

​4) Worse even that (3) is the fact that the boundaries of timezones are not constant but have changed over time. So your database of historical timezone information also needs to include accurate geographical information for every time zone AND you need to record the location of every local datetime value in order to be able to calculate when it happened in UTC time (and even then (1) causes problems).

​5) If you think you understand timezones completely and can deal with all these issues go read up on Navajo Indian Reservations, fractional time zones and then consider what happens if you are recording datetime values associated with a moving vehicle.

Storing datetime values in UTC is the only sensible answer to all of these issues.

One of very few exceptions to this rule is when you have a local datetime that you cannot convert to UTC time because you can't be sure of the timezone, e.g. Datetimes from digital cameras or camcorders where all you have is a local time and have no idea what timezone it is in. In such cases you can store a local datetime but you really have very little ability to do much with it apart from redisplay it.

The other one to watch for is where you have an event at a given time in a given location, e.g. 10AM: Family Reunion in Taos, New Mexico. In this case you need to do the UTC conversion in some timezone other than the one your user is in right now. And you want to store the fact that it is a time that should be shown in the local New Mexico timezone not the user's timezone.

Grouping is another key issue - you need to convert your UTC times into local times BEFORE you group them into days because my 'today' might not be the same as your 'today'. This complicates your ability to perform grouping in your database.

UTC is the only sensible format to store date time information in a database. If you can't generate a UTC datetime from the input then the value you have may as well be stored as a string because there's very little you can do with it without a lot of extra effort!

Related Stories

Cover Image for Xamarin Forms Application For Home Automation

Xamarin Forms Application For Home Automation

Building a Xamarin Forms application to control my home automation system

Ian Mercer
Ian Mercer

JSON Patch - a C# implementation

Ian Mercer
Ian Mercer

Dynamically building 'Or' Expressions in LINQ

How to create a LINQ expression that logically ORs together a set of predicates

Ian Mercer
Ian Mercer

VariableWithHistory - making persistence invisible, making history visible

A novel approach to adding history to variables in a programming language

Ian Mercer
Ian Mercer

Updated Release of the Abodit State Machine

A hierarchical state machine for .NET

Ian Mercer
Ian Mercer

Building a better .NET State Machine

A state machine for .NET that I've released on Nuget

Ian Mercer
Ian Mercer
Cover Image for The Internet of Dogs

The Internet of Dogs

Connecting our dog into the home automation

Ian Mercer
Ian Mercer

A simple state machine in C#

State machines are useful in many contexts but especially for home automation

Ian Mercer
Ian Mercer

Convert a property getter to a setter

Ian Mercer
Ian Mercer

MongoDB Map-Reduce - Hints and Tips

Ian Mercer
Ian Mercer
Cover Image for Weather Forecasting for Home Automation

Weather Forecasting for Home Automation

Ian Mercer
Ian Mercer

Lengthening short Urls in C#

Ian Mercer
Ian Mercer

ASP.NET MVC SEO - Solution Part 1

Ian Mercer
Ian Mercer

Building sitemap.xml for SEO ASP.NET MVC

Ian Mercer
Ian Mercer

Tip: getting the index in a foreeach statement

A tip on using LINQ's Select expression with an index

Ian Mercer
Ian Mercer

WCF and the SYSTEM account

Namespace reservations and http.sys, my, oh my!

Ian Mercer
Ian Mercer

404 errors on IIS6 with ASP.NET 4 Beta 2

Ian Mercer
Ian Mercer

Mixed mode assembly errors after upgrade to .NET 4 Beta 2

Fixing this error was fairly simple

Ian Mercer
Ian Mercer

The EntityContainer name could not be determined

How to fix the exception "the entitycontainer" name could not be determined

Ian Mercer
Ian Mercer

Shortened URLs should be treated like a Codec ...

Expanding URLs would help users decide whether or not to click a link

Ian Mercer
Ian Mercer

A great site for developing and testing regular expressions

Just a link to a site I found useful

Ian Mercer
Ian Mercer

Entity Framework in .NET 4

Ian Mercer
Ian Mercer

System.Data.EntitySqlException

Hints for dealing with this exception

Ian Mercer
Ian Mercer

Exception Handling using Exception.Data

My latest article on CodeProject covers the lesser known Exception.Data property

Ian Mercer
Ian Mercer

ASP.NET Custom Validation

How to solve a problem encountered with custom validation in ASP.NET

Ian Mercer
Ian Mercer

Optimization Advice

Some advice on software optimization

Ian Mercer
Ian Mercer

Linq's missing link

LinqKit came in handy back in 2009

Ian Mercer
Ian Mercer

Cache optimized scanning of pairwise combinations of values

Using space-filling curves to optimize caching

Ian Mercer
Ian Mercer

Threading and User Interfaces

A rant about how few software programs get threading right

Ian Mercer
Ian Mercer