TimeZoneCode to TimeZoneInfo

Question

In our MS dynamics CRM project we created a mass-user upload batch.
The batch reads from an excel file and does a mass upload of the users.
One of the things this batch needs to set is the timezonecode.

In the excel file the timezone will be written as eg "UTC+1"
The code used by CRM seems to be the timezonecode SQL-server is using as can be found here.

What is the cleanest way of mapping these?
My ideas so far:

Hardcode a conversion store
Fetch the codes from CRM somehow
Fetch the codes from SQL somehow

Currently we just implemented our own conversion class with hard coded values.
Is there a better way? Can we leverage the .net TimezoneInfo class somehow?

Update
To get all the CRM timezones we did the following:

var colSet = new ColumnSet(true);
var query = new QueryExpression(TimeZoneDefinition.EntityLogicalName) { ColumnSet = colSet};
var timeZoneDefs = service.RetrieveMultiple(query).Entities.Select(tz => tz.ToEntity<TimeZoneDefinition>());

But it seems the only properties filled are Id, Code, StandardName and UserInterfaceName. It seems only the UI name contains the offset we're looking for.

Is there any way to ensure the Bias property is loaded?

As I'm researching your question, it seems very strange to me that CRM has their own implementation of time zones. I wonder why they didn't use `TimeZoneInfo` to begin with? Any thoughts on this? — Matt Johnson-Pint, Jun 13 '13 at 16:39
@MattJohnson I'm quite sure that the reason is somewhere in the history. Some architectural decisions where made based on .NET 1. Some of these where changed, but some are more or less identical. Would love to see NodaTime there btw ;) — ccellar, Jun 13 '13 at 18:39
Daryl's code is pretty good, but you'd want to confirm that the CRM `standardname` field does indeed align with `TimeZoneInfo.Id`. I don't have a list to compare, but check Arizona. If it says "Arizona" in the `standardname`, then it's no good. If it says "US Mountain Standard Time", then you should be good to go. The "US" must be in there, it has to match the registry keys at `HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones` — Matt Johnson-Pint, Jun 14 '13 at 01:55
Noda Time in CRM? Yeah right, I doubt MS would go for that! :) But I am working to get it available from SQL Server (via SQLCLR) — Matt Johnson-Pint, Jun 14 '13 at 01:56
BTW - SQL Server has no concept of time zones. The link you gave is for SQL Server "Notification Services", which died in SQL 2005. However, I compared it to [this list](http://msdn.microsoft.com/en-us/library/bb887715.aspx) from CRM 4.0 and it appears to be strikingly similar. My guess is that at one point CRM must have used SQL Notification Services so they made them align. I could not find a similar list for CRM 2011, so I have no idea if they have changed at all. — Matt Johnson-Pint, Jun 14 '13 at 02:15
@MattJohnson CRM 2011 requires SQL Server 2008 R2 or newer, so that may be why you're not finding the list. Also, from my edit, you can see that the Arizona Time Zone is correctly displaying "US Mountain Standard Time" — Daryl, Jun 14 '13 at 12:30

Daryl · Accepted Answer · 2013-06-14T12:13:17.283

Here is the code that I use to get a user's TimeZoneInfo. It retrieves the TimeZoneDefinition from CRM based on the name, but I believe you can look it up by the Bias matching the UTC offset from your excel file.

public static TimeZoneInfo GetUserTimeZone(IOrganizationService service, Guid userId)
{
    int timeZoneCode = 35; //default timezone to eastern just incase one doesnt exists for user
    var userSettings = service.Retrieve(UserSettings.EntityLogicalName, userId, new ColumnSet("timezonecode")).ToEntity<UserSettings>();

    if ((userSettings != null) && (userSettings.TimeZoneCode != null))
    {
        timeZoneCode = userSettings.TimeZoneCode.Value;
    }

    return GetTimeZone(service, timeZoneCode);
}

public static TimeZoneInfo GetTimeZone(IOrganizationService service, int crmTimeZoneCode)
{
    var qe = new QueryExpression(TimeZoneDefinition.EntityLogicalName);
    qe.ColumnSet = new ColumnSet("standardname");
    qe.Criteria.AddCondition("timezonecode", ConditionOperator.Equal, crmTimeZoneCode);
    return TimeZoneInfo.FindSystemTimeZoneById(service.RetrieveMultiple(qe).Entities.First().ToEntity<TimeZoneDefinition>().StandardName);
}

Edit - Bias is null

This could just be our on prem version of CRM, but this is what it is currently populated in CRM for any time zone that is -5, -6,-7, or -8. CRM TimeZoneDefinition .

This would make the bias lookup null & void.

On a side note, 99% of our users are on Eastern Time, but we have a few in California, and I haven't heard of any issues as of yet. But now I'm wondering if we bothered to test this before and after DST...

Interesting how you query into CRM, but you seem to be matching the "standard name" property in CRM to the "ID" property of the time zone. Are you sure they are going to match? On `TimeZoneInfo`, the `StandardName` and `DaylightName` are language specific to the OS (not the thread culture or UI culture). Only the `Id` is safe to match on. Also, matching by the UTC offset bias is not a good suggestion, since there are *many* time zones that share the same offset. — Matt Johnson-Pint, Jun 13 '13 at 16:27
Indeed, we realised that Bias is suboptimal too. As our tool will require only five possible regions, we're opting for hard coding the values. But for future reference we would like to follow up our original question anyway. — Boris Callens, Jun 14 '13 at 07:51
Thanks for verifying this. So conclusion: standard name is the only way to go and should be reliable — Boris Callens, Jun 17 '13 at 08:50

score 4 · Answer 2 · answered Jun 19 '13 at 08:52

In case you should need a list of all CRM timezones and their relative SQL identifier: We wrote a little program to loop over all the options in CRM and output the name, semicolon, sql code.

(GMT+10:00) Canberra, Melbourne, Sydney (Commonwealth Games 2006); 256 
(GMT-04:00) Georgetown, La Paz, Manaus, San Juan; 55 
(GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London; 85 
(GMT+06:00) Ekaterinburg; 180 
(GMT+03:00) Baghdad; 158 
(GMT+06:00) Dhaka; 196 
(GMT-06:00) Guadalajara, Mexico City, Monterrey; 29 
(GMT+04:00) Abu Dhabi, Muscat; 165 
(GMT+02:00) Beirut; 131 
(GMT) Coordinated Universal Time; 92 
(GMT+10:00) Canberra, Melbourne, Sydney; 255 
(GMT+10:00) Brisbane; 260 
(GMT-01:00) Cape Verde Is.; 83 
(GMT+01:00) Brussels, Copenhagen, Madrid, Paris; 105 
(GMT+12:00) Auckland, Wellington; 290 
(GMT+2:00) Nicosia; 115 
(GMT+02:00) Athens, Bucharest; 130 
(GMT+13:00) Nukualofa; 300 
(GMT+10:00) Hobart; 265 
(GMT+04:00) Port Louis; 172 
(GMT+07:00) Novosibirsk; 201 
(GMT-03:30) Newfoundland; 60 
(GMT+03:30) Tehran; 160 
(GMT+08:00) Beijing, Chongqing, Hong Kong, Urumqi; 210 
(GMT+02:00) Amman; 129 
(GMT-06:00) Central America; 33 
(GMT+08:00) Krasnoyarsk; 207 
(GMT-11:00) Coordinated Universal Time-11; 6 
(GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb; 100 
(GMT-05:00) Bogota, Lima, Quito; 45 
(GMT+10:00) Yakutsk; 240 
(GMT+04:00) Yerevan; 170 
(GMT+09:00) Osaka, Sapporo, Tokyo; 235 
(GMT+10:00) Guam, Port Moresby; 275 
(GMT+12:00) Fiji; 285 
(GMT+04:30) Kabul; 175 
(GMT-05:00) Eastern Time (US & Canada); 35 
(GMT+11:00) Vladivostok; 270 
(GMT-01:00) Azores; 80 
(GMT+02:00) Jerusalem; 135 
(GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague; 95 
(GMT+07:00) Bangkok, Hanoi, Jakarta; 205 
(GMT-03:00) Buenos Aires; 69 
(GMT+09:00) Irkutsk; 227 
(GMT+12:00) Magadan; 281 
(GMT-03:00) Cayenne, Fortaleza; 70 
(GMT-07:00) Chihuahua, La Paz, Mazatlan; 12 
(GMT+02:00) Cairo; 120 
(GMT-08:00) Baja California; 5 
(GMT+12:00) Petropavlovsk-Kamchatsky - Old; 295 
(GMT+05:00) Tashkent; 185 
(GMT+03:00) Nairobi; 155 
(GMT-04:00) Atlantic Time (Canada); 50 
(GMT-12:00) International Date Line West; 0 
(GMT+03:00) Kaliningrad, Minsk; 159 
(GMT-02:00) Coordinated Universal Time-02; 76 
(GMT+02:00) Istanbul; 134 
(GMT+08:00) Perth; 225 
(GMT+13:00) Samoa; 1 
(GMT) Casablanca; 84 
(GMT+06:00) Astana; 195 
(GMT+09:30) Darwin; 245 
(GMT-04:30) Caracas; 47 
(GMT-10:00) Hawaii; 2 
(GMT+09:00) Seoul; 230 
(GMT+08:00) Kuala Lumpur, Singapore; 215 
(GMT-06:00) Guadalajara, Mexico City, Monterrey - Old; 30 
(GMT+06:30) Yangon (Rangoon); 203 
(GMT+04:00) Moscow, St. Petersburg, Volgograd; 145 
(GMT+02:00) Damascus; 133 
(GMT-07:00) Arizona; 15 
(GMT+04:00) Tbilisi; 173 
(GMT-09:00) Alaska; 3 
(GMT-03:00) Brasilia; 65 
(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna; 110 
(GMT-07:00) Chihuahua, La Paz, Mazatlan - Old; 13 
(GMT+08:00) Ulaanbaatar; 228 
(GMT-03:00) Greenland; 73 
(GMT+09:30) Adelaide; 250 
(GMT-02:00) Mid-Atlantic; 75 
(GMT+12:00) Coordinated Universal Time+12; 284 
(GMT+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius; 125 
(GMT+02:00) Harare, Pretoria; 140 
(GMT-05:00) Indiana (East); 40 
(GMT+01:00) West Central Africa; 113 
(GMT-08:00) Pacific Time (US & Canada); 4 
(GMT+05:00) Islamabad, Karachi; 184 
(GMT+11:00) Solomon Is., New Caledonia; 280 
(GMT+03:00) Kuwait, Riyadh; 150 
(GMT+08:00) Taipei; 220 
(GMT-03:00) Montevideo; 74 
(GMT+09:30) Adelaide (Commonwealth Games 2006); 251 
(GMT+10:00) Hobart (Commonwealth Games 2006); 266 
(GMT+05:30) Sri Jayawardenepura; 200 
(GMT+04:00) Baku; 169 
(GMT+05:30) Chennai, Kolkata, Mumbai, New Delhi; 190 
(GMT-06:00) Central Time (US & Canada); 20 
(GMT-07:00) Mountain Time (US & Canada); 10 
(GMT-06:00) Saskatchewan; 25 
(GMT-04:00) Cuiaba; 58 
(GMT-04:00) Asuncion; 59 
(GMT+01:00) Windhoek; 141 
(GMT-04:00) Santiago; 56 
(GMT+05:45) Kathmandu; 193 
Bahia Standard Time; 71 
(GMT) Monrovia, Reykjavik; 90

PotatoFarmer · Answer 3 · 2020-06-18T00:23:48.670

TimeZones: Using Dynamics CRM Web API:

EDIT: Dynamics CRM uses Windows Time Zone names. Javascript is almost certainly going to use IANA. Adding a Windows->IANA mapping library (e.g. windows-iana) will likely make this output much more useful.

//Get available timezone codes, as array
const { findOneIana } = require("windows-iana");

await axios.get(`${crmAPIUrl}timezonedefinitions?$select=timezonecode,standardname,userinterfacename,bias`)
.then(r => r.value)
.then(arr => arr.map(i => (
  {
    timezonecode: i.timezonecode,
    userinterfacename: i.userinterfacename,
    standardname: i.standardname,
    ianaTZ: findOneIana(i.standardname)
)))

This will return an array of time zones as Dynamics CRM sees them, complete with matching IANA values. The ianaTZ value can be used in rest of your JS code.

[
  ...
  {
    timezonecode: 145,
    userinterfacename: '(GMT+03:00) Moscow, St. Petersburg',
    standardname: 'Russian Standard Time',
    ianaTZ: 'Europe/Moscow'
  },
  {
    timezonecode: 133,
    userinterfacename: '(GMT+02:00) Damascus',
    standardname: 'Syria Standard Time',
    ianaTZ: 'Asia/Damascus'
  },
  ...
]

TimeZoneCode to TimeZoneInfo

3 Answers3

Edit - Bias is null

TimeZones: Using Dynamics CRM Web API:

Linked