C#•2y ago

❔ PostgreSQL DateTime in EF Core

So I have an app that uses EF Core with Npgsql that runs locally and I want to connect to a postgresql database which runs in a docker container. The connection and adding stuff works and all, but when I wanna save my changes, then I get this error Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported.. Googling didn't really yield any valuable answers either fluffyFoxThink

. So my columns are in the picture and my model is this. Any idea how to handle DateTime? in this context?

public sealed class MyModel
{
    [DataType(DataType.DateTime)]
    public DateTime? Start { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime? End { get; set; }
}

public sealed class MyModel
{
    [DataType(DataType.DateTime)]
    public DateTime? Start { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime? End { get; set; }
}

23 Replies

Saber•2y ago

use DateTimeOffset?

HimmDawg•2y ago

Does that work, when I import stuff from a csv, that is a datetime? Right, so the solution is actually using this line of code in my setup method: AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

Jayy•2y ago

no, this is a hack for migration to 6 its not a solution dont do this youve hit the breaking change in npgsql in version 6 its an interesting one @HimmDawg what column types are your columns right now

Amos•2y ago

Using DateTimeOffset is the correct approach, all your code should work just fine with this one change. There's a reason the feature was made "Legacy," by enabling it isn't solving anything. Just moving the problem. 🙂

Jayy•2y ago

this also isnt the correct approach lol it very greatly depends on WHAT your goals are good lord yall

Amos•2y ago

Ah, TIL. That's how I handled it! 😄

Jayy•2y ago

did you handle it that way like what was ur reason

Amos•2y ago

Nothing in particular, I matched the accepted field types with what I could do in C#. Really naïve approach granted. Do you have a doc with the correct way to approach date/time storing using Postgres?

Jayy•2y ago

https://www.npgsql.org/efcore/release-notes/6.0.html#breaking-changes yes

Amos•2y ago

Muchas gracias.

Jayy•2y ago

https://www.npgsql.org/doc/release-notes/6.0.html theres actually a decent amount of nuance here tread carefully read both links closely

Amos•2y ago

So implementation is heavily contextualised?

Jayy•2y ago

yes

Amos•2y ago

Will do!

Jayy•2y ago

plz take note of what your columns CURRENTLY are as well as how you are writing to them you risk data loss if you arent exact in how you handle it

Amos•2y ago

Yeah that's all fine. I'm learning still so no production code as it were.

Jayy•2y ago

you are likely fine but def rego through those docs and make changes as appropriate i recommend just switching to nodatime

Amos•2y ago

Appreciate your input! 🙂

HimmDawg•2y ago

Ouhw

I see So my columntypes right now are timestamp with timezone

HimmDawg•2y ago

And in the model, I have

public DateTime? Start { get; set; }

public DateTime? End { get; set; }

public DateTime? Start { get; set; }

public DateTime? End { get; set; }

And the goal is to read some data from a csv and import it to a postgresql db in a docker container What I also have is a TypeConverter, that converts the respective inputs to a datetime. So following your link, I just do DateTime.SpecifyKind(dateTime, DateTimeKind.Utc); and i should be good fluffyFoxLurk

that worked owo

thanks for the information.

Jayy•2y ago

Yes this can work, I'd highly recommend upgrading to nodatime tho

HimmDawg•2y ago

NodaTime seems pretty handy. Makes stuff a bit easier

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ PostgreSQL DateTime in EF Core