DateTime Pitfalls

If you find some of these things in your application you should double check if your application works correctly with dates and times:

  1. Usages of non-UTC aligned date and time
  2. Usages of GETDATE() in SQL with the dates from the client without adjustments
  3. User input without time zone adjustments
  4. Calculations on dates without time zone adjustments
  5. Duration measurements with DateTime

If users of your application are located in the same time zone it should be OK. But if you need to support several time zones beware about DateTime and related issues with parsing, DST (Daylight Saving Time) and conversion between time zones. The rule of thumb “UTC all the time” can solve a lot of issues, there are some cases when it is not enough to adjust dates to it. Below I summarized my finding about pitfalls with date/time values.

DateTime Pitfalls

The rule of thumb which tells you to store date/time values in UTC and convert it back to local values only at moments when it’s shown to users works perfectly. Especially when you track system events or user activity (logs, audit of actions) which is not related to the business transactions. In business related cases and when you do calculations upon dates the exclusion of time zone information may cause issues. Let take a closer look at the following cases:

  • When user wants to see data in the system for another time zone than the one he is located in.
  • When user wants to see reports with data from several time zones and data should be presented in its local time.
  • When user schedules an event in the future in a time zone which supports Daylight Saving Time (DST) transitions.

The main issue with DateTime is that its value has no relation to concrete time and depends on how you read it. Of course there is DateTimeKind which should help you, but  it is not as helpful as it may seem.

Beware of DateTimeKind.Unspecified

The usage of x.ToUniversalTime() will subtract your local time zone offset X, and usage of x.ToLocalTime() will add X. If the value of DateTime does not have specified DateTimeKind you will get different results depending on the method. This can be a cause of issues in your application. The code example you can find here. In the example DateTime is created with DateTimeKind.Unspecified. Below table presents results which you will get for the methods.

new DateTime(2017, 1, 14, 1, 30, 0) .ToUniversalTime() .ToLocalTime()
{0} 14/01/2017 01:30:00 13/01/2017 23:30:00 14/01/2017 03:30:00
ISO 8601 {0:O} 2017-01-14T01:30:00.0000000 2017-01-13T23:30:00.0000000Z 2017-01-14T03:30:00.0000000+02:00
RFC 1123 {0:R} Sat, 14 Jan 2017 01:30:00 GMT Fri, 13 Jan 2017 23:30:00 GMT Sat, 14 Jan 2017 03:30:00 GMT
Sortable {0:s} 2017-01-14T01:30:00 2017-01-13T23:30:00 2017-01-14T03:30:00
UTC Sortable {0:u} 2017-01-14 01:30:00Z 2017-01-13 23:30:00Z 2017-01-14 03:30:00Z
UTC full {0:U} 13 January 2017 23:30:00 13 January 2017 23:30:00 14 January 2017 01:30:00

You may say that using of DateTimeKind.Utc or DateTimeKind.Local will solve the issue. This is true unless you are converting DateTime to string representation. You can notice that only two of the above formats keep DateTimeKind  information: UTC sortable and ISO 8601[1]. You may also notice that formats “U” and “u” behave differently.

Serialization of DateTime Cuts off DateTimeKind

When we say “serialization” it means conversion of DateTime to and from string value. Although DateTime provides enough routines to correctly do this, the solution is too fragile. Let’s try to parse the value which we get in JSON envelope. In most of the cases you’ll get one of the four formats (see the table below). Assume we know that this is UTC time, so that it can be parsed to values with UTC kind DateTime.Parse(string) and DateTime.FromBinary(long). The result in the 3rd column shows that the kind is not initialized correctly. So that next usage of .ToUniversalTime() broke the value completely. In order to solve this we may use the advanced version of Parse method to specify the kind DateTime.Parse(string, IFormatProvider, DateTimeStyles). The 4th column shows that it does not work still for the first string. The example code is here.

Format Value in JSON .Parse(string)
.FromBinary(long)
.Parse(string, null, AdjustToUniversal)
.FromFileTimeUtc(long)
Sortable 2017-01-14T01:30:00 Parsed: 2017-1-14 1:30:00
Kind: Unspecified
Parsed: 2017-01-14 01:30:00
Kind: Unspecified
ISO 8601 2017-01-14T01:30:00.0000000Z Parsed: 2017-1-14 3:30:00
Kind: Local
Parsed: 2017-01-14 01:30:00
Kind: Utc
Zulu time 2017-01-14 01:30:00Z Parsed: 2017-1-14 3:30:00
Kind: Local
Parsed: 2017-01-14 01:30:00
Kind: Utc
Long integer (ticks) 636199542000000000 Parsed: 2017-1-14 1:30:00
Kind: Unspecified
Parsed: 2017-01-14 01:30:00
Kind: Utc

The kind can be set explicitly, but this is still not a good solution, because it depends on too many assumptions: developers should follow the same approach, communicating systems should use the same approach, servers should use UTC time zone, or at least don’t change it. If someone make a mistake it will be hard to find and recognize.

Comparison of DateTime Ignores UTC/Local

Comparison of DateTime is not that easy with multiple time zones. If you try to compare two DateTime values which represent the same time in different time zones you’ll get wrong result. This is because the DateTime comparison doesn’t use its kind.

The comparison should result to True, but there is no way to DateTime to understand these two dates represent the same time. Code example is here.

Beware of Daylight Saving Time

From the examples above you may start thinking that the storing time zone information along with time in UTC will solve everything. This is almost true. When you’re scheduling an event in the future which should happen in the specific local time you’ll get issues.

The one side of the problem is that the time zone offset could be changed by government. That means if UTC time was stored before the change it will be converted back to local incorrectly. The time offset won’t help also. In order to solve this you can store the local representation of DateTime.

The other side of the problem is related to computation of the next occurrences of the event. Your system will schedule incorrect times for events using UTC if DST happens during the events sequence. Because UTC is not affected by DST it will be ignored during the calculation of the sequence events, but when the time will be converted to the local representation the DST time offset will be applied. The code example below illustrates the case.

Missing and Happening Twice Hour

If your application is sensitive to date/times and work during DST transitions you might have issues. Because of the added or subtracted time offset such days are 23h and 25h long. Also one hour will be skipped and the other will be tracked twice.

Spring Forward Fall Back

GMT line represents the local time which you can see on your watch. You may see that the time in DB does not correspond to the local time without other information.

Ambiguous Time

The nature of DST brings ambiguous user input to the game. Your application should be protected from the non-existing values like 2017-03-26 01:30:00 in GTM. Because of the spring forward transition this date will never happen. During the fall back transition two events could happened one after another in one hour. Because DateTime is stored as UTC there is no way to understand that without the time offset.

DateTimeOffset and TimeZoneInfo

Wrapping up everything above it should be clear that the concrete time can be specified with three characteristics together: date/time + offset + time zone. UTC maybe enough for logs and audit data, but for business related data when we need to know moment according to the location we need also a time zone. The time offset is need only for the case with ambiguous time during DST. SQL Server (starting with 2008) and .NET Framework (starting with 3.5) both support the type DateTimeOffset which stores the local time with offset from UTC. It also implements related logic for the comparison and conversion.

You should not care about DateTimeKind anymore, because DateTimeOffset will always carry the time offset with the DateTime value. So that the methods .ToUniversalTime() and .ToLocalTime() will behave correctly.

The serialization/deserialization of dates have the same pitfalls with one exception. The specified time offset will be preserved in DateTimeOffset.

The comparison is easy and simple.The same example with two dates representing the same time in different time zone will show true as it should.

DST related problems affect DateTimeOffset in the same way. For example, the time for the sequence of events will slide without correction to DST. The time offset is required to solve ambiguous times issue and distinguish between two moments with the same UTC time. If such values would store with the offset there is no way to misunderstand when the event is happened.

Wrap Up

Kind (UTC/Local) Serialization Comparison Scheduling Daylight Saving Time
DateTime Use DateTimeKind. Can point only to local or UTC. Kind can be assumed, but it is not preserved and can be lost. Ignores the kind. Required coding. The simplest solution is to use local time. Solved with TimeZoneInfo. Ambiguous dates in database.
DateTimeOffset Use the time offset. Can point to any time offset. The time offset can be explicitly specified, it is preserved in the value always. If it is not specified the local time offset will be used. Solved. Solved with TimeZoneInfo. Protected from ambiguous dates.

See Also

  1. MSDN: Standard Date and Time Format Strings
  2. MSDN: Coding Best Practices Using DateTime in the .NET Framework
  3. Noda Time: What’s wrong with DateTime anyway?
  4. The case against DateTime.Now
  5. General Information About Time Zones
 



No Comments


You can leave the first : )



Leave a Reply