How to Convert from .Net TimeZone to MySQL TimeZone

Question

I am trying to find out how to take the TimeZone or TimeZoneInfo object in .NET and use it to convert all my dates in my MySQL database from UTC to the Local Users Correct Time Zone. I am building a Desktop App. I'm planing on using the built in Convert_TZ function, but the Information that i get back from those objects does not line up with the built in method.

I found this article (http://codeofmatt.com/2014/04/07/working-with-time-zone-names-in-net/) which puts the Time Zone Info object into a better format for display but it still does not match up.

I am very new to using the TimeZone object. If there is a better way to get the current Time Zone and is easier to use then I will try that.

Answer 1

MySQL uses IANA time zones. You can read about MySQL's time zone support here. You can read about the different types of time zones in the timezone tag wiki.

.NET on Windows works with Microsoft Windows time zones. This is done via the TimeZoneInfo object. The older TimeZone object is also in the .NET Framework, but should not be used anymore.

To work with IANA time zones instead of Windows time zones within .NET, you can use the Noda Time library. Using Noda Time, you can get the system's local IANA time zone like this:

string tz = DateTimeZoneProviders.Tzdb.GetSystemDefault().Id;

Note that .NET on Linux or macOS already uses IANA time zones with the TimeZoneInfo object, so Noda Time is optional.

If you already are using Windows time zones, you can convert them to IANA time zones, using the methods described here.

Also note that in a previous version of this answer, I advised against using MySQL's built-in time zone support on Windows because they had not been keeping the time zone data current. This was reported to the MySQL team here, and has been fixed. If you are running MySQL on Windows, you will find the latest time zone data files here.