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

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.

IMPORTANT

WIth latest .NET9 EF migrations you need to also add this `SetSwitch` line to you context constructor, so that EF doesn't try to alter your no-timezone fields every time You do an `Add-Migration`.

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);
    }
}

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);
    }    
}

In my case it was a mistake I made

InvitedOn = DateTime.Now

should have been

InvitedOn = DateTime.UtcNow

and it worked

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.

UPDATE:

Instead of using that extension class, you should use DatetimeOffset, this will store UTC information and can be used with SQL Server and PostgreSQL (all tested).

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));
    }

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));
        }
    }
}

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/

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 without timezone.

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

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));
                }
            }
        }
    }

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

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.

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

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

For those using EF, it is resolved by standardizing the use of DateTime:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
   base.ConfigureConventions(configurationBuilder);

   configurationBuilder.Properties<DateTime>().HaveColumnType("timestamp without time zone");
   configurationBuilder.Properties<DateTime?>().HaveColumnType("timestamp with time zone");
}

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.