Get offset minutes from IANA (TZDB) time zone-CodePudding

I have a table T1 with Id, Name and TimeZone as columns. The TimeZone column has IANA (TZDB) format like America/Chicago I'm getting the data from T1 like

response = T1.Where(t => t.Id == 9).Select(rez => new
           {
              RezName = rez .Name,
              Offset = ...
           });

Inside Offset I need to get the current offset in minutes (like -300 for America/Chicago that has offset -05). Is there a way to get the offset minutes inside the LINQ query or only by iterating after the select and calculate the local time for each element?

CodePudding user response：

As you originally tagged your question with nodatime, here is how you can leverage NodaTime for this:

using NodaTime;

...

Instant now = SystemClock.Instance.GetCurrentInstant();
response = T1.Where(t => t.Id == 9)
   .Select(rez => new
   {
      RezName = rez.Name,
      TimeZone = rez.TimeZone
   })
   .AsEnumerable()
   .Select(x => new
   {
      RezName = x.RezName,
      Offset = (int) DateTimeZoneProviders.Tzdb[x.TimeZone]
                         .GetUtcOffset(now).ToTimeSpan().TotalMinutes
   });

If you are leveraging .NET on Linux or macOS, or if you are using .NET 6 or higher on Windows, you can do this without Noda Time:

DateTimeOffset now = DateTimeOffset.UtcNow;
Instant now = SystemClock.Instance.GetCurrentInstant();
response = T1.Where(t => t.Id == 9)
   .Select(rez => new
   {
      RezName = rez.Name,
      TimeZone = rez.TimeZone
   })
   .AsEnumerable()
   .Select(x => new
   {
      RezName = x.RezName,
      Offset = (int) TimeZoneInfo.FindSystemTimeZoneById(x.TimeZone)
                         .GetUtcOffset(now).TotalMinutes
   });

Yet another alternative would be to use `TZConvert.GetTimeZoneInfo from TimeZoneConverter with similar code to the second example.

Keep in mind, that all of these return the current offset from UTC (as minutes, with positive values being East of GMT). For America/Chicago, that's presently -300 because daylight saving time is in effect. When daylight time ends, it will return -360.

Note that in the above examples, the time zone code must occur after the query materializes, as it would be impossible for a LINQ provider like EF to translate it to a SQL query.