Mapping of SharePoint timezones to BCL/TimeZoneInfo

fowl2 commented 1 year ago

Describe the feature

It'd be really great if this library provided a helper method to convert SharePoint timezones into .Net timezones.

This is really useful:

When a time/date value needs to be presented to a user so that it can be presented in local time - eg. email.
For doing math on any time/date column - eg. so that adding a day to a date across a daylight savings change doesn't add/subtract an hour when displayed on the web.

Additional context

The TimeZone.Information property doesn't include the 'when' of any daylight savings transition(s), so isn't enough to synthesize a useful timezone.

Example of with implementation (uses Nodatime, but a pure TimeZoneInfo version would be almost identical minus the fallback):

        public static DateTimeZone ToDateTimeZone(this Microsoft.SharePoint.Client.TimeZone spTz)
        {
            var bclId = SharePointIdToBcl(spTz.Id);

            if (bclId is not null && DateTimeZoneProviders.Bcl.GetZoneOrNull(bclId) is DateTimeZone bclTz)
                return bclTz;

            if (spTz.Information.DaylightBias == 0)
                return DateTimeZone.ForOffset(Offset.FromSeconds(-spTz.Information.StandardBias * 60));

            throw new ArgumentOutOfRangeException($"Can't locate system timezone for SP TZ: Id='{spTz.Id}', Description='{spTz.Description}', Bias={spTz.Information.Bias}, StandardBias={spTz.Information.StandardBias}, DaylightBias={spTz.Information.DaylightBias}");
        }

        public static string? SharePointIdToBcl(this int spId)
            => spId switch
            {
                2 => "UTC", // (UTC) Dublin, Edinburgh, Lisbon, London
                3 => "Romance Standard Time", // (UTC+01:00) Brussels, Copenhagen, Madrid, Paris
                4 => "W. Europe Standard Time", // (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
                5 => "GTB Standard Time", // (UTC+02:00) Athens, Bucharest
                6 => "Central Europe Standard Time", // (UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague
                7 => "Belarus Standard Time", // (UTC+02:00) Minsk (old)
                8 => "E. South America Standard Time", // (UTC-03:00) Brasilia
                9 => "Atlantic Standard Time", // (UTC-04:00) Atlantic Time (Canada)
                10 => "US Eastern Standard Time", // (UTC-05:00) Eastern Time (US and Canada)
                11 => "Central Standard Time", // (UTC-06:00) Central Time (US and Canada)
                12 => "US Mountain Standard Time", // (UTC-07:00) Mountain Time (US and Canada)
                13 => "Pacific Standard Time", // (UTC-08:00) Pacific Time (US and Canada)
                14 => "Alaskan Standard Time", // (UTC-09:00) Alaska
                15 => "Hawaiian Standard Time", // (UTC-10:00) Hawaii
                16 => "Samoa Standard Time", // (UTC+13:00) Samoa
                17 => "New Zealand Standard Time", // (UTC+12:00) Auckland, Wellington
                18 => "E. Australia Standard Time", // (UTC+10:00) Brisbane
                19 => "Cen. Australia Standard Time", // (UTC+09:30) Adelaide
                20 => "Tokyo Standard Time", // (UTC+09:00) Osaka, Sapporo, Tokyo
                21 => "Singapore Standard Time", // (UTC+08:00) Kuala Lumpur, Singapore
                22 => "SE Asia Standard Time", // (UTC+07:00) Bangkok, Hanoi, Jakarta
                23 => "India Standard Time", // (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi
                24 => "Arabian Standard Time", // (UTC+04:00) Abu Dhabi, Muscat
                25 => "Iran Standard Time", // (UTC+03:30) Tehran
                26 => "Arabic Standard Time", // (UTC+03:00) Baghdad
                27 => "Israel Standard Time", // (UTC+02:00) Jerusalem
                28 => "Newfoundland Standard Time", // (UTC-03:30) Newfoundland
                29 => "Azores Standard Time", // (UTC-01:00) Azores
                30 => "Mid-Atlantic Standard Time", // (UTC-02:00) Mid-Atlantic
                31 => "Greenwich Standard Time", // (UTC) Monrovia, Reykjavik
                32 => "SA Eastern Standard Time", // (UTC-03:00) Cayenne, Fortaleza
                33 => "SA Western Standard Time", // (UTC-04:00) Georgetown, La Paz, Manaus, San Juan
                34 => "US Eastern Standard Time", // (UTC-05:00) Indiana (East)
                35 => "SA Pacific Standard Time", // (UTC-05:00) Bogota, Lima, Quito
                36 => "Canada Central Standard Time", // (UTC-06:00) Saskatchewan
                37 => "Central Standard Time (Mexico)", // (UTC-06:00) Guadalajara, Mexico City, Monterrey
                38 => "US Mountain Standard Time", // (UTC-07:00) Arizona
                39 => "Dateline Standard Time", // (UTC-12:00) International Date Line West
                40 => "Fiji Standard Time", // (UTC+12:00) Fiji
                41 => "Central Pacific Standard Time", // (UTC+11:00) Solomon Is., New Caledonia
                42 => "Tasmania Standard Time", // (UTC+10:00) Hobart
                43 => "West Pacific Standard Time", // (UTC+10:00) Guam, Port Moresby
                44 => "AUS Central Standard Time", // (UTC+09:30) Darwin
                45 => "China Standard Time", // (UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi
                46 => "N. Central Asia Standard Time", // (UTC+07:00) Novosibirsk
                47 => "West Asia Standard Time", // (UTC+05:00) Tashkent
                48 => "Afghanistan Standard Time", // (UTC+04:30) Kabul
                49 => "Egypt Standard Time", // (UTC+02:00) Cairo
                50 => "South Africa Standard Time", // (UTC+02:00) Harare, Pretoria
                51 => "Russian Standard Time", // (UTC+03:00) Moscow, St. Petersburg, Volgograd
                53 => "Cape Verde Standard Time", // (UTC-01:00) Cabo Verde
                54 => "Azerbaijan Standard Time", // (UTC+04:00) Baku
                55 => "Central America Standard Time", // (UTC-06:00) Central America
                56 => "E. Africa Standard Time", // (UTC+03:00) Nairobi
                57 => "Central European Standard Time", // (UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb
                58 => "Ekaterinburg Standard Time", // (UTC+05:00) Ekaterinburg
                59 => "FLE Standard Time", // (UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius
                60 => "Greenland Standard Time", // (UTC-03:00) Greenland
                61 => "Myanmar Standard Time", // (UTC+06:30) Yangon (Rangoon)
                62 => "Nepal Standard Time", // (UTC+05:45) Kathmandu
                63 => "North Asia East Standard Time", // (UTC+08:00) Irkutsk
                64 => "North Asia Standard Time", // (UTC+07:00) Krasnoyarsk
                65 => "Pacific SA Standard Time", // (UTC-04:00) Santiago
                66 => "Sri Lanka Standard Time", // (UTC+05:30) Sri Jayawardenepura
                67 => "Tonga Standard Time", // (UTC+13:00) Nuku'alofa
                68 => "Vladivostok Standard Time", // (UTC+10:00) Vladivostok
                69 => "W. Central Africa Standard Time", // (UTC+01:00) West Central Africa
                70 => "Yakutsk Standard Time", // (UTC+09:00) Yakutsk
                71 => "Central Asia Standard Time", // (UTC+06:00) Astana
                72 => "Korea Standard Time", // (UTC+09:00) Seoul
                73 => "W. Australia Standard Time", // (UTC+08:00) Perth
                74 => "Arab Standard Time", // (UTC+03:00) Kuwait, Riyadh
                75 => "Taipei Standard Time", // (UTC+08:00) Taipei
                76 => "AUS Eastern Standard Time", // (UTC+10:00) Canberra, Melbourne, Sydney
                77 => "Mountain Standard Time (Mexico)", // (UTC-07:00) Chihuahua, La Paz, Mazatlan
                78 => "Pacific Standard Time (Mexico)", // (UTC-08:00) Baja California
                79 => "Jordan Standard Time", // (UTC+02:00) Amman
                80 => "Middle East Standard Time", // (UTC+02:00) Beirut
                81 => "Central Brazilian Standard Time", // (UTC-04:00) Cuiaba
                82 => "Georgian Standard Time", // (UTC+04:00) Tbilisi
                83 => "Namibia Standard Time", // (UTC+01:00) Windhoek
                84 => "Caucasus Standard Time", // (UTC+04:00) Yerevan
                85 => "Argentina Standard Time", // (UTC-03:00) Buenos Aires
                86 => "Morocco Standard Time", // (UTC) Casablanca
                87 => "Pakistan Standard Time", // (UTC+05:00) Islamabad, Karachi
                88 => "Venezuela Standard Time\r\n", // (UTC-04:30) Caracas
                89 => "Mauritius Standard Time", // (UTC+04:00) Port Louis
                90 => "Montevideo Standard Time", // (UTC-03:00) Montevideo
                91 => "Paraguay Standard Time", // (UTC-04:00) Asuncion
                92 => "Kamchatka Standard Time", // (UTC+12:00) Petropavlovsk-Kamchatsky - Old
                93 => "UTC", // (UTC) Coordinated Universal Time
                94 => "Ulaanbaatar Standard Time", // (UTC+08:00) Ulaanbaatar
                95 => "UTC-11", // (UTC-11:00) Coordinated Universal Time-11
                96 => "UTC-02", // (UTC-02:00) Coordinated Universal Time-02
                97 => "UTC+12", // (UTC+12:00) Coordinated Universal Time+12
                98 => "Syria Standard Time", // (UTC+02:00) Damascus
                99 => "Magadan Standard Time\r\n", // (UTC+10:00) Magadan
                100 => "Kaliningrad Standard Time", // (UTC+02:00) Kaliningrad
                101 => "Turkey Standard Time", // (UTC+03:00) Istanbul
                102 => "Bangladesh Standard Time", // (UTC+06:00) Dhaka
                103 => "Bahia Standard Time", // (UTC-03:00) Salvador
                104 => "E. Europe Standard Time", // (UTC+02:00) E. Europe
                106 => "Russia Time Zone 3", // (UTC+04:00) Izhevsk, Samara
                107 => "Russia Time Zone 10", // (UTC+11:00) Chokurdakh
                108 => "Russia Time Zone 11", // (UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky
                109 => "Belarus Standard Time", // (UTC+03:00) Minsk
                110 => "Astrakhan Standard Time", // (UTC+04:00) Astrakhan, Ulyanovsk
                111 => "Altai Standard Time", // (UTC+07:00) Barnaul, Gorno-Altaysk
                112 => "Tomsk Standard Time", // (UTC+07:00) Tomsk
                114 => "Sakhalin Standard Time", // (UTC+11:00) Sakhalin
                115 => "Omsk Standard Time", // (UTC+06:00) Omsk
                _ => null
            };

Not all the SharePoint timezone descriptions match the Windows timezone descriptions, so I had to manually fix up some of them, but this script provided the starting point:

$sysTz = [TimeZoneInfo]::GetSystemTimeZones();

$web = get-pnpweb -Includes RegionalSettings.TimeZones
foreach ($spTz in $web.RegionalSettings.TimeZones | sort Id) {

$x = $sysTz | ? DisplayName -EQ $spTz.Description
"$($spTz.Id) => ""$($x.Id)"", // $($spTz.Description)"
}

Alternatively, you could use UTCToLocalTime and LocalTimeToUTC but that's a lot of server round trips (performance) and precludes using a useful library to reduce errors (reliability).

jansenbe commented 1 year ago

@fowl2 : we do have conversion built in (see https://github.com/pnp/pnpcore/blob/dev/src/sdk/PnP.Core/Model/SharePoint/Core/Internal/TimeZone.cs#L43-L227). Would that help you?

When a PnPContext is created we automatically load the web's regional settings (see https://pnp.github.io/pnpcore/using-the-sdk/basics-context.html#loading-additional-iweb-and-isite-properties-when-creating-a-pnpcontext), so the web's timezone information is present. This enables you to convert to and from UTC date times as shown in these test cases https://github.com/pnp/pnpcore/blob/dev/src/sdk/PnP.Core.Test/SharePoint/WebTests.cs#L756-L942.

Today the internal static TimeZoneInfo GetTimeZoneInfoFromSharePoint(string timeZoneDescription) method is internal, but this one possibly can be made public.

jansenbe commented 1 year ago

Hi @fowl2 , did my comments help solve your timezone questions?

jansenbe commented 1 year ago

@fowl2 : I'm closing this issue as there's no recent feedback. Always happy to re-open if still relevant, just let me know. Thanks for using PnP Core SDK and providing the feedback.

fowl2 commented 1 year ago

@jansenbe sorry I've had this browser tab open for a few days, let me press send!

Ahh interesting! Without looking at the implementation I would have assumed LocalTimeToUtc and UtcToLocalTime were simply calling the server methods with that same name, and suffer from the associated round-trip penalty.

They appear to use the system timezone database for transition but site information for offsets. This possibly "torn" timezone may be produce results different from. It's a tricky problem.
Should they be setting DateTime.Kind?

Making GetTimeZoneInfoFromSharePoint or similar public would be useful!

On the implementation:

It is matching on "Description" instead of Id - does the service make any promises on stability here?
There seems to be some fallback with fuzzy matching, I'm concerned about the accuracy here - eg. both Fuji and NZ are UTC+12, but only NZ has daylight time.

jansenbe commented 1 year ago

@fowl2 : using Id versus "Description" indeed is a better approach, I've changed our internal implementation. Also provided a GetTimeZoneInfo method in ITimeZone to get the equivalent .NET timezone object for a SharePoint timezone. Would this be sufficient for your use cases?

jansenbe commented 1 year ago

@fowl2: also adding here that indeed the user should specify the correct input.

// Convert to UTC time
var utcDate = localDate.ToUniversalTime();

// Convert to Web's timezone
var localSiteTime = context.Web.RegionalSettings.TimeZone.UtcToLocalTime(utcDate);

I think this can be closed now, happy to hear if you feel different.

pnp / pnpcore

Mapping of SharePoint timezones to BCL/TimeZoneInfo #1008

Category

Describe the feature

Additional context