Midnight Universal Time is not Midnight Local Time!

A few of my peers have recently move to the cloud and some have found out that we can no longer ignore time zones!

DateTime is an object that is familiar to us. Most of the time we use it without any second thoughts. There lies our first mistake. Not using the DateTimeOffset in code that runs on the cloud can result in some strange and unwanted behavior.

The reason DateTime is usually the reason why our dates are off, is that by default it creates dates in local time. To create a date in UTC you are required to specify a DateTimeKind.Utc in the date’s constructor.

Imagine the following scenario. A developer is executing code in the Windows Azure emulator to debug the code before deploying to the production environment on the cloud. The configurations of the code running in the emulator if pointing to the production database and the code is creating dates using the DateTime object. These dates are then inserted into Windows Azure SQL Database. Then the developer, who is satisfied with the results, goes to production with the new code.

A few days go by, then suddenly a client calls to report weird dates and that the application isn’t working as expected. Tickets are created in the past! A second customer also reports this weird behavior, but he reports that dates are in the future!  Just so we’re clear, time travel is not a possible cause…

At this point, the developer starts to debug locally, after an hour he starts seeing odd times in the database. The confused developer reports that date that should have been saved with a time of midnight, but that he is finding that only some of the dates are save with the right time. Oddly, the correct dates are the dates which were create during the initial debug session prior to the initial deployment.

A few weeks ago I wrote about setting the time zone of your development machine to UTC (Coordinated Universal Time). This recommendation was a direct result from going through an experience similar to the scenario described in this post.

I found that whenever a non-UTC DateTime is inserted into Windows Azure SQL Database, it will be converted to its Greenwich equivalent. This means that if my machine, which is GMT –5, creates a DateTime and inserts ‘2013/01/01 00:00:00’ it into SQL Database, the queries will return ‘2012/12/31 19:00:00’.

A DateTime value defines a particular date and time. Starting with version 2.0 of the .NET Framework, it includes a Kind property that provides limited information about the time zone to which that date and time belongs. […] Unless a particular DateTime value represents UTC, that date and time value is often ambiguous or limited in its portability. For example, if a DateTime value represents the local time, it is portable within that local time zone (that is, if the value is deserialized on another system in the same time zone, that value still unambiguously identifies a single point in time). Outside the local time zone, that DateTime value can have multiple interpretations. If the value’s Kind property is DateTimeKindUnspecified, it is even less portable: it is now ambiguous within the same time zone and possibly even on the same system on which it was first serialized. Only if a DateTime value represents UTC does that value unambiguously identify a single point in time regardless of the system or time zone in which the value is used.

To solve this problem, the developer will need to use the DateTimeOffset. Doing so will remove ambiguity, it will also simplify the creation of UTC dates. Consequently it will reduce the amount of errors related to dates. This is especially important where systems depend on dates and times.

The DateTimeOffset structure represents a date and time value, together with an offset that indicates how much that value differs from UTC. Thus, the value always unambiguously identifies a single point in time. Uses of the DateTimeOffset include

— Uniquely and unambiguously identify a single point in time. The DateTimeOffset type can be used to unambiguously define the meaning of "now", to log transaction times, to log the times of system or application events, and to record file creation and modification times.

— Preserve multiple related times, as long as those times are stored as two separate values or as two members of a structure.

Furthermore a DateTimeOffset value is not tied to a particular time zone, but can originate from any of a variety of time zones.

An example from MSDN shows how you can take a DateTimeOffset instance and identify its possible time zone. This is extremely useful when you wish to show the time zone and the time of day within the specified
time zone. In many circumstances, this will convey much more information that showing the end user local time. for example, imagine that you are reading a ticket about an issue that occurred during the night. The user probably did not call support at 3:00 AM, they probably called sometime during the day. By being shown the local version of the time, you are effectively missing context from the ticket. A good compromise in this situation would be to display the  time and the corresponding time zone. Then by displaying the time in local time when the mouse is placed over the date.

When querying the database using a date that is specified by the end user, it is important to convert the date to its corresponding DateTimeOffset then to use its UTC DateTime  to query the database. Not converting the date entered through the UI will result in trying to filter UTC dates using a local DateTime. The results will probably not satisfy the end user’s expectations.

Its important to note, that saving dates in UTC isn’t limited to SQL Database. This can also be quite important when working with Windows Azure Table Storage Service or any other service that has the ability of storing dates like the Windows Azure Blob Storage Service .

Keep in mind that midnight universal time isn’t midnight local time for everyone.

Examples

Remember that the following creates a date in local time

var date = new DateTime(2013, 01, 01, 0, 0, 0);

The following example create UTC dates

var dateUtc1 = new DateTime(2013, 01, 01, 0, 0, 0, DateTimeKind.Utc);

var dateUtc2 = new DateTimeOffset(2013, 01, 01, 0, 0, 0, TimeSpan.Zero).UtcDateTime;

//The DateTimeOffset will carry much more information than
//the previous DateTime examples
var offsetUtc = new DateTimeOffset(2013, 01, 01, 0, 0, 0, TimeSpan.FromHours(-5));
            
var localTime = offsetUtc.LocalDateTime;
var utcTime = offsetUtc.UtcDateTime;
var timeZoneOffset = offsetUtc.Offset;

 

References

• Choosing Between DateTime, DateTimeOffset, and TimeZoneInfo
• DateTime
• DateTimeOffset
• TimeZoneInfo