.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

Good place to put the setting is the static constructor of the DB Context.

In this case startup class remains cleaner.
Also it's useful if you have several projects using the same DB Context.
E.g:

public class MyContext : DbContext
{
    static MyContext()
    {
        AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
    }
    
    // Other stuff of your context
}

Update July 2023 - Solution #2

Today I've faced with an issue when this trick didn't work in one of my projects.
After researching I found another way to make sure the flag can be set once before any other code runs.

It's done with help of ModuleInitializer attribute:

Note: it requires C#9 and higher (.NET 5+)

Just add a new file to your project where DbContext is located.
And put next content:

using System.Runtime.CompilerServices;

namespace Your.Project.Namespace;

public static class MyModuleInitializer
{
    [ModuleInitializer]
    public static void Initialize()
    {
        AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
    }
}

Answer 4

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 5

In my case it was a mistake I made

InvitedOn = DateTime.Now

should have been

InvitedOn = DateTime.UtcNow

and it worked

Answer 6

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 7

Maybe a little bit late, but for me I just created this converter

public class DateTimeToDateTimeUtc : ValueConverter<DateTime, DateTime>
{
    public DateTimeToDateTimeUtc() : base(c => DateTime.SpecifyKind(c, DateTimeKind.Utc), c => c)
    {

    }
}
 protected sealed override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Properties<DateTime>()
            .HaveConversion(typeof(DateTimeToDateTimeUtc));
    }

Answer 8

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 9

For me, I have updated the proyect with dotnet ef migrations add

Command reference:

dotnet ef migrations add <MigrationName> --context <YoutContextClassName> --startup-project <../Path to your startup proyect> --verbose

After it postgre have created the migration file changing all datetime from "with time zone" to "without time zone"

I hope It was usefull for you

NOTA:

• I've Updated from net5.0 to net6.0
• postgresql 6.0.X
• Using code-first to database.

UPDATE:

Also or after that you need to check Kind of DateTime if not is Utc you can force It with static SpecifyKind method of DateTime

Greetings

Answer 10

I have also stumbled across this error while trying to persist a DateTime with Kind = UTC (I am actually using DateTime.UtcNow) in a "timestamp without time zone" column. IMO, the error does not make sense, since UTC datetimes should be

My fix was to switch to "timestamp with time zone" because:

• it seems to work as expected: I get the {UTC timestamp}+00
• "EnableLegacyTimestampBehavior" sounds like something that is likely to be deprecated in the future
• Converting all DateTime's to UTC Kind while elegant sounds like a hack that might backfire in some scenarios where I really want some DateTime of another Kind

Answer 11

I had a similar problem and to solve this, I used DateTime.ToUniversalTime() Method in C#.

For example,

Date= DateTime.Now.ToUniversalTime();

Or,

DateTime dateTime = DateTime.Now.ToUniversalTime();

To obtain more detail, you can browse this site https://www.geeksforgeeks.org/datetime-touniversaltime-method-in-c-sharp/

Answer 12

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 13

I'm too late with this but this is how I usually handle this:

DateTime.SpecifyKind((DateTime)MyNullableDateTimeField!, DateTimeKind.Utc)

Answer 14

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.