Skip to content

DateTime(Kind=UTC) being converted to local when inserting to TIMESTAMP WITHOUT TIME ZONE #1803

@bharrison44

Description

@bharrison44

As per the documentation, a DateTime(Kind=UTC) should be sent as-is. I am seeing the opposite behaviour with Npgsql 3.2.6 and PostgreSQL 10.1.

With my Postgres servers default timezone of 'Australia/Brisbane', DateTime values with a kind of UTC are being converted to local time.

My test table:

CREATE TABLE public.ts_test (ts_type text NOT NULL, ts timestamp without time zone NOT NULL)

My insert code:

using (var command = connection.CreateCommand())
{
    command.CommandText = "INSERT INTO ts_test (ts_type, ts) VALUES (@type1, @ts1),  (@type2, @ts2), (@type3, @ts3)";
    command.Parameters.AddWithValue("type1", "UTC");
    command.Parameters.AddWithValue("ts1", new DateTime(2020, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc));
    command.Parameters.AddWithValue("type2", "Unspecified");
    command.Parameters.AddWithValue("ts2", new DateTime(2020, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
    command.Parameters.AddWithValue("type3", "Local");
    command.Parameters.AddWithValue("ts3", new DateTime(2020, 1, 1, 0, 0, 0, 0, DateTimeKind.Local));
    command.ExecuteNonQuery();
}

The results of this is:

SELECT * FROM ts_test;
   ts_type   |         ts
-------------+---------------------
 UTC         | 2020-01-01 10:00:00
 Unspecified | 2020-01-01 00:00:00
 Local       | 2020-01-01 00:00:00
(3 rows)

If however I run SET TIME ZONE 'utc'; before insert all DateTimes are inserted correctly.

SELECT * FROM ts_test;
   ts_type   |         ts
-------------+---------------------
 UTC         | 2020-01-01 00:00:00
 Unspecified | 2020-01-01 00:00:00
 Local       | 2020-01-01 00:00:00
(3 rows)

Setting the connection time zone to any other value also behaves consistently ie SET TIME ZONE '+4'; before insert results in:

SELECT * FROM ts_test;
   ts_type   |         ts
-------------+---------------------
 UTC         | 2020-01-01 04:00:00
 Unspecified | 2020-01-01 00:00:00
 Local       | 2020-01-01 00:00:00
(3 rows)

Specifying a NpgsqlDbType as Timestamp is also a way I found that results in correct behaviour, however if I am interpreting the documentation correctly the default value TimestampTZ should not perform any conversion, either.

command.Parameters.AddWithValue("ChangeTime1", NpgsqlDbType.Timestamp, new DateTime(2020, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc));

The work around of setting the connections time zone before inserts will work for us, but it would be great to get some clarification on the intended behaviour and whether that is what is being observed here or what I believe is being described by the documentation.

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions