Question 219939 in PostgreSQL Question posted in
The official documentation can be found here.

C# DateTime.MinValue is handled differently by PostgreSQL, EFCore or Npgsql?

I am saving a C# DateTime.MinValue to a PostgreSQL column of type timestamp with timezone with EFCore 7.05 and Ngpsql 7.04

  • Date = {1/01/0001 12:00:00 AM}
  • Kind = Utc

When I load the column, the value becomes

  • Date = {1/01/0001 12:00:00 AM}
  • Kind = Unspecified

Other non-default values can be saved and loaded fine with the correct DateTime.Kind value.
Does anybody know what is happening in EFCore, npgsql and Postgres? Which one should I look into?

Tags:

2

Answers


  1. 0

    Might be because the minimum value stored by postgresql is January 1, 4713 BC, documented here.

    And for C# the minimum date is 1/01/0001, documented here.

    This might be causing DateTime.MinValue set to Unspecified.

    Login or Signup to reply.
  2. 0

    DateTime.MinValue almost isn’t ever used to represent an actual timestamp, but rather a special marker or value that’s smaller than all other values. The corresponding value in PostgreSQL is -infinity, which is why Npgsql performs that conversion. The conversion is performed both ways, both when you write DateTime.MinValue to PG, and when you read -infinity from PG as a .NET DateTime.

    Note that DateTime.MinValue has Kind=Unspecified, not Utc; this is why reading -infinity results in Kind=Unspecified.

    Note that the conversion behavior can be disabled via a special compatibility switch, but that’s not recommended: that means that you’ll get an actual timestamp of 1/1/0001 in the database rather than -infinity.

    See these Npgsql docs for more info.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search