npgsql: "Cannot write DateTime with Kind=Unspecified" is confusing
Using 6.0.2, we receive an error:
Cannot write DateTime with Kind=Unspecified to PostgreSQL type ‘timestamp with time zone’, only UTC is supported. Note that it’s not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
The table has solely columns of type timestamp without time zone. From other discussions I understand that for storing UTC, the type should be timestamp with time zone.
We will make sure the delivery of the data is with kind changed from Unspecified to UTC for the future.
However, I find the error message confusing since the types do not align between message and actual column types.
Suggestion is to finetune the error message, since it will probably be essential for a fast adaption of the Npgsql 6 driver.
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 29 (12 by maintainers)
@monty241 the error message does not indicate that your PostgreSQL column type is wrong - Npgsql has no knowledge of the column type you’re inserting into, etc. It says that you’ve asked Npgsql to send an Unspecified DateTime as a PG timestamptz type, e.g. by setting NpgsqlDbType.TimestampTz on your parameter; so the mismatch isn’t between the value and the column, but rather between the value and your parameter type.
Can you provide the actual C# code triggering this? Is it because you’re mixing DateTimes with different kinds in an array and trying to send that?
@fernandocristan I don’t have details or a code sample from you, but it’s very likely that you’re writing a DateTime to the database, which Npgsql sends as
timestampto PostgreSQL, not asdate; the type of the column in the database doesn’t matter here - Npgsql has no idea about your column type when sending the parameter (PG converts thetimestampthat Npgsql sends todatewhen writing to the column).You need to tell Npgsql to send a
date. The recommended way is to switch to the DateOnly type introduced in .NET 6.0. Otherwise you can explicitly set NpgsqlDbType on the parameter to NpgsqlDbType.Date (see Dapper custom parameter types).I am interested in avoiding it 😉
Thanks for your answer. Helpful!
This change is so frustrating.
When using
DateTimeOffset.UtcNow+timestamp with time zone+ specifying the parameter asNpgsqlDbType.TimestampTzIt still throws
The error makes no sense. I’m not using
without time zoneI’m usingwith time zoneThis is the first time I’ve hit a real roadblock with npgsql and this change seems bonkers to me.