Have a DateTime Kind.Unspecified column in Postgresql in EF Core

Question

I have this database column:

[MaxLength(10)]
[Required]
public List<NpgsqlRange<System.DateTime>> Dates { get; set; }

How do I make this allow only Kind.Unspecified?

I get this error when I insert:

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.

Every single solution I have seen is to use Npgsql.EnableLegacyTimestampBehavior. However, that is to allow unspecified type in a UTC column, and I don't want to allow unspecified type in a UTC column. I want the column to be strongly typed as unspecified.

Also, when I use EnableLegacyTimestampBehavior, it saves it to postgresql with the "+12" in it, which IS UTC (which I don't want).

@Orifjon Dates = table.Column<List<NpgsqlRange<System.DateTime>>>(type: "tstzmultirange", maxLength: 10, nullable: true) — BeniaminoBaggins
– BeniaminoBaggins, Commented Sep 11, 2023 at 3:18

Orifjon · Accepted Answer · 2023-09-11 03:42:51Z

0

It seems that, your database type does not match your code type here. Database type timestamp without time zone corresponds to DateTime(Kind=Unspecified) and timestamp with time zone corresponds to DateTime(Kind=UTC). for range and multirange types, these valuers cannot be mixed.

If you want to keep C# type, you can change Dates = table.Column<List<NpgsqlRange<System.DateTime>>>(type: "tstzmultirange", maxLength: 10, nullable: true) to Dates = table.Column<List<NpgsqlRange<System.DateTime>>>(type: "tsmultirange", maxLength: 10, nullable: true)

Since you can get different kind of DateTime, you can explicitly create new DateTime instance with Kind=Unspecified before assigning it to Dates property.

For more details, See: Type mapping

Postgresql built in types

p.s.: You can also use NodaTime which aligns better with Postgresql types.

answered Sep 11, 2023 at 3:42

Orifjon

1,1079 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BeniaminoBaggins Over a year ago

Thanks. That code I commented was generated. How do I change what is generated? The code in the question is the only place I defined the type.

Orifjon Over a year ago

It is generated based on your actual database type. If you can't change database type, you have to go with DateTime(Kind=UTC).

Collectives™ on Stack Overflow

Have a DateTime Kind.Unspecified column in Postgresql in EF Core

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related