Npgsql refuses a local DateTime for a timestamp with time zone column
Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone'Short answer
Since Npgsql 6, a DateTime is written as timestamptz only when its Kind is Utc. A Local or Unspecified value sent to that type throws. Convert the value to UTC before saving, use DateTimeOffset with offset 0, or map the column to timestamp without time zone.
Fix it fast
1Find out which cause you have
Find where the value is created. DateTime.Now has Kind Local, and a date parsed or read from a form often has Kind Unspecified. Search the code for them.
grep -rn "DateTime.Now\|DateTime.Today\|DateTimeKind" --include=*.cs .2Match what you see, then jump to its fix
- 1
The exception names Kind=Local (or Unspecified) and 'timestamp with time zone'A Local or Unspecified DateTime goes into a timestamptz columnFix - 2
Values come from users in different time zones, and you converted them by hand in many placesYou want to keep an offset instead of a bare DateTimeFix - 3
The same code worked on Npgsql 5 and broke after an upgradeOld code depends on the pre-6.0 timestamp behaviourFix
- 1
What does the error look like?
Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone'A save that worked before the Npgsql 6 upgrade now fails on a DateTime property
Cause 1 of 3
A Local or Unspecified DateTime goes into a timestamptz column
1Check
The Npgsql docs state that, from 6.0, a UTC DateTime is written as timestamptz, a Local or Unspecified one as timestamp without time zone, and sending a non-UTC DateTime as timestamptz throws.
dotnet ef migrations script | grep -i "timestamp"2Fix
Use UTC at the source: DateTime.UtcNow, or convert a received value with ToUniversalTime() before you save it. For an Unspecified value you know is UTC, set the Kind explicitly.
entity.CreatedAt = DateTime.UtcNow;
entity.StartsAt = DateTime.SpecifyKind(parsed, DateTimeKind.Utc);3Verify
SaveChanges completes with no exception.
dotnet runConfirmed by npgsql.org
Cause 2 of 3
You want to keep an offset instead of a bare DateTime
1Check
The docs say Npgsql supports DateTimeOffset for timestamptz, but only with an offset of 0.
grep -rn "DateTimeOffset" --include=*.cs .2Fix
Type the property as DateTimeOffset and convert incoming values to UTC offset before writing. If you must keep the original zone, store its ID in a separate text column, because PostgreSQL does not keep it in timestamptz.
entity.StartsAt = incoming.ToUniversalTime(); // DateTimeOffset with offset 03Verify
The save works, and the stored value reads back as the same instant.
SELECT starts_at FROM events ORDER BY id DESC LIMIT 1;Confirmed by npgsql.org
Cause 3 of 3
Old code depends on the pre-6.0 timestamp behaviour
1Check
The 6.0 release notes say writing Local or Unspecified DateTime as timestamptz is no longer possible and that a legacy switch exists. Npgsql keeps a legacy switch, Npgsql.EnableLegacyTimestampBehavior, that restores the pre-6.0 handling.
grep -rn "EnableLegacyTimestampBehavior" --include=*.cs .2Fix
Use the switch only as a temporary bridge. Set it at the very start of the program, before any Npgsql call, and plan the move to UTC values.
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);3Verify
The old code path saves again; schedule the real fix from the first cause.
dotnet runConfirmed by npgsql.org
How do I stop it coming back?
- Use DateTime.UtcNow, never DateTime.Now, for values that go to the database.
- Convert user input to UTC at the edge of the application.
- Use DateTimeOffset or the NodaTime plugin if time zones matter to the business logic.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
Related fixes
Databases
Npgsql throws 'Exception while reading from stream' against PostgreSQL
System.IO.EndOfStreamException: Attempted to read past the end of the stream
This exception means the PostgreSQL connection was closed under Npgsql while it waited for data.
3 causes, each with a fixDatabases
Dapper cannot find a constructor that fits the query columns
The error appears when Dapper maps rows into a record or a class whose only constructor takes arguments
Dapper looks for a public parameterless constructor first.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fix