How to correctly save DateTime using Entity Framework Core?

Question

I have a class containing a DateTime property. How to correctly save time using Entity Framework Core? I will give an example below.

I use the TIMESTAMP format to store the DateTime in the database. I have a server and database (MySQL) that have timezone +3 (Moscow).

SELECT TIMEDIFF(current_timestamp(), utc_timestamp()); // result = 03:00:00

I also have a program that can run on the PC of various users. The program saves the object containing the DateTime property in the database:

obj.DateTime = DateTime.Parse("2020-10-13T19:00:00+0300");

Now let's look at a few situations:

The program is launched on a PC with a time zone of +3 (Moscow)

Console.WriteLine(obj.DateTime?.ToString()); // 2020-10-13 19:00:00

I check the data in the database using phpMyAdmin and I see 2020-10-13 19:00:00. Everything is OK. The time zone of the PC is equal to the time zone of the database, so we see the same result.

The program is open on a PC with a time zone of +6:30 (Yangon)

Console.WriteLine(obj.DateTime?.ToString()); // 13.10.2020 22:30:00

I check the data in the database using phpMyAdmin and I see 2020-10-13 22:30:00. Fail! I should have seen everything at the same time 19:00, not 22:30. The obj.DateTime property contained the same time!

How can I now understand that these records contain equal time? Why does the EF not transfer DateTime to the UTC?

the timezone of the server is fixed and you can grab the timezone information from the users computer and now you correct it before saving. and of course vice versa — nbk
– nbk, Commented Oct 31, 2020 at 0:30
You will likely find this easier if you persist your times in UTC on the server and then apply a local timezone-specific correction whenever you access the server (read or write). You might also want to look at Noda Time (nodatime.org). It smooths out a lot of .NET's time weirdnesses — Flydog57
– Flydog57, Commented Oct 31, 2020 at 1:40

Shervin Ivari · Accepted Answer · 2020-10-31 05:30:38Z

If you want to save DateTime base on the specific zone, not the server time I suggest you create a service, and every time Instead of calling DateTime you call that service. there is a class called TimeZoneInfo which represents any time zone in the world and it's available on the system namespace.

Make sure you are not using DateTime for getting the current date Directly

DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("Russian Standard Time"));

This will Converts a Coordinated Universal Time (UTC) to the time in a specified time zone. You can find more about zone ids in https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones

If you are using Dependency injection:

public class DateTimeService : IDateTime
{
    public DateTime Now => TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("Russian Standard Time"));
}

public interface IDateTime
{
    DateTime Now { get; }
}

or if you prefer using a Helper Class:

public static class DateTimeHelper
{
    public static DateTime Now() => TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("Russian Standard Time"));
}

Collectives™ on Stack Overflow

How to correctly save DateTime using Entity Framework Core?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related