Saving datetime offset in database

Question

I currently have a problem with saving the UTC time. I have an application in which you can specify a timer for a job and this is then saved in the database. However, I have the following problem:

The email is saved in this method:

public async Task<int> AddEmail(Email model)
{
    using var context = _contextFactory.CreateDbContext();

    model.ScheduledDate = model.ScheduledDate.ToUniversalTime();

    try
    {
        await context.Emails.AddAsync(model);
        await context.SaveChangesAsync();

        return model.Id;
    }
    catch (Exception)
    {
        return 0;
    }
}

public class Email
{
    public int Id { get; set; }

    public string Title { get; set; }

    public string Subject { get; set; }

    public string BodyHtml { get; set; }

    [Column(TypeName = “timestamp with time zone”)]
    public DateTimeOffset ScheduledDate { get; set; }

    public EmailStatus Status { get; set; }

    public string SenderEmail { get; set; }

    public List<EmailRecipient> EmailRecipients { get; set; } = [];
}

On the following line I discovered during debugging that the value of ScheduledDate is “2024-10-16 13:19:47.000 +0200”, which is correct at this moment. But before I save it I want to normalize the value to UTC. This also works because I can read the value “2024-10-16 11:19:47.000 +0000” from the model just before saving, which is correct.

However, when I look in the database afterwards, the value is not normalized to UTC, which I don't understand, as I was able to check the correct value just before saving during debugging and it was still correct. What is the problem?

The final goal would be that the UTC normalized time is stored in the DB, that the application also runs on my server, which is located at a different location and I can therefore also work with the timer transferring time zones.

"when I look in the database afterwards" - what tool are you using for that? — Fildor
– Fildor, Commented Oct 16, 2024 at 12:11
You use Entity Framework with Npgsql? You might want to read npgsql.org/doc/types/… if it applies here. Especially "In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), reading a timestamp with time zone as a DateTimeOffset returns a local offset based on the timezone of the server where Npgsql is running." — Ralf
– Ralf, Commented Oct 16, 2024 at 12:26
So it has the same effect when saving a timespam as DateTimeOffset that it saves a local offset based on the timezone of the server running Npgsql — alessio venturini
– alessio venturini, Commented Oct 16, 2024 at 12:31
Within the constraints named there "In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled)". Outside of that i would expect you get what you are asking for. And as i read it it has been written correctly to the database its changed to local when reading. But thats more or less guessing/assuming from the docs. — Ralf
– Ralf, Commented Oct 16, 2024 at 12:36

Richard Huxton · Accepted Answer · 2024-10-16 12:08:17Z

3

If you are saving as "timestamp with time zone" (which it sounds like you are) then it IS being saved in UTC (docs here)

It will be displayed according to whatever your client specifies as its local time-zone. Or, you can ask what time it is in a specific time-zone if you want.

answered Oct 16, 2024 at 12:08

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

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

2 Comments

alessio venturini Over a year ago

However, as I have shown in the database, the UTC time is not saved because it is 2 hours earlier at the time I checked it and also the time zone in the data cannot be +0200 if it is UTC, but should be +0000 in UTC format as recognized by the debugger.

Richard Huxton Over a year ago

Please read what I said about the timestamp being displayed in a local timezone.

Collectives™ on Stack Overflow

Saving datetime offset in database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related