Dataverse - Convert UTC Time to User Timezone in Plugin

Today, I'll share a quick tip on converting the UTC date to the User's Timezone settings in the Plugin (once you read the logic, you can also implement this logic into Power Automate/Custom API). In Dataverse, we can set our timezone in the below UI (go to Settings > Personalized Settings > on General Tab, you can select "Set the time zone you are in"):

Set the user's time zone

This information will be recorded on the usersettings table which can be extracted using the below query:

Query to get standardname TimeZone in SQL4CDS

Based on that, I can create the below code:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Extensions;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Linq;

namespace BlogPackage
{
    public class PreCreateParent : PluginBase
    {
        public PreCreateParent() : base(typeof(PreCreateParent))
        {
        }

        protected override void ExecuteDataversePlugin(ILocalPluginContext localPluginContext)
        {
            var target = localPluginContext.PluginExecutionContext.InputParameterOrDefault<Entity>("Target");

            var now = localPluginContext.PluginUserService.ConvertToUserTimeZone(localPluginContext.PluginExecutionContext.InitiatingUserId, DateTime.UtcNow);
            target["tmy_number"] = $"PreCreateParent {now:dd-MM-yyyy HH:mm:ss tt}";
        }
    }

    public static class OrganizationServiceExtensions
    {
        public static string GetUserTimeZone(this IOrganizationService service, Guid userId)
        {
            var query = new QueryExpression("timezonedefinition")
            {
                ColumnSet = new ColumnSet("standardname"),
                NoLock = false,
                TopCount = 1
            };
            var userSettingLink = query.AddLink("usersettings", "timezonecode", "timezonecode");
            userSettingLink.Columns = new ColumnSet(false);
            userSettingLink.LinkCriteria.AddCondition("systemuserid", ConditionOperator.Equal, userId);

            var result = service.RetrieveMultiple(query);

            return result.Entities.FirstOrDefault()?.GetAttributeValue<string>("standardname");
        }

        public static DateTime ConvertToUserTimeZone(this IOrganizationService service, Guid userId, DateTime utc)
        {
            if (utc.Kind != DateTimeKind.Utc) throw new InvalidPluginExecutionException("DateTime must be in UTC!");
            var userTimeZone = TimeZoneInfo.FindSystemTimeZoneById(service.GetUserTimeZone(userId));
            if (userTimeZone.StandardName == "UTC") return utc;

            return TimeZoneInfo.ConvertTimeFromUtc(utc, userTimeZone);
        }
    }
}

In the above code, you can focus on the extension method of GetUserTimeZone function which is responsible for retrieving the "timezonedefinition.standardname" value based on systemuserid that we passed.

Next, you can see the ConvertToUserTimeZone which will use the "timezonedefinition.standardname" value and convert the TimeZone from UTC to the targeted time zone using TimeZoneInfo.ConvertTimeFromUtc method.

At last, for today's demo, I use the OrganizationServiceExtensions.ConvertToUserTimeZone to set the Primary Field of the table that I created from UI (I already registered the plugin in Create of the record).

Here is the result:

Demo result

FYI, GetUserTimeZone and ConvertToUserTimeZone are called extension methods. For me, it has benefits to make our code cleaner and reusable (we can call the extension method like in row #19).

Updated

I forgot that CRM already has its own Message to convert to UTC to Local Time via LocalTimeFromUtcTimeRequest andLocal Time to UTC via UtcTimeFromLocalTimeRequest (Thanks Betim!). Betim also feedback that he also has a solution which makes me add the below benchmark:

using BenchmarkDotNet.Attributes;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Xrm.Sdk;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;

namespace DataverseClient
{
    [MemoryDiagnoser]
    [Orderer(BenchmarkDotNet.Order.SummaryOrderPolicy.FastestToSlowest)]
    [SimpleJob(launchCount: 1, warmupCount: 0)]
    public class ConvertToUtcTest
    {
        private readonly Guid _userId = new Guid("F9E7905C-CF7A-EE11-8179-002248201668");
        private readonly IOrganizationService _service;

        public ConvertToUtcTest()
        {
            _service = GetService();
        }

        public IOrganizationService GetService()
        {
            var builder = Helper.CreateHostBuilder().Build();
            var serviceProvider = builder.Services;

            return serviceProvider.GetRequiredService<ServiceClient>();
        }

        [Benchmark]
        public void TemmysLocalTime()
        {
            var localTime = _service.ConvertToUserTimeZone(_userId, DateTime.UtcNow);
            Console.WriteLine(localTime);
        }

        [Benchmark]
        public void BetimsLocalTime()
        {
            var userTimeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneDefinitionQueries.GetTimezoneByUserId(_userId, _service));
            var localTime = userTimeZone.ConvertToLocal(DateTime.UtcNow);
            Console.WriteLine(localTime);
        }

        [Benchmark]
        public void LocalTimeFromUtcTimeRequest()
        {
            var now = DateTime.UtcNow;
            // Query the TimeZoneCode
            var timeZoneCode = GetTimeZoneCode(_userId);
            var req = new LocalTimeFromUtcTimeRequest
            {
                UtcTime = now,
                TimeZoneCode = timeZoneCode
            };
            var result = (LocalTimeFromUtcTimeResponse)_service.Execute(req);
            Console.WriteLine(result.LocalTime.ToString());
        }

        private int GetTimeZoneCode(Guid userId)
        {
            var query = new QueryExpression("timezonedefinition")
            {
                NoLock = true,
                TopCount = 1,
                ColumnSet = new ColumnSet("timezonecode")
            };
            query.AddLink("usersettings", "timezonecode", "timezonecode")
                 .LinkCriteria
                 .AddCondition("systemuserid", ConditionOperator.Equal, userId);
            var result = _service.RetrieveMultiple(query).Entities.FirstOrDefault();
            return result?.GetAttributeValue<int?>("timezonecode").GetValueOrDefault() ?? 0;
        }
    }

    #region Temmy's Solution
    public static class OrganizationServiceExtensions
    {
        public static string GetUserTimeZone(this IOrganizationService service, Guid userId)
        {
            var query = new QueryExpression("timezonedefinition")
            {
                ColumnSet = new ColumnSet("standardname"),
                NoLock = false,
                TopCount = 1
            };
            var userSettingLink = query.AddLink("usersettings", "timezonecode", "timezonecode");
            userSettingLink.Columns = new ColumnSet(false);
            userSettingLink.LinkCriteria.AddCondition("systemuserid", ConditionOperator.Equal, userId);

            var result = service.RetrieveMultiple(query);

            return result.Entities.FirstOrDefault()?.GetAttributeValue<string>("standardname");
        }

        public static DateTime ConvertToUserTimeZone(this IOrganizationService service, Guid userId, DateTime utc)
        {
            if (utc.Kind != DateTimeKind.Utc) throw new InvalidPluginExecutionException("DateTime must be in UTC!");
            var userTimeZone = TimeZoneInfo.FindSystemTimeZoneById(service.GetUserTimeZone(userId));
            if (userTimeZone.StandardName == "UTC") return utc;

            return TimeZoneInfo.ConvertTimeFromUtc(utc, userTimeZone);
        }
    }
    #endregion

    #region Betim's Solution

    // https://github.com/albanian-xrm/AlbanianXrm-XrmExtensions/blob/main/AlbanianXrm.XrmExtensions.Source/Queries/TimeZoneDefinitionQueries.cs
    public static class TimeZoneDefinitionQueries
    {
        public const string USER_DOES_NOT_EXIST = "User with Id='{0}' does not exist";
        public static string GetTimezoneByUserId(Guid userId, IOrganizationService service)
        {
            var query = new QueryExpression("timezonedefinition")
            {
                NoLock = true,
                TopCount = 1,
                ColumnSet = new ColumnSet("standardname")
            };
            query.AddLink("usersettings", "timezonecode", "timezonecode")
                 .LinkCriteria
                 .AddCondition("systemuserid", ConditionOperator.Equal, userId);
            var result = service.RetrieveMultiple(query).Entities.FirstOrDefault();
            if (result == null)
            {
                throw new InvalidPluginExecutionException(string.Format(USER_DOES_NOT_EXIST, userId));
            }
            return result.GetAttributeValue<string>("standardname");
        }
    }

    // https://github.com/albanian-xrm/AlbanianXrm-XrmExtensions/blob/main/AlbanianXrm.XrmExtensions.Source/Extensions/TimeZoneInfoExtensions.cs
    public static class TimeZoneInfoExtensions
    {
        public static DateTime ConvertToUTC(this TimeZoneInfo timeZoneInfo, DateTime localDateTime)
        {
            var adjustment = timeZoneInfo.GetAdjustmentRule(localDateTime);
            if (adjustment == null)
            {
                return DateTime.SpecifyKind(localDateTime.Add(-timeZoneInfo.BaseUtcOffset), DateTimeKind.Utc);
            }
            var timeDifference = timeZoneInfo.BaseUtcOffset;
            if (timeZoneInfo.SupportsDaylightSavingTime && timeZoneInfo.IsDaylightSavingTime(localDateTime))
            {
                timeDifference = timeDifference.Add(adjustment.DaylightDelta);
            }
            return DateTime.SpecifyKind(localDateTime.Add(-timeDifference), DateTimeKind.Utc);
        }

        public static DateTime ConvertToLocal(this TimeZoneInfo timeZoneInfo, DateTime utcDateTime)
        {
            var adjustment = timeZoneInfo.GetAdjustmentRule(utcDateTime);
            if (adjustment == null)
            {
                return DateTime.SpecifyKind(utcDateTime.Add(timeZoneInfo.BaseUtcOffset), DateTimeKind.Unspecified);
            }
            var timeDifference = timeZoneInfo.BaseUtcOffset;
            if (timeZoneInfo.SupportsDaylightSavingTime && timeZoneInfo.IsDaylightSavingTime(utcDateTime))
            {
                timeDifference = timeDifference.Add(adjustment.DaylightDelta);
            }
            return DateTime.SpecifyKind(utcDateTime.Add(timeDifference), DateTimeKind.Unspecified);
        }

        public static DateTime GetDaylightTransitionAsDate(this TimeZoneInfo.TransitionTime transition, DateTime date)
        {
            if (transition.IsFixedDateRule)
            {
                return new DateTime(date.Year, transition.Month, transition.Day);
            }
            else
            {
                var transitionMonth1st = new DateTime(date.Year, transition.Month, 1);

                var daysToDayOfWeek = transition.DayOfWeek - transitionMonth1st.DayOfWeek;
                var weeks = daysToDayOfWeek >= 0 ? transition.Week - 1 : transition.Week;
                var transitionMonthDay = transitionMonth1st.AddDays(daysToDayOfWeek);
                if (transitionMonthDay.AddDays(7 * weeks).Month != transition.Month)
                {
                    weeks -= 1;
                }
                return transitionMonthDay.AddDays(7 * weeks);
            }
        }

        public static TimeZoneInfo.AdjustmentRule GetAdjustmentRule(this TimeZoneInfo localTimezone, DateTime date)
        {
            var adjustments = localTimezone.GetAdjustmentRules();
            // Iterate adjustment rules for time zone
            foreach (TimeZoneInfo.AdjustmentRule adjustment in adjustments)
            {
                // Determine if this adjustment rule covers year desired
                if (adjustment.DateStart <= date && adjustment.DateEnd >= date)
                    return adjustment;
            }
            return null;
        }
    }
    #endregion
}

As you can see, we compared three solutions. Mine, Betim's, and also using LocalTimeFromUtcTimeRequest. And, here is the result:

Betim's solution is the winner🏆

You can check Betim's solution here https://github.com/albanian-xrm/AlbanianXrm-XrmExtensions/blob/main/AlbanianXrm.XrmExtensions.Source/Extensions/TimeZoneInfoExtensions.cs.

Hope you are learning something and happy CRM-ing!