Need in-depth understanding of NpgSQL DateTimeOffset processing

Question

Since version 6.0 Npgsql throws an exception if trying to save a DateTimeOffset field with non-zero offset.

For instance:

public class User
{
  public Guid Id { get; private set; }
  public DateTimeOffset? LockedUntil { get; private set; }
  public void Lock()
  {
    LockedUntil = DateTimeOffset.Now.Add(TimeSpan.FromDays(1));
  }
}
// ...
builder.Property(u => u.Id);
builder.Property(u => u.Login);
// ...
var user = dbContext.Users.First(u => u.Id == someId);
user.Lock();
dbContext.SaveChanges();

This causes an exception: System.ArgumentException: ... only offset 0 (UTC) is supported I was curious why? I mean I do know that this is documented behaviour. Meanwhile, it seems like Postgres works perfectly fine with time zones when the type is timestamptz.

SELECT '2024-04-10T15:00:00.000Z'::timestamptz = '2024-04-10T13:00:00.000-02:00'::timestamptz;
--> true

create table test_timestamptz(some_field timestamptz);
INSERT INTO test_timestamptz(some_field)
VALUES ('2024-04-10T15:00:00.000Z'), ('2024-04-10T13:00:00.000-02:00'), ('2024-04-10T20:00:00.000+05:00');

SELECT COUNT(*) FROM test_timestamptz WHERE some_field = '2024-04-10T15:00:00.000Z';
--> 3
SELECT COUNT(*) FROM test_timestamptz WHERE some_field = '2024-04-10T16:00:00.000+01:00';
--> 3

In my project I would like to be able to work with time values having different time zones (especially compare them). DateTimeOffset gives me that and hides TZ math. After all I ended up with this sort of converter:

public class DateTimeOffsetToDateTimeConverter : ValueConverter<DateTimeOffset, DateTime>
{
  public DateTimeOffsetToDateTimeConverter() : base(
    v => v.UtcDateTime,
    v => new DateTimeOffset(v))
  {
  }
}

Could it be some sort of tech restriction? Or have I misunderstood something (C# isn't my main PL)

Answer 1

This is covered in the Npgsql docs:

A common mistake is for users to think that the PostgreSQL timestamp with time zone type stores the timezone in the database. This is not the case: only a UTC timestamp is stored. There is no single PostgreSQL type that stores both a date/time and a timezone, similar to .NET DateTimeOffset. To store a timezone in the database, add a separate text column containing the timezone ID.

And from the PostgreSQL docs:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).

And

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.

Which can be observed with:

select *
from test_timestamptz;

Which gives for me:

some_field
2024-04-10 15:00:00.000000 +00:00
2024-04-10 15:00:00.000000 +00:00
2024-04-10 15:00:00.000000 +00:00

So the behavior incorporated by Npgsql since 6.0 is designed to avoid the potential confusion.

If the pre-6.0 behaviour is desired there is no need for custom converter - you can enable the legacy driver behavior with the switch:

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);