Scaffold-DbContext generates DateTime instead of DateTimeOffset for PostgreSQL database

[jgador]

Scaffold-DbContext for PostgreSQL with data type timestamp with timezone generates DateTime instead of DateTimeOffset.

Scaffold-DbContext "Host=localhost;Port=5432;Database=<db>;User ID=<user id>;Password=<password>;" Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir Entities.Psql.Generated -Namespace Data.Entities.Psql -ContextDir Contexts.Psql.Generated -DataAnnotations -Context PsqlContext -ContextNamespace Data.Contexts.Psql -NoPluralize -NoOnConfiguring -Force

Provider and version information

EF Core version: 6.0.5
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 6.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.4.4

[ajcvickers]

/cc @roji. ;-)

[jgador]

If this is a simple change, I'd love to start my contribution to Npgsql with this small task.

[roji]

@jgador this is an Npgsql-specific issue, and so belongs in https://github.com/npgsql/efcore.pg rather than here.

In any case, this is by design. The PostgreSQL timestamp with time zone type does not, in fact, store a timezone offset in the database - it simply means that the timestamp value is expected to be UTC. In that sense, .NET DateTime (with Kind=Utc) is a closer match than DateTimeOffset is, and that's why it's mapped that way. Npgsql does allow you to have .NET DateTimeOffset properties - mapping them to timestamp with time zone - but only DateTimeOffsets with a zero offset are supported. The documentation should provide more details on all this.

I'm going to go ahead and close this, but if you'd like to continue the conversation feel free to post back here and I'll respond.