"Cannot write DateTime with Kind=Unspecified" is confusing

[monty241]

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.

[monty241]

Suggestion is to split the possible causes.

Original message:

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.

When there are different DateTimes in an array: report exactly that:

An array may contain only values with all identical values for Kind in DateTime. Please make sure the kind of the columns {LIST} is all the same, instead of {VARIANT1}, {VARIANT2}.

When one or more column values do not align with 'timestamp with time zone':

All column values to be loaded into columns of type 'timestamp with time zone' must all have a value of either Local or Utc for Kind. The columns {LIST} have a DateTime with Kind Unspecified. Please make the kind explicit for the values of these columns.

When one or more column values do not align with 'timestamp without time zone':

?

[roji]

@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?

[monty241]

Thanks for the explanation. The code had already been altered to hard set kind to UTC where applicable and causing problems.

One of the root causes was probably that the mapping .net data type <-> database data type was often assumed to bidirectional, with exotic types after one mapping back and forth nicely fitting in the supported range of types that our SQL engine supports. The mapping is (similar to PostgreSQL) done without the actual data being studied beforehand (when any).

Specifically for PostgreSQL we have altered the mapping to differentiate DateTime.

We are still having issues mixing DateTimes of different Kinds in an array/range, but that type of use will be considered unsupported. It is unclear whether such tables with multiple types of timestamp really exist in the wild outside our laboratory test cases.

I find the error messages in isolation still confusing in grasping the actual problem and recommended approach (multiple causes bundled as one message). No problem when it is a will-not-be-fixed; was just to make sure that the information was provided.

[roji]

I get your point about the array error; the way things are implemented internally make it a bit hard to create a tailored exception message for this case. However, the InvalidCastException is reported as an inner exception of a very array-specific message:

Unhandled exception. System.Exception: While trying to write an array, one of its elements failed validation. You may be trying to mix types in a non-generic IList, or to write a jagged array.
 ---> System.InvalidCastException: Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. 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.

I may tweak the first array-specific message to make it a bit more appropriate, but otherwise I hope it provides enough context to understand what's going on (with the "mixing").

We are still having issues mixing DateTimes of different Kinds in an array/range, but that type of use will be considered unsupported. It is unclear whether such tables with multiple types of timestamp really exist in the wild outside our laboratory test cases.

The question is what that would mean in PostgreSQL: an array is either of timestamptz (in which case it's supposed to an array of UTC timestamps), or an array of timestamp (in which case it's an array of unspecified timestamps). I get that some existing code may mix DateTimes with different Kinds in the same array (since it worked up until 6.0), but that's exactly the kind of problematic practice that 6.0 tries to prevent.

[monty241]

Thanks for the elaboration. I understand the InnerException can be used to ease detecting the actual cause.

[hazzik]

This is the culprit in our case:

No one has asked Npgsql to set parameter as TimestampTZ.

The parameter was initialized like this:

var p = command.CreateParameter();
p.DbType = DbType.DateTime;

And this is highly unexpected.

This is the single parameter. No arrays. Column is 'timestamp without time zone'.

[roji]

In Npgsql 6.0, DbType.DateTime refers to PostgreSQL timestamptz, whereas DbType.DateTime2 refers to timestamp. DbType purports to be a database-agnostic type specification system, but that's necessarily limited. Note that DbType.DateTime and DateTime2 only exist since SQL Server happens to have two timestamp types (datetime is old and discouraged, datetime2 is newer and encouraged).

In PG, DbType.DateTime must point to either timestamptz or timestamp; since storing UTC timestamps is a very common case, we made it point to timestamptz. We took advantage of the fact that DateTime2 also exists to make it point to timestamp.

I see that our docs on this are out of date - I've updated them.

[monty241]

I don't understand the logic of mapping DateTime2 to timestamp without time zone. Both datetime and datetime2 on SQL Server have the same semantics; they just differ in internal storage format and no semantical differences I think, with datetime2 being the clear winner in terms of resolution, allowable values and precision.

Besides piggy-backing on a different naming, would it not be more safe for SQL Server tables being migrated to introduce no differences in the mapping?

[roji]

We discussed this at length when considering the changes for 6.0, and compatibility with SQL Server was indeed considered.

At the end of the day, the idea of achieving database compatibility via DbType is very limited: types simply aren't the same across databases. Specifically, the type distinction between UTC and non-UTC timestamps exists in PostgreSQL, but it does not exist in SQL Server (where datetime2 vs. datetime is simply a matter of a bad old type vs. a good new type). There's simply no way to just have some DbType enum which does what you want across all databases - you have to carefully consider types per-database and make decisions.

Besides piggy-backing on a different naming, would it not be more safe for SQL Server tables being migrated to introduce no differences in the mapping?

That would depend on the data being migrated, i.e. whether it's UTC or non-UTC. But SQL Server isn't the only database out there, and given UTC timestamps are more common in the general case, we decided to have DbType.DateTime map to them.

[monty241]

Thanks for the background!

[roji]

I think there's nothing actionable left here, so I'll go ahead and close this. But don't hesitate to post back if you have more questions or thoughts.

[rsr-maersk]

@roji any thought here? When I have db column as timestamp without time zone, and the Datetime unspecified

• Save with Dapper, a No good get the same exception
• Save with EF core, Works

Any ideas what we need to do to get Dapper to save a parameter that is Datetime and column that is timestamp without time zone?

[roji]

Dapper should work - which version are you using? If not the latest, try upgrading to the latest as an issue was fixed in this area. If you still have trouble, please open a new with a runnable code sample.

[rsr-maersk]

@roji version = packageReference Include="dapper" Version="1.50.5"

It was inherited from SQL kata... But 2 is prob best to updgrade

[roji]

Uh, yeah, that version's from 2018...

[rsr-maersk]

@roji updated to latest dapper. Still didn't work. I will try to make a testable sln. But for now i will insert with ef.. Thanks

[dkuziems]

@roji updated to latest dapper. Still didn't work. I will try to make a testable sln. But for now i will insert with ef.. Thanks

I had same problem which was resolved by upgrading Dapper and related packages to latest versions (for example Dapper "2.0.90"-> "2.0.123"), worked with:

    <PackageReference Include="Dapper" Version="2.0.123" />
    <PackageReference Include="Dapper.Contrib" Version="2.0.78" />
    <PackageReference Include="Npgsql" Version="6.0.3" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.3" />

Good luck

[phillip-haydon]

This change is so frustrating.

When using DateTimeOffset.UtcNow + timestamp with time zone + specifying the parameter as NpgsqlDbType.TimestampTz

It still throws

System.InvalidCastException: Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. 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 error makes no sense. I'm not using without time zone I'm using with time zone

This is the first time I've hit a real roadblock with npgsql and this change seems bonkers to me.

[roji]

@phillip-haydon please open a new issue with a runnable code sample - this shouldn't happen.

[TajievOlimjon]

у меня тоже проблема с типом datetime

[TajievOlimjon]

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.

[roji]

@TajievOlimjon please read this blog post and the 6.0 release notes to understand this. If you're still having trouble after that, please open a new issue with a runnable code sample that shows what you're trying to do.

[fernandocristan]

Hello, I've been having a problem with this too.

The column type in pg is Date, in dotnet it is DateTime and I have received the following error: 'Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported'.

The Kind of the property is unspecified, but even if the pg column is Date I need to change it to UTC?

I'm using dapper with the Dommel package.

Thanks

[roji]

@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 timestamp to PostgreSQL, not as date; 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 the timestamp that Npgsql sends to date when 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).

[marcwittke]

In PG, DbType.DateTime must point to either timestamptz or timestamp; since storing UTC timestamps is a very common case, we made it point to timestamptz.

Is there a way to change this globally to timestamp in EFCore?

[roji]

@marcwittke no, but there's DbType.DateTime2 which points to timestamp without time zone. You may also be interested in the Npgsql.EnableLegacyTimestampBehavior compat flag, though I wouldn't recommend it (see the date/time docs and the 6.0 release notes).

[marcwittke]

You may also be interested in the Npgsql.EnableLegacyTimestampBehavior compat flag

I am interested in avoiding it :wink:

Thanks for your answer. Helpful!

[KateYatsiuk]

@roji, the DateOnly type really helped! Thanks a lot!

[roji]

Great!