Issue with DateTime.Kind and PostgreSQL in .NET 6 Web API

Question

I am encountering an issue while executing the SaveChanges() method in a background job in a .NET 6 Web API project. The code snippet below illustrates the scenario:

public bool UpdateServices()
{
    var activeServices = _context.services
                            .AsNoTracking()
                            .Where(service => service.active)
                            .ToList();

    foreach (var service in activeServices)
    {
        var server = _context.servers.FirstOrDefault(server => server.id == service.server_id && server.active);
        if (server != null)
        {
            service.status = 1;
            service.updated_at = DateTime.SpecifyKind(DateTime.UtcNow, DateTimeKind.Utc);
        }
        _context.services.Update(service);
    }
    _context.SaveChanges();
    return true;
}

However, during execution, I encounter the following exception at _context.SaveChanges():

Cannot write DateTime with Kind=Unspecified 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, or multirange. (Parameter 'value')

The underlying database is PostgreSQL, and the field updated_at is of type timestamp without time zone.

How can I resolve this issue and ensure that the DateTime values are correctly handled for PostgreSQL in this context?

williamqmoura · Accepted Answer · 2024-05-09 13:26:26Z

1

I saw this problem being resolved in other posts maybe the answers to these posts can help you:

In this first post_1 he follows a path to adjust the database configuration, basically uses "EnableLegacyTimestampBehavior" defining it in AppContext.SetSwitch().
In this second post_2 follows an implementation in the code to handle the DateTime before, so it creates an extention method SetKindUtc() to control the DateTime.

I hope I helped you find the solution to this error!

answered May 9, 2024 at 13:26

williamqmoura

112 bronze badges

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

Collectives™ on Stack Overflow

Issue with DateTime.Kind and PostgreSQL in .NET 6 Web API

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related