.NET 6 RC1 and issues with datetime fields

[viniciusverasdossantos]

System.InvalidCastException: '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.'

I have updated the packages to .NET 6 RC1 and am having issues with datetime fields.

<PackageReference Include="Npgsql" Version="6.0.0-rc.1-ci.20210923T063756" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="6.0.0-rc.1-ci.20210923T214126" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0-rc.1.21452.10" />

[roji]

@viniciusverasdossantos these are intentional breaking changes which are in the process of being documented: see npgsql/doc#116 for the details.

The changes here are quite big, and we want to make it as easy as possible for users to transition - it would be good to have any feedback after reading the release and breaking changes notes.

[roji]

@viniciusverasdossantos the release notes page is now online here.

[Taifunov]

@roji where we can see examples how to migrate from old version to new ?

[roji]

@Taifunov have you taken a look at the release notes? I plan to write more docs (and a blog post explaining the context), but hopefully these notes should be enough to start - please let me know how it goes!

[roji]

Am closing this issue, but anyone needing assistance is still welcome to post questions here (after reading the release notes).

[3ldar]

@roji I really appreciate the great work. But I'm failing to understand choosing timestamptz over timestamp for the .net data type DateTime. Most developers (lazy ones like me) stick with the DateTime instead of DateTimeOffset and deal with the time zone where you serialize the data (talking about asp.net). With the default option without explicitly specifying a column type, every time you query or update you need to specfiy a Kind for DateTime. And when you migrate the previously set min value to the timestamptz it will become something like this : 0001-12-31 23:06:32.000000 +00:00 BC which will fail to convert back to DateTime. Regards.

[roji]

@3ldar I understand and I agree that this isn't an ideal situation; FWIW I thought about this a lot and discussed with the other people working on Npgsql. Here's some context (I need to sit down and write a post about this):

• In PostgreSQL, UTC timestamps are represented by timestamptz (it's the standard, various functions interact with them in that way etc.).
• In .NET, UTC timestamps are represented by DateTime with Kind=Utc. One of the original sins of DateTime design is to represent UTC and non-UTC timestamps with the same CLR type; this forces us to make a choice in the EF Core provider, to map DateTime either to timestamp or to timestamptz (FWIW NodaTime does things right with different types). Since UTC timestamps are by far the more common/recommended thing to store, we're switching to mapping to timestamptz.
• One of the great sources of confusion and errors is mixing UTC and non-UTC timestamps, and various timezone conversions that happen (sometimes implicitly!) when doing that. As a result, the provider strictly disallows mixing, forcing you to use UTC DateTime in timestamptz contexts and non-UTC DateTime in timestamp contexts. This protects users against various pits of failure and brings lots of clarity and sanity, but does cause pain for people used to just using Unspecified DateTime because that's the default.

To summarize, yeah, assuming you're dealing with UTC timestamps in your code, you must make that explicit by making sure your DateTimes actually have Kind=Utc. My hope is that despite an initial painful upgrade, users end up with a much saner, safer timestamp mapping system. And there's a compatibility flag to allow you to upgrade to 6.0 without going through this change (or deferring it for later).

I'd be very interested to hear any other feedback on this!

Specifically about migrating existing data:

And when you migrate the previously set min value to the timestamptz it will become something like this : 0001-12-31 23:06:32.000000 +00:00 BC which will fail to convert back to DateTime.

Can you provide more info on what you mean here? I wrote some migration notes from timestamp to timestamptz, have you tried those?

[3ldar]

@roji I missed below part of the notes :

This means that your new timestamptz column now contains 10:00 UTC, which is probably not what you want: if the original values were in fact UTC values, you need them to be preserved as-is, with only the column type changing. To do this, edit your migration and add the following to the top of your migration's Up and Down methods:
migrationBuilder.Sql("SET TimeZone='UTC'");

It was just mentioned in the notes. The previous value was 0001-01-01T00:00:00.0000000 before the migration so it assumed a positive time zone while migrating so it went backward in the time: Now we have a date that has a negative value (I mean BC) which .net DateTime doesn't support.

I should have read that more carefully before migrating blindly. And thanks for your kind reply.

[roji]

@3ldar sure thing. You should be able to correct the data by executing the down migration (migrating back from timestamptz to timestamp, but ensuring you have the same TimeZone as you had when doing the up migration!). At that point you can edit the migration to add SET TimeZone='UTC' and re-execute the up migration.

[amitdixit]

@roji appreciate the effort which you have being givine and I am a fan of your work. Having said that this breaking change is something needs more elaboration and explanation. I guess the current "Npgsql.EntityFrameworkCore.PostgreSQL" Version="6.0.0" . Has some kind of bug. After going through the documentation I found that only Utc seems to be working.. i.e. DateTime.UtcNow in only working. DateTime.Now does not work. I made the Columns as "timestamp", "timestamptz" and all other combinations but I only get InvalidCastException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Now this has colapsed my whole application; since we are planning to upgrade it to from .Net 5 to .net 6.
Since my application heavily rely on local time and we expect only local users to be using it. Making it Utc is adding an extra effort to re-convert it to Local Timezone.
And I have to go with AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

[roji]

@amitdixit you should be able to write DateTime.Now to timestamp without time zone columns - including without the legacy compatibility flag. If this doesn't work for you, can you please open a new issue with a runnable code sample that shows the problem?

[VILLAN3LL3]

I'm getting the same error when loading entities from the database although I added AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true); to the ctor of my dbcontext...

[3ldar]

I'm getting the same error when loading entities from the database although I added AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true); to the ctor of my dbcontext...

DbContext constructor may not be the best place to set that switch. You should set this switch at the start-up config your application preferably before you configure services. Also, make sure that you don't have any timestamptz columns in your DB.

[roji]

@VILLAN3LL3 can you please open a new issue with a runnable code sample that shows the problem?

[VILLAN3LL3]

@VILLAN3LL3 can you please open a new issue with a runnable code sample that shows the problem?

#2140

[hhyyrylainen]

I'm not sure what's the proper place to put this, but I had one slight issue with the suggested migrationBuilder.Sql("SET TimeZone='UTC'"); in the documentation because I use SQL scripts to migrate in my staging/production environment. I got an SQL syntax error, which was easy enough to fix by adding one ; (like this: migrationBuilder.Sql("SET TimeZone='UTC';"); It seems that even though that code ends up within a DO $EF$ BEGIN block, it seemed to correctly apply to the migration operations it needed to.

[roji]

Thanks @hhyyrylainen, I'll update the docs.

[iam-ben]

I got the same issue yesterday. @roji When I tried to debug, it looks like the Entity Framework deserializes timestamp without timezone into a DateTime with Kind set to Unspecified, instead of UTC. Then looks like I am required to set the Kind to UTC when making some other changes and updating back to the database. Is that the expected steps?

[roji]

@iam-ben yes, Npgsql 6.0 reads PG timestamp without time zone as DateTime.Unspecified, which is how that PG type is meant to be used; see the 6.0 release notes as well as this blog post for more context.

If the actual data you're storing in the database is UTC timestamps in your database, consider using timestamp with time zone instead of timestamp without time zone; that's the right PG type for that (the docs above provide a migration guide between the two types).

[iam-ben]

Thank you @roji for a quick response! Yeah... currently I have plenty of timestamp without timezone in the DB. Will need to have some cleanup and switch to timestamp with timezone for a long-run fix solution. For a temporary workaround, I will use the backward compatibility code for now.

[amitdixit]

This is how I made it working

[Column(TypeName = "timestamp without time zone")]
public DateTime? ModifiedDate { get; set; }

[ItWorksOnMyMachine]

All of my datatypes are timezone with time stamp in our database. The model type is DateTime. I'm setting a date value to DateTime.UtcNow in an insert, but am getting this error:

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 tried setting a ColumnAttribute on the column to Column(TypeName="timestamptz") and am getting this error:
MappingException: Schema specified is not valid. Errors:
(8329,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' of member 'CreatedDate' in type 'CodeFirstNamespace.TemporaryJoiner' is not compatible with 'Npgsql.timestamptz[Nullable=False,DefaultValue=,Precision=7]' of member 'createddate' in type 'CodeFirstDatabaseSchema.TemporaryJoiner'.

[propell-ant]

@ItWorksOnMyMachine, for those who met the similar problem,
the attribute [Column(TypeName="timestamptz")] in your code uses the wrong type name (actually not).
To make it work use the PostgreSQL native "timestamp with time zone" here.
The right attribute will be:
[Column(TypeName = "timestamp with time zone")]

[roji]

@propell-ant the type name timestamptz and timestamp with time zone should be equivalent. If you're seeing a problem with timestamptz that disappears when switching to timestamp with time zone, can you please open a new issue with a minimal code repro so I can fix that?

[propell-ant]

@roji, I have checked it again - indeed, both timestamptz and timestamp with time zone produces the same model without any errors on EF6.
I have probably misused the "NpgsqlDbType.TimestampTz" string recently in the Column attribute TypeName parameter, that made me think the timestamptz is also incorrect here.
I've updated my comment above to highlight that there is no issue.

[roji]

Thanks for confirming @propell-ant!