Time zones in .NET

12 Sep 2013

I’m a fairly new .NET developer. I worked with the framework a bit in 2005–6,
but hadn’t really touched it since then. In the meantime, I’ve been sticking
to the Linux ecosystem, and a little OS X, as mentioned in a previous
article.

So, time zones. I know they’re a sore point for many environments, but most
seem to dig themselves out of the hole and provide something that is, in the
end, usable.

Ruby’s builtin Time is actually pretty darn good, and if you use Rails,
ActiveSupport makes it even better. pytz seems .. alright. Databases
generally have their heads screwed on straight. A lot of the time you can get
away with just storing seconds since the epoch and call it a day, because
there’s nothing more intrinsic built into the system.

Then I got my new job, and it was time get back into
.NET. A lot has changed since 2006; it had only hit 2.0 then, mind.

So I felt confident I was using the latest, modern stuff. We target 4.0 and
4.5 across our projects, and there’s plenty nice about it.

Then I had to work with System.DateTime. Oh. Oh, gosh.

I quote the manual at you.

DateTime.Kind Property

Gets a value that indicates whether the time represented by this instance is
based on local time, Coordinated Universal Time (UTC), or neither.

Kind is the lone field on a DateTime which has anything to do with time
zones. It can take the value Local, Universal, or Unspecified. What
does that even MEAN. Note that Kind is ignored in comparisons, too, which
can only mean more fun for application developers.

It would be remiss of me to fail to note the paragraph in the docs which state:

An alternative to the DateTime structure for working with date and time
values in particular time zones is the DateTimeOffset structure. The
DateTimeOffset structure stores date and time information in a private
DateTime field and the number of minutes by which that date and time differs
from UTC in a private Int16 field. This makes it possible for a
DateTimeOffset value to reflect the time in a particular time zone, whereas a
DateTime value can unambiguously reflect only UTC and the local time zone’s
time. For a discussion about when to use the DateTime structure or the
DateTimeOffset structure when working with date and time values, see
Choosing Between DateTime, DateTimeOffset, and
TimeZoneInfo.

The linked page states that “although the DateTimeOffset type includes most of
the functionality of the DateTime type, it is not intended to replace the
DateTime type in application development.”

Intention or not, it should ALWAYS be used. It lists as a suitable
application for the DateTimeOffset:

Uniquely and unambiguously identify a single point in time.

Because we don’t want that at any other time? When do you want a DateTime
which non-specifically and ambiguously identifies several points in time?

On the other hand, listed as suitable for DateTime:

Retrieve date and time information from sources outside the .NET Framework,
such as SQL databases. Typically, these sources store date and time
information in a simple format that is compatible with the DateTime
structure.

No fucking comment.

It continues:

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 DateTimeKind.Unspecified, 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.

Enter Npgsql, a Postgres driver for
.NET with a frightening amount of code. It only works with DateTime objects
when sending or receiving timestamps to or from Postgres.

Postgres has two column types: timestamp with time zone and timestamp
without time zone (or timestamptz and timestamp, respectively). The
former is about as good as a DateTime, but without trying to be more than it
can: it doesn’t have Kind, which improves its usability by an order of
magnitude. You can make a policy decision like “we’ll always store UTC
timestamps”, and you’ve solved timezones in your application. They mark a
specific point in time unambiguously.

Or you can just use timestamptz and they still unambiguously mark a specific
point in time. It’s magic!

So how does Npgsql deal with this?

The genesis of this post was because we were noting strange behaviour: we had
read a timestamptz out of the database, and then later SELECTed all rows
where that column was strictly less than the value we read out. And yet that
same row would be included in the result. It made no sense.

Turns out it really did make no sense.

The rest of this blog is a sequence of test cases which demonstrate just how
bad the situation is.

[SetUp]publicvoidSetUp(){_connection=newNpgsqlConnection("Host=localhost; Port=5432; Database=time_zones_in_dot_net; "+"User ID=time_zones_in_dot_net");_connection.Open();}[TearDown]publicvoidTearDown(){_connection.Close();}[Test]publicvoidTimeZonesSane(){// introduction
// ------------
// This test assumes the *local* machine (running NUnit) has local time of +10.
// It's agnostic to the time zone setting on the database server.
// In other words, Postgres +1, .NET -100000000.
// Render UTC (+0), Postgres (+3) and .NET (+10) distinguishable.
_connection.Execute("SET TIME ZONE '+3'");// In the below tests we assert that the queries yield a .NET DateTime object
// which, when .ToUniversal() is called on it, produces the given date in
// "mm/dd/yyyy HH:MM:SS" format.
// After that is the non-.ToUniversal() date in parenthesis. This is *always* 10
// hours ahead for a Local or Unspecified, and the same for Utc. DateTime
// objects have no knowledge of offset, only Kind.
// There's also a character appended to represent what time zone "kind" it came
// back with; "L" for Local, "?" for Unspecified, "U" for Universal.
// As noted below, ToUniversal() on a Local or Unspecified returns a new DateTime
// with Kind set to Universal, and unilaterally subtracts the time zone offset of
// the machine the code is running on.
// tests using string literals
// ---------------------------
// Not useful in themselves, because we'll never use string literals, but help to
// demonstrate some initial weirdness.
// string timestamp time zone unspecified: assumed to be in database local time.
// Returns with Local Kind.
QueryEqual("09/11/2013 03:47:03 (09/11/2013 13:47:03) L","SELECT '2013-09-11 06:47:03'::timestamp with time zone");// string timestamp with time zone: should come back with the correct universal
// value, with Local Kind.
QueryEqual("09/11/2013 05:47:03 (09/11/2013 15:47:03) L","SELECT '2013-09-11 06:47:03+1'::timestamp with time zone");// string timestamp without time zone: comes back 'unspecified' with the exact
// datetime specified. ToUniversal() assumes unspecified = local, so -10.
// Returns with Unspecified Kind.
QueryEqual("09/10/2013 20:47:03 (09/11/2013 06:47:03) ?","SELECT '2013-09-11 06:47:03'::timestamp without time zone");// string timestamp with time zone, coerced to not have a time zone: as if the
// time zone wasn't in the string. Returns with Unspecified Kind.
QueryEqual("09/10/2013 20:47:03 (09/11/2013 06:47:03) ?","SELECT '2013-09-11 06:47:03+1'::timestamp without time zone");// tests using .NET values as parameters
// -------------------------------------
// These represent what we'll usually do. They're also really messed up.
// unadorned parameter: regardless of the DateTimeKind, the date is treated as
// without time zone; the exact date given comes back, but with Unspecified Kind,
// and so is as good as forced to local time.
DateTimesEqual("09/10/2013 20:47:03 (09/11/2013 06:47:03) ?","SELECT @DateTime",newDateTime(2013,9,11,6,47,3,DateTimeKind.Local),newDateTime(2013,9,11,6,47,3,DateTimeKind.Unspecified),newDateTime(2013,9,11,6,47,3,DateTimeKind.Utc));// parameter specified as with time zone: regardless of the DateTimeKind, the
// date is treated as in the database local time. It comes back with Local Kind.
DateTimesEqual("09/11/2013 03:47:03 (09/11/2013 13:47:03) L","SELECT (@DateTime)::timestamp with time zone",newDateTime(2013,9,11,6,47,3,DateTimeKind.Local),newDateTime(2013,9,11,6,47,3,DateTimeKind.Unspecified),newDateTime(2013,9,11,6,47,3,DateTimeKind.Utc));// parameter specified as without time zone: as for unadorned parameter.
DateTimesEqual("09/10/2013 20:47:03 (09/11/2013 06:47:03) ?","SELECT (@DateTime)::timestamp without time zone",newDateTime(2013,9,11,6,47,3,DateTimeKind.Local),newDateTime(2013,9,11,6,47,3,DateTimeKind.Unspecified),newDateTime(2013,9,11,6,47,3,DateTimeKind.Utc));// discussion
// -----------
// DateTime parameters' kinds are ignored completely, as shown above, and are
// rendered into SQL as a 'timestamp' (== 'timestamp without time zone'). When a
// comparison between 'timestamp with time zone' and timestamp without time zone'
// occurs, the one without is treated as being in database local time.
// Accordingly, you should set your database time zone to UTC, to prevent
// arbitrary adjustment of incoming DateTime objects.
// The next thing to ensure is that all your DateTime objects should be in
// Universal time when going to the database; their Kind will be ignored by
// npgsql. If you send a local time, the local time will be treated as the
// universal one.
// Note that, per the second group just above, 'timestamp with time zone' comes
// back as a DateTime with Local Kind. If you throw that right back into npgsql,
// as above, the Kind will be summarily ignored and the *local* rendering of that
// time treated as UTC. Ouch.
// conclusions
// -----------
// 'timestamp with time zone' is read as DateTime with Local Kind. Note that the
// actual value is correct, but it's invariably transposed to local time (i.e.
// +10) with Local Kind, regardless of the stored time zone. Calling
// .ToUniversal() yields the correct DateTime in UTC.
// DateTime's Kind property is ignored. To work around, set database or session
// time zone to UTC and always call ToUniversal() on DateTime parameters.
// Don't use 'timestamp without time zone' in your schema.
}privatevoidDateTimesEqual(stringexpectedUtc,stringquery,paramsDateTime[]dateTimes){foreach(vardateTimeindateTimes){varcursor=_connection.Query<DateTime>(query,new{DateTime=dateTime});DatesEqual(expectedUtc,cursor.Single());}}privatevoidQueryEqual(stringexpectedUtc,stringquery){DatesEqual(expectedUtc,_connection.Query<DateTime>(query).Single());}privatestaticvoidDatesEqual(stringexpectedUtc,DateTimedate){varcode="_";switch(date.Kind){caseDateTimeKind.Local:code="L";break;caseDateTimeKind.Unspecified:code="?";break;caseDateTimeKind.Utc:code="U";break;}varuni=date.ToUniversalTime();Assert.AreEqual(expectedUtc,string.Format("{0} ({1}) {2}",uni.ToString(CultureInfo.InvariantCulture),date.ToString(CultureInfo.InvariantCulture),code));}