How to read Postgres TIMESTAMPTZ as .NET Core DateTimeOffset

Question

I can't find a way how to read a postgres timestamptz as a .NET DateTimeOffset value. According to the documentation it seems to be possible. Could someone describe how to handle this issue.

I'm using: Postgres: 11.x Npgsql: 4.1.0

The code snippet is extremely simple:

using (var connection = new NpgsqlConnection(connectionString))
{
    connection.Open();
    using (var command = new NpgsqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = "SELECT '2004-10-19 10:23:54+02'::timestamptz";
        var o = (DateTimeOffset)command.ExecuteScalar();
        Console.WriteLine(o);
    }
}

It throws an invalid cast exception from DateTime to DateTimeOffset:

Unhandled Exception: System.InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.DateTimeOffset'

Answer 1

By default a DateTime value is read by command.ExecuteScalar(). There's the reader method GetFieldValue<T>() to get a DateTimeOffset value. That's not suitable for me at the moment but a possible workaround.

More details: https://github.com/npgsql/npgsql/issues/2641