What is the recommended way to store DateTimeOffsets to 'timestamp with time zone' fields and to avoid the exception: Cannot write DateTimeOffset with Offset=01:00:00 to PostgreSQL type 'timestamp with time zone'

Steps to reproduce

Just try to write any DateTimeOffset to a Postgres database using Dapper.

The issue

The DateTimeOffset value is not stored and the mapper throws an exception:

Exception message: Cannot write DateTimeOffset with Offset=01:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
Stack trace: System.InvalidCastException: Cannot write DateTimeOffset with Offset=01:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTimeOffset value, NpgsqlParameter parameter)
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 654

Further technical details

Npgsql version: 6.0.0 PostgreSQL version: 13.0 Operating system: Windows 10 / Debian on Docker Dapper version: 2.0.123

Other details about my project setup: Nothing relevant, projects are running in the GMT+1 (In summer time GMT+2) and a German environment.

What is the preferred way to fix this issue? The only two things I can think of is:

Set the Npgsql.EnableLegacyTimestampBehavior AppContext switch (Which will probably soon be removed, so updates are not possible anymore for me)
Change all DateTimeOffsets manually to UTC and write them to the database (Not possible at all as this would take ages to check)
Some kind of global “mapper” to change all DateTimeOffsets to UTC (No idea how this would work)

While I totally agree to @roji (https://www.roji.org/postgresql-dotnet-timestamp-mapping) that the naming of timestamptz in PostgreSQL is just bullshit to be honest, it would be nice to have a more generic solution (As I said, it’s possible to upgrade all my database calls, but I don’t want to do that as it takes ages…). Maybe, I’m wrong here and the Dapper people do have a solution I didn’t see yet.

Issue Analytics

State:
Created 2 years ago
Reactions:1
Comments:5 (4 by maintainers)

Top GitHub Comments

6reactions

ghostcommented, Mar 10, 2022

For everyone running into the same questions. We “solved” it with EF core 6.0 by using a value converter for DateTimeOffset.

Bulk-configuring a value converter was recently introduced to EF core which we are taking advantage of. https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#bulk-configuring-a-value-converter

public class DateTimeOffsetConverter : ValueConverter<DateTimeOffset, DateTimeOffset>
{
    public DateTimeOffsetConverter()
        : base(
            d => d.ToUniversalTime(),
            d => d.ToUniversalTime())
    {
    }
}

Overwrite in your DbContext

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder
        .Properties<DateTimeOffset>()
        .HaveConversion<DateTimeOffsetConverter>();
}

0reactions

SeppPennercommented, Mar 10, 2022

For everyone running into the same questions. We “solved” it with EF core 6.0 by using a value converter for DateTimeOffset.

Bulk-configuring a value converter was recently introduced to EF core which we are taking advantage of. https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#bulk-configuring-a-value-converter
public class DateTimeOffsetConverter : ValueConverter<DateTimeOffset, DateTimeOffset>
{
    public DateTimeOffsetConverter()
        : base(
            d => d.ToUniversalTime(),
            d => d.ToUniversalTime())
    {
    }
}
Overwrite in your DbContext
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder
        .Properties<DateTimeOffset>()
        .HaveConversion<DateTimeOffsetConverter>();
}

Nice. Sadly, I use Dapper and still didn’t have time to find the issue…