.NET6 and DateTime problem. Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone'

Question

I have common problem.

Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone'

And I want to enable Legacy Timestamp behavoour as is documented here: https://github.com/npgsql/doc/blob/main/conceptual/Npgsql/types/datetime.md/

public MyDbContext(DbContextOptions<MyDbContext> contextOptions) : base(contextOptions)
        {
            AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
            AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
        }

But doesn't work. I still get same error.

What I am doing wrong. Why legacy behaviour doesn't work?

Answer 1

A. Solved by adding

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
to the Startup Configure method.

---

B. Or in case you have no Startup class at all and all your initialization is inside Program.cs with a host builder then your file ending might look like:

... //adding services etc
var host = builder.Build();
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
... //your other scoped code
await host.RunAsync();

---

To query database using System.Linq.Dynamic we need to specify time kind too.
Filter example: $"User.BirthDate>={time.ToStringUtc()}"

public static string ToStringUtc(this DateTime time)
{
    return $"DateTime({time.Ticks}, DateTimeKind.Utc)";
}

---

At the same time the answer https://stackoverflow.com/a/70142836/7149454 by @istvan-kardkovacs applies. Basically to add an .SetKindUtc() to every = new DateTime() you are creating.. The switch above didn't obviously worked for me in a background hosted service that was populating database before any other code was executed.

Answer 2

You have to set the DateTimeKind for all DateTime fields in the create,insert,update operations and for the DateTime comparisons in the Linq queries. I have created a small extension method and add to all date fields.

public static class DateTimeExtensions
{
    public static DateTime? SetKindUtc(this DateTime? dateTime)
    {
        if (dateTime.HasValue)
        {
            return dateTime.Value.SetKindUtc();
        }
        else
        {
            return null;
        }
    }
    public static DateTime SetKindUtc(this DateTime dateTime)
    {
        if (dateTime.Kind == DateTimeKind.Utc) { return dateTime; }
        return DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
    }
}

And unit-tests to show functionality:

using System;
using System.Diagnostics.CodeAnalysis;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace MyNamespace;

[TestClass]
[ExcludeFromCodeCoverage]
public class DateTimeExtensionsTests
{
    [TestMethod]
    public void SetKindUtcNullInputTest()
    {
        DateTime? input = null;
        DateTime? result = input.SetKindUtc();
        Assert.IsNull(result);
    }

    [TestMethod]
    public void SetKindUtcNonNullRegularDateInputTest()
    {
        DateTime? input = DateTime.Now;
        DateTime? result = input.SetKindUtc();
        Assert.IsNotNull(result);
        /* below is the primary functionality.  if the input did not have a "Kind" set, it gets set to DateTimeKind.Utc */
        Assert.AreEqual(DateTimeKind.Utc, result.Value.Kind);
    }

    [TestMethod]
    public void SetKindUtcNonNullOffsetDateInputTest()
    {
        DateTime? input = DateTime.Now;
        DateTime withKindUtcInput = DateTime.SpecifyKind(input.Value, DateTimeKind.Utc);
        DateTime? result = withKindUtcInput.SetKindUtc();
        Assert.IsNotNull(result);
        /* Utc "in" remains "Utc" out */
        Assert.AreEqual(DateTimeKind.Utc, result.Value.Kind);
    }
    
    [TestMethod]
    public void UnspecifiedKindIsOverwrittenTest()
    {
        DateTime? input = DateTime.Now;
        DateTime withKindUtcInput = DateTime.SpecifyKind(input.Value, DateTimeKind.Unspecified);
        DateTime? result = withKindUtcInput.SetKindUtc();
        Assert.IsNotNull(result);
        /* note the behavior.  "DateTimeKind.Unspecified" with overwritten with DateTimeKind.Utc */
        Assert.AreEqual(DateTimeKind.Utc, result.Value.Kind);
    }
    
    [TestMethod]
    public void LocalKindIsOverwrittenTest()
    {
        DateTime? input = DateTime.Now;
        DateTime withKindUtcInput = DateTime.SpecifyKind(input.Value, DateTimeKind.Local);
        DateTime? result = withKindUtcInput.SetKindUtc();
        Assert.IsNotNull(result);
        /* note the behavior.  "DateTimeKind.Local" with overwritten with DateTimeKind.Utc */
        Assert.AreEqual(DateTimeKind.Utc, result.Value.Kind);
    }    
}

Answer 3

I added code to my DbContext to set this on all date properties on my models:

//dbcontext
public override int SaveChanges()
{
    _changeTrackerManager?.FixupEntities(this);
    return base.SaveChanges();
}

//don't forget the async method!
public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default)
{
    _changeTrackerManager?.FixupEntities(this);
    return base.SaveChangesAsync();
}

This IChangeTrackerManager dependency will be injected, and then any time entities are saved it will call this method below which will fixup all the utc date time kinds.

public void FixupEntities(DbContext context)
{
    var dateProperties = context.Model.GetEntityTypes()
        .SelectMany(t => t.GetProperties())
        .Where(p => p.ClrType == typeof(DateTime))
        .Select(z => new
        {
            ParentName = z.DeclaringEntityType.Name,
            PropertyName = z.Name
        });

    var editedEntitiesInTheDbContextGraph = context.ChangeTracker.Entries()
        .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified)
        .Select(x => x.Entity);

    foreach (var entity in editedEntitiesInTheDbContextGraph)
    {
        var entityFields = dateProperties.Where(d => d.ParentName == entity.GetType().FullName);

        foreach (var property in entityFields)
        {
            var prop = entity.GetType().GetProperty(property.PropertyName);

            if (prop == null)
                continue;

            var originalValue = prop.GetValue(entity) as DateTime?;
            if (originalValue == null)
                continue;

            prop.SetValue(entity, DateTime.SpecifyKind(originalValue.Value, DateTimeKind.Utc));
        }
    }
}

Answer 4

modifying https://stackoverflow.com/a/71179214/507421 from @DLeh slightly

    private void ConvertDateTimesToUniversalTime()
    {
        var modifiedEntites = ChangeTracker.Entries<IHaveAggregateRootId>()
                .Where(e => (e.State == EntityState.Added || e.State == EntityState.Modified || e.State == EntityState.Deleted)).ToList();
        foreach (var entry in modifiedEntites)
        {
            foreach (var prop in entry.Properties)
            {
                if (prop.Metadata.ClrType == typeof(DateTime))
                {
                    prop.Metadata.FieldInfo.SetValue(entry.Entity, DateTime.SpecifyKind((DateTime)prop.CurrentValue, DateTimeKind.Utc));
                }
                else if (prop.Metadata.ClrType == typeof(DateTime?) && prop.CurrentValue != null)
                {
                    prop.Metadata.FieldInfo.SetValue(entry.Entity, DateTime.SpecifyKind(((DateTime?)prop.CurrentValue).Value, DateTimeKind.Utc));
                }
            }
        }
    }

Answer 5

Same thing happened to me when my Controller deserialize the object and I was trying to insert/update it with EF and Npgsql.EntityFrameworkCore.PostgreSQL. I used ToUniversalTime() to all dates and it worked for me.

Answer 6

Nick has already answered this question, I just want to add another solution to this time zone problem.

Instead of enabling that option, you can just convert all datetime before being written using this extension. This is what I did.

Create this extension class:

public static class UtcDateAnnotation
{
    private const string IsUtcAnnotation = "IsUtc";
    private static readonly ValueConverter<DateTime, DateTime> UtcConverter = new ValueConverter<DateTime, DateTime>(convertTo => DateTime.SpecifyKind(convertTo, DateTimeKind.Utc), convertFrom => convertFrom);

    public static PropertyBuilder<TProperty> IsUtc<TProperty>(this PropertyBuilder<TProperty> builder, bool isUtc = true) => builder.HasAnnotation(IsUtcAnnotation, isUtc);

    public static bool IsUtc(this IMutableProperty property)
    {
        if (property != null && property.PropertyInfo != null)
        {
            var attribute = property.PropertyInfo.GetCustomAttribute<IsUtcAttribute>();
            if (attribute is not null && attribute.IsUtc)
            {
                return true;
            }

            return ((bool?)property.FindAnnotation(IsUtcAnnotation)?.Value) ?? true;
        }
        return true;
    }

    /// <summary>
    /// Make sure this is called after configuring all your entities.
    /// </summary>
    public static void ApplyUtcDateTimeConverter(this ModelBuilder builder)
    {
        foreach (var entityType in builder.Model.GetEntityTypes())
        {
            foreach (var property in entityType.GetProperties())
            {
                if (!property.IsUtc())
                {
                    continue;
                }

                if (property.ClrType == typeof(DateTime) ||
                    property.ClrType == typeof(DateTime?))
                {
                    property.SetValueConverter(UtcConverter);
                }
            }
        }
    }
}
public class IsUtcAttribute : Attribute
{
    public IsUtcAttribute(bool isUtc = true) => this.IsUtc = isUtc;
    public bool IsUtc { get; }
}

And add that converter in your DbContext file:

protected override void OnModelCreating(ModelBuilder builder)
{
     builder.ApplyUtcDateTimeConverter();//Put before seed data and after model creation
}

This will result all your DateTime and DateTime? object got converted to Utc kind of date before being written to Db.

This will be my one way ticket to support this PostgreSql Db, because I have a requirement to support some database (Sql Server, PostgreSql, and soon MySql). Manually convert every datetime value to Utc won't be a good solution.

Our application hasn't had requirement for time zone yet, but using that extension we could easily add time zone support in it.

Answer 7

I found the answer. Don't add the lines to your dB Context. Instead in a WFP application add to MainWindow.xaml.cs as follows:

add the line "EnableLegacyTimestampBehavior" before the InitializeComponent statement in the public MainWindow method.

You don't need the "DisableDateTimeInfinityConversions" statement.

Your code with DateTime will now work.

Answer 8

In my case it was a mistake I made

InvitedOn = DateTime.Now

should have been

InvitedOn = DateTime.UtcNow

and it worked