The majority of the projects I've worked on in my career existed in a single time zone and used on-premises hardware. But how we handle systems that span time zones has changed and continues to change, and dealing with those changes is not always intuitive. As the cloud becomes more pervasive, it's becoming increasingly likely that, even if your client remains in only in a single time zone, their databases, services, and UIs may live in different time zones. And who knows, maybe someday your customer will expand or move. That's why I advocate building all new systems with the capability to handle multiple time zones.

In the past, not only did we not have a good way to handle multiple time zones, but each of the tools we used to build our systems had different support for dates, times, and time zones. In order to come up with a good solution, we often had to create multiple columns in our databases and multiple properties on our classes and keep them all in sync ourselves. A lot of developers wrote custom code to handle it and in some cases, we still have to do that today. But if you're a Microsoft stack developer, there are now pretty good ways to deal with this type of data built right into the tools.

The DateTimeOffset data type was originally introduced way back in .NET 2.0 at the end of 2005. Like its sibling the DateTime data type, it could store a date and/or time, but it added a third component, called the offset, which defaults to the current offset of the local time zone from Coordinated Universal Time (UTC it's not CUT because the acronym comes from the French language). For example, I live in Houston where the offset during Daylight Savings Time is -5 hours. That is, it's five hours earlier in Houston than UTC. UTC, sometimes referred to as Zulu because it's denoted with a “Z” next to the offset when written out, is a more precise standard than Greenwich Mean Time (GMT), which is the official time at the Royal Observatory in Greenwich, England. For the most part, you can think of UTC, Zulu, and GMT as basically the same thing. So when I say it's 1:41PM -5 here in Houston, it's the same as saying it's 6:41PM -0 UTC or 4:41AM +10 tomorrow morning in Canberra. When my services and database are in Azure (which uses the UTC time zone regardless of the data center's physical location) and timestamps something as 6:41PM (-0), I can see that that timestamp happened at 1:41PM local time (-5).

If you stored your data in SQL Server as many of us did, you had to wait three more years until SQL Server 2008 was released before you could write your .NET DateTimeOffset value into the database. Prior to that, you had to use one column to store the date and time and another to store the offset, and, if you were good, you knew enough to make sure your time offset column included decimal places so you could save offsets that weren't whole hours such as the +5:45 offset (5.75) used in parts of Europe and Africa. Most modern databases and programming languages support DateTimeOffset data types or an equivalent, so your best bet when writing a new system is to use DateTimeOffsets everywhere. When you do so, you record a moment in time that works across all time zones.

If it were only that easy! Although this strategy works for most scenarios, there are some interesting twists to be aware of.

If You Only Want to Store a Date

Although SQL Server also introduced data types that stored only a Date or only a Time in 2008, there are no matching data types in .NET. I've often felt this was an oversight. A mistake that many people make when storing only a date is using the DateTime type.

It's a mistake for several reasons: It's an especially dangerous situation because there's a quirk that only rears its head sometimes: when the time offset is great enough to change the time enough to cross midnight, which changes the date. In my scenario here in Houston, if I send my birthday as a DateTime of 9/29/1964 00:00:00 (midnight) to a service in Azure, it might subtract five hours (six in winter) and show up as 9/28/1964 making me a day older than I actually am! If I unintentionally send the current time along with the date, this bug shows up at different times of the day than in the cases where I'm careful to send the value as midnight.

Kind or Unkind?

You might be wondering why I said might subtract five hours (six in winter). That's an excellent question and one that baffled me for a while until I dug deep into the documentation of the .NET DateTime data type. The culprit is the Kind property, which was added to the DataTime structure in .NET 3.0. Kind is an enum that can be one of three values: Unspecified, Local, or UTC. The behavior we all seem to expect is when this property is set to the default value of DateTimeKind.Unspecified, which basically says to ignore any concept of an offset. If I specify 9/29/1964 00:00:00 in Houston and send it to Azure which is using UTC anywhere else in the world, the value is read as 9/29/1964 00:00:00. The documentation also says that you can use the DateTime.SpecifyKind() method to set this value. If you specified to use local time or UTC time, you can do that to and know what to expect.

What the documentation doesn't clearly specify is that some properties and methods on the DateTime type set the Kind property FOR YOU.

What the documentation doesn't clearly specify is that some properties and methods on the DateTime type set the Kind property FOR YOU. You can only find that documented in those methods. For example, if I wrote this bit of code:

var n = DateTime.Today;

You see that the Kind property is set to Local, but if I run this code:

var n = new DateTime(n.Year, n.Month, n.Day);

You see that the Kind property is set to Unspecified.

In the debugger, these DateTime values look identical except for the Kind property which few people know to check. If I send both of these values from my app running in Houston to a service in Azure to be stored in a database, I might get different results when I ask for the data back. The value in the first example will likely be adjusted by -5 hours. If I store the value in the database in a DateTime column, then it will be five hours off and if I store it in a Date column, it will be stored as yesterday.

Why? What's the difference between these two examples? When you use things like DateTime.Now or DateTime.Today, the Kind property is set to Local to indicate that it was set from the computer's local clock. However, when you create a DateTime with explicit values for year, month, and day, the local clock isn't used (n.Year, n.Month, and n.Day are just integers in this context) and the Kind property is set to Unspecified.

The bottom line is, use DateTimeOffsets when you can. They save a lot of headaches, even if you don't care about the offset. Alternatively, you can create your own .NET type to wrap a DateTime so that the Kind property is always set to Unspecified whenever a value is set. Although that smells a bit like a hack, it works and you may be stuck with it on some projects.

If You Only Want to Store a Time

As a .NET developer, you have a couple of choices. The TimeSpan class is very handy for manipulating hours, minutes, seconds, or ticks, and it maps relatively well to SQL Server's Time data type. Alternately, you can store the hours, minutes, or seconds since midnight in an int in both .NET and SQL Server or, if you need more precision, you can store ticks since midnight as a four-byte integer (a long in .NET and a bigint in SQL Server).

A Word about Time Zones

Time zones and offsets are not the same thing. One problem that DateTimeOffsets don't solve is storing the originating time zone. Knowing the offset isn't enough to tell you which time zone it came from. For instance, Arizona doesn't observe Daylight Savings Time, so in summer it's the same time in California as it is in Arizona, but in the winter, it's not. Also, there are plenty of instances where time zones overlap. If you need to store data with time zones or convert DateTimeOffsets to a particular time zone, see .NET's TimeZoneInformation class, introduced in version 3.5.

An Unusual Cases

There will always be a few cases that don't fit the mold. For example, I recently worked with a client who scheduled appointments for their customers. To them, a 10AM appointment on July 1 should show up as a 10:00 AM appointment on July 1, no matter which time zone it's viewed in. If an associate in a branch in London looks at a schedule for a branch in Los Angeles, they don't want to see that appointment showing up as 2AM (which is what time it would actually take place). In cases like this, it's best to store a DateTimeOffset along with TimeZoneInfo, and use the DateTimeOffset.ToOffset() method to get whichever version of the appointment time is required for the situation. Alternately, you could use a DateTime and ensure that the Kind property is set to Unspecified. This has the disadvantage of never knowing exactly when something actually happened. In this scenario, you'll just never know exactly when a 5PM appointment starts, but hey, it's 5 o'clock somewhere!

Summary

I hope this has cleared up at least few questions for you and maybe even got you thinking a bit! Dates and times are intuitive, but actually quite difficult. Time zones, daylight savings time, leap years, leap seconds, and even different calendars all come in to play. Fortunately, .NET gives us a pretty good arsenal of tools to work with to handle the various situations. My wish list includes adding Date and Time data types to .NET and introducing a DateTime-like type that ignores offsets so that it works consistently. I don't think it's going to happen, but I'll give it some time.