Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Npgsql refuses a local DateTime for a timestamp with time zone column

ErrorCannot 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.

  • Applies to Npgsql 8, EF Core 8, PostgreSQL 16, .NET 8
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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 .
  2. 2Match what you see, then jump to its fix

    1. 1The exception names Kind=Local (or Unspecified) and 'timestamp with time zone'A Local or Unspecified DateTime goes into a timestamptz columnFix
    2. 2Values 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. 3The same code worked on Npgsql 5 and broke after an upgradeOld code depends on the pre-6.0 timestamp behaviourFix

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 run

Confirmed by npgsql.org

Did this fix it?

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 0

3Verify

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

Did this fix it?

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 run

Confirmed by npgsql.org

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.