Map Utc DateTime in domain to DateTime without timezone in Postgres

July 30, 2023

I have a domain class like this:

public class MyClass
{
    public DateTime CreatedUtc { get; set; }
    ...
}

When I create a migration for a Postgres db it produces this:

ALTER TABLE "MyClasses" ADD "CreatedUtc" timestamp with time zone NOT NULL DEFAULT TIMESTAMPTZ '-infinity';

But it is a UTC date, so I want to store it without a time zone. How do I do that?

Using version 6.0.8 of Npgsql.EntityFrameworkCore.Postgres. The domain is in a .NET Standard 2.0.3 library.

>Solution :

timestamp with time zone is the right DB column type to store UTC timestamps. Presence of timezone means that timestamp has got UTC offset in it (offset will be always zero for UTC) and such timestamps will be loaded from DB with correct kind DateTimeKind.Utc.

If column is declared as timestamp without timezone then values will be loaded with DateTimeKind.Unknown, without offset there is no way to distinct UTC from non-UTC value.

Details from Npgsql: https://www.npgsql.org/doc/types/datetime.html