kivikakk.ee

[SetUp]
public void SetUp()
{
    _connection = new NpgsqlConnection(
        "Host=localhost; Port=5432; Database=time_zones_in_dot_net; " +
        "User ID=time_zones_in_dot_net");
    _connection.Open();
}

[TearDown]
public void TearDown()
{
    _connection.Close();
}

[Test]
public void TimeZonesSane()
{
    // 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",
                   new DateTime(2013, 9, 11, 6, 47, 3, DateTimeKind.Local),
                   new DateTime(2013, 9, 11, 6, 47, 3, DateTimeKind.Unspecified),
                   new DateTime(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",
                   new DateTime(2013, 9, 11, 6, 47, 3, DateTimeKind.Local),
                   new DateTime(2013, 9, 11, 6, 47, 3, DateTimeKind.Unspecified),
                   new DateTime(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",
                   new DateTime(2013, 9, 11, 6, 47, 3, DateTimeKind.Local),
                   new DateTime(2013, 9, 11, 6, 47, 3, DateTimeKind.Unspecified),
                   new DateTime(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.
}

private void DateTimesEqual(string expectedUtc, string query,
                            params DateTime[] dateTimes)
{
    foreach (var dateTime in dateTimes) {
        var cursor = _connection.Query<DateTime>(query, new {DateTime = dateTime});
        DatesEqual(expectedUtc, cursor.Single());
    }
}

private void QueryEqual(string expectedUtc, string query)
{
    DatesEqual(expectedUtc, _connection.Query<DateTime>(query).Single());
}

private static void DatesEqual(string expectedUtc, DateTime date)
{
    var code = "_";
    switch (date.Kind) {
        case DateTimeKind.Local:
            code = "L";
            break;
        case DateTimeKind.Unspecified:
            code = "?";
            break;
        case DateTimeKind.Utc:
            code = "U";
            break;
    }

    var uni = date.ToUniversalTime();
    Assert.AreEqual(expectedUtc,
                    string.Format("{0} ({1}) {2}",
                                  uni.ToString(CultureInfo.InvariantCulture),
                                  date.ToString(CultureInfo.InvariantCulture),
                                  code));
}