.net postgres EF core Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone'

Question

I have a .Net6 console app that uses nuget packages:

"Microsoft.EntityFrameworkCore, Version 6.0.8"
"Npgsql.EntityFrameworkCore.PostgreSQL, Version 6.0.6"
"Npgsql, Version 6.0.6"

I am using postgres 14.4 as thats installed and used for running scripts, etc.
I run a .sql script called "createTables.sql" that looks like this

create table if not exists "Messages" (
    "Id" uuid primary key,
    "Time" timestamp without time zone,
    "MessageType" text,
    "Message" text
);

My EF core context looks like this

namespace Database;

public class MessageContext : DbContext
{
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    var dbConfig = Configurer.Config.MessageDbConfig;
    optionsBuilder.UseNpgsql(new NpgsqlConnectionStringBuilder
      {
        Database = dbConfig.DbName,
        Username = dbConfig.Username,
        Password = dbConfig.Password,
        Host = dbConfig.Host,
        Port = dbConfig.Port,
        Pooling = dbConfig.Pooling
      }.ConnectionString,
     optionsBuilder => optionsBuilder.SetPostgresVersion(Version.Parse("14.4")));

    base.OnConfiguring(optionsBuilder);
  }

  public DbSet<Msg> Messages { get; set; }
}

public class Msg
{
  public Guid Id { get; set; } = Guid.NewGuid();
  public DateTime Time { get; set; } = DateTime.Now;
  public string MessageType { get; set; }
  public string Message { get; set; }
}

after running the .sql file using the psql command from the command line. I check on pgAdmin 4 and see
confirming that the column type is timestamp without timezone. Yet when i create a new Message via

    using var context = new MessageContext();
    context.Messages.Add(new Msg
    {
      MessageType = message.GetType()
        .FullName,
      Message = message.ToString()
    });
    context.SaveChanges();

when saving the changes, i get the exception

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> System.InvalidCastException: Cannot write DateTime with Kind=Local 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.
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)

Looks very contradicting because it is set without timezone and when the Msg is created is instantiates the "Time" property with the local time zone kind. If i switch it to "DateTime.UtcNow" instead of "DateTime.Now", it works. I don't get why it throws exception when i set it to "timestamp without time zone". obviously an easy solution is to just use utc time but regardless, why is the exception being thrown knowing the details?

Okan Karadag · Accepted Answer · 2022-09-12 23:09:06Z

10

npgsql 6.0 breaking changes

UTC timestamps have been cleanly separated from non-UTC timestamps, aligning with the PostgreSQL types. The former are represented by timestamp with time zone and DateTime with Kind UTC, the latter by timestamp without time zone and DateTime with Kind Local or Unspecified. It is recommended to use UTC timestamps where possible.

Solution 1: enable to the pre-6.0 behavior

MessageContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

Solution 2: use DateTimeOffset

public DateTime Time { get; set; } = DateTimeOffset.Now;

Excellent article for DateTimeOffset vs DateTime

answered Sep 12, 2022 at 23:09

Okan Karadag

3,0951 gold badge15 silver badges27 bronze badges

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

Comments

Serge GINDIN · Accepted Answer · 2024-03-07 07:35:06Z

4

The problem is in DateTime type map to timestamp with time zone by default. You should set type column for this property in data Builder annotation as timestamp without timezone. Then it is possible to write DateTime with DateTimeKind eq Local or Undefined, but cannot with DateTimeKind eq Utc value.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Message>(
        eb =>
        {
            eb.Property(b => b.Time).HasColumnType("timestamp(6)");            
        });
 }

or with Attribute annotation

public class Message
{
    [Column(TypeName = "timestamp(6)")]
    public string Time { get; set; }
}

answered Mar 7, 2024 at 7:35

Serge GINDIN

411 bronze badge

Comments

Catsher · Accepted Answer · 2024-09-24 18:56:11Z

1

None of answers weren't working for me, so I found another solution. When you creating entity with Datetime without UTC, but in your database used column with datetime with UTC, appears this error.

So solution is cast Datetime to UTC format. It can be done with .ToUniversalTime() method.

In your example:

public DateTime Time { get; set; } = DateTime.Now.ToUniversalTime();

(It's better to use DateTime.UtcNow in you example, I just provided more universal decision for example)

answered Sep 24, 2024 at 18:56

Catsher

111 bronze badge

Comments

Hekmat · Accepted Answer · 2023-12-11 18:17:31Z

-1

Simply add this line to Startup or Program

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

answered Dec 11, 2023 at 18:17

Hekmat

7291 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

.net postgres EF core Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone'

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related