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?

Answer 1

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. .

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...