NpgSql EntityFramework Core not persisting DateTimeOffset as UTC time in database

Question

I'm using Npgsql.EntityFrameworkCore.PostgreSQL 7.0.4 in my console app to persist DateTimeOffset to PostgreSQL Database. However the values stored in my database are always in Local DateTimeOffset. My local machine is configured with IST time zone which is UTC +05.30.

Here is my code,

DbContext:

public class TestContext : DbContext
{
    public DbSet<Category> Categories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Database=EFDateTime;User id=postgres;Password=xxxx;")
                       .EnableSensitiveDataLogging()
                       .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);
    }
}

Category:

public class Category
{
    public int Id { get; set; }
    public DateTimeOffset DateTimeProperty { get; set; } = DateTimeOffset.UtcNow;
    public DateTimeOffset DateTimeOffsetProperty { get; set; } = DateTimeOffset.UtcNow;
}

I'm creating new DbContext and new Category and saving to database.

var context = new TestContext();
var category = new Category();
context.Categories.Add(category);
context.SaveChanges();

Now when I check the database, I can see Local DateTimeOffset with +05.30 instead of UTC time with +00.00

Here is the query generated by EF Core,

info: 07-08-2023 16:32:19.239 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (36ms) [Parameters=[@p0='2023-08-07T11:02:16.4694593+00:00' (DbType = DateTime), @p1='2023-08-07T11:02:16.4694577+00:00' (DbType = DateTime)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Categories" ("DateTimeOffsetProperty", "DateTimeProperty")
      VALUES (@p0, @p1)
      RETURNING "Id";

Here is the screen shot,

If we see the highlighted values in above image, the values generated in the query is in UTC as expected but in database it is in IST.

Value	DateTimeProperty	DateTimeOffsetProperty	Kind
Query Generated	2023-08-07T11:02:16.4694577+00:00	2023-08-07T11:02:16.4694593+00:00	UTC
Database Persisted	2023-08-07 16:32:16.469457+05:30	2023-08-07 16:32:16.469459+05:30	Local

Script Generated by EF Core command Script-Migration:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

START TRANSACTION;

CREATE TABLE "Categories" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "DateTimeProperty" timestamp without time zone NOT NULL,
    "DateTimeOffsetProperty" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Categories" PRIMARY KEY ("Id")
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230806181121_Initial', '7.0.9');

COMMIT;

START TRANSACTION;

ALTER TABLE "Categories" ALTER COLUMN "DateTimeProperty" TYPE timestamp with time zone;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20230806181852_AddOffset', '7.0.9');

COMMIT;

Please can someone help me understand what I'm doing wrong? I would like to learn and understand this.

Answer 1

However the values stored in my database are always in Local DateTimeOffset

PostgreSQL does not store any timezone along with your timestamp, even though the type is called timestamp with time zone. That type simply holds UTC timestamps (by convention). The +05.30 time zone is being added for display purposes only when you select the value via a UI, based on your seesion's TimeZone parameter. You can change the TimeZone to be something else, at which point your UI will show the values in a different time zone; but the value stored in the database is always a UTC timestamp (for the timestamp with time zone type).

Note that TimeZone doesn't matter when you're using Npgsql to read the value back - it will always be read back as a UTC timestamp (e.g. DateTimeOffset with Offset=0). TimeZone only affects conversions of the timestamp to text, e.g. for display purposes.

It's recommend to give this Npgsql doc page a good read to better understand the mapping between .NET date/time types and PostgreSQL types, and also this PG doc page for more general information.