Dataverse: Avoiding Timezone Pitfalls When Integrating Dataverse Date and DateTime Fields

New day, new things to learn. While integrating with Dataverse may seem straightforward at first, especially when it comes to DateTime things, it can quickly become complex due to different configuration settings. In this post, I’ll walk through the key factors that cause these variations, so you can confidently choose the right settings before building your integration.

I created simple code and created several records with the following variations:

From the table above, I set several settings:

• The DateTime value to 7-Sept-1990 15:30:00 PM
• Set the DateTimeKind (Local/Unspecified/UTC)
• App: The Application User's Timezone (On the above setting, I only tested 2 scenarios: GMT and GMT+8)
• Server: The Server's Timezone (2 scenarios: GMT+8 and GMT+7)
• ToUTC: Before sending to Dataverse, let the system call *.ToUniversalTime() method.

Next, you can view the result in the Dataverse (DateOnly (UTC), DateTime TimeZone Independent (UTC), DateTime User Local (UTC)), which returns all values in UTC Format.

How system behave by default

Based on my understanding:

• Avoid sending DateTime in DateTimeKind.Local as it leads to double calculation, which leads to complications.
• For DateTimeKind.Unspecified, means SDK will automatically convert to the server's Timezone (in my case, deduct 7/8 hours based on my setting).
• For DateTimeKind.UTC, means SDK will keep the value as is. Dataverse is also receiving it without changing the value. Usually, when we read/receive DateTime, by default, the value of the DateTime will be set to DateTimeKind.Unspecified. So, technically, when we call *.ToUniversalTime(), the value will be transformed to the Server's Timezone (in my case, it will -7/-8 depends on the Timezone I chose on my computer). This will be the safest option for you to use during integration (to always call *.ToUniversalTime()).

And when we want to retrieve the data from the SDK, Dataverse will return the DateTime in the UTC format. Hence, I added a snippet of code for standardization:

using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.PowerPlatform.Dataverse.Client.Extensions;

namespace DataverseBenchmarkProject
{
    public class TimezoneOrganizationService : IOrganizationService
    {
        private ServiceClient _service;
        private static Dictionary<string, RetrieveEntityResponse> _entityMetadata = new Dictionary<string, RetrieveEntityResponse>();
        private static Dictionary<Guid, TimeZoneInfo> _timeZoneUsers = new Dictionary<Guid, TimeZoneInfo>();

        public Guid UserId { get; set; }

        public TimezoneOrganizationService(ServiceClient service)
        {
            _service = service;
        }

        public void Associate(string entityName, Guid entityId, Relationship relationship, EntityReferenceCollection relatedEntities)
        {
            _service.Associate(entityName, entityId, relationship, relatedEntities);
        }

        public Guid Create(Entity entity)
        {
            return _service.Create(ConvertToUtc(entity));
        }

        private TimeZoneInfo GetUserTimeZone(Guid userId)
        {
            if (_timeZoneUsers.ContainsKey(userId))
            {
                return _timeZoneUsers[userId];
            }
            else
            {
                var timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneDefinitionQueries.GetTimezoneByUserId(userId, _service));
                _timeZoneUsers[userId] = timeZoneInfo;
                return timeZoneInfo;
            }
        }

        private RetrieveEntityResponse GetEntityMetadata(string entityName)
        {
            if (_entityMetadata.ContainsKey(entityName))
            {
                return _entityMetadata[entityName];
            }
            else
            {
                var request = new RetrieveEntityRequest
                {
                    EntityFilters = EntityFilters.Attributes,
                    LogicalName = entityName
                };
                var response = (RetrieveEntityResponse)_service.Execute(request);
                _entityMetadata[entityName] = response;
                return response;
            }
        }

        public void Delete(string entityName, Guid id)
        {
            _service.Delete(entityName, id);
        }

        public void Disassociate(string entityName, Guid entityId, Relationship relationship, EntityReferenceCollection relatedEntities)
        {
            _service.Disassociate(entityName, entityId, relationship, relatedEntities);
        }

        public OrganizationResponse Execute(OrganizationRequest request)
        {
            if (request is CreateRequest createRequest)
            {
                createRequest.Target = ConvertToUtc(createRequest.Target);
            }
            else if (request is UpdateRequest updateRequest)
            {
                updateRequest.Target = ConvertToUtc(updateRequest.Target);
            }

            return _service.Execute(request);
        }

        public Entity ConvertToLocal(Entity entity)
        {
            var timeZone = GetUserTimeZone(_service.GetMyUserId());
            if (timeZone == TimeZoneInfo.Utc) return entity;

            var dateTimeUserLocalAttributes = GetEntityMetadata(entity.LogicalName)?.EntityMetadata?.Attributes?
                .Where(e => e is DateTimeAttributeMetadata dateTimeAttributeMetadata && dateTimeAttributeMetadata.DateTimeBehavior == DateTimeBehavior.UserLocal).ToArray();
            foreach (var attribute in entity.Attributes)
            {
                if (attribute.Value is DateTime dateTimeValue)
                {
                    var currentDateTimeAttributeMetadata = dateTimeUserLocalAttributes?.FirstOrDefault(a => a.LogicalName == attribute.Key);
                    if (currentDateTimeAttributeMetadata == null) continue;

                    var localTime = timeZone.ConvertToLocal(dateTimeValue);
                    entity[attribute.Key] = localTime;
                }
                else if (attribute.Value is AliasedValue aliasedValue && aliasedValue.Value is DateTime dateTimeValueAliased)
                {
                    var aliasedEntityMetadata = GetEntityMetadata(aliasedValue.EntityLogicalName)?.EntityMetadata?.Attributes?
                        .Where(e => e is DateTimeAttributeMetadata dateTimeAttributeMetadata && dateTimeAttributeMetadata.DateTimeBehavior == DateTimeBehavior.UserLocal).ToArray();
                    var currentDateTimeAttributeMetadata = dateTimeUserLocalAttributes?.FirstOrDefault(a => a.LogicalName == aliasedValue.AttributeLogicalName);
                    if (currentDateTimeAttributeMetadata == null) continue;

                    entity[attribute.Key] = new AliasedValue(aliasedValue.EntityLogicalName, aliasedValue.AttributeLogicalName, timeZone.ConvertToLocal(dateTimeValueAliased));
                }
            }
            return entity;
        }

        public Entity ConvertToUtc(Entity entity)
        {
            var timeZone = GetUserTimeZone(_service.GetMyUserId());

            var dateTimeUserLocalAttributes = GetEntityMetadata(entity.LogicalName)?.EntityMetadata?.Attributes?
                .Where(e => e is DateTimeAttributeMetadata dateTimeAttributeMetadata).Select(e => (DateTimeAttributeMetadata)e).ToArray();
            foreach (var attribute in entity.Attributes)
            {
                if (attribute.Value is DateTime dateTimeValue)
                {
                    var currentDateTimeAttributeMetadata = dateTimeUserLocalAttributes?.FirstOrDefault(a => a.LogicalName == attribute.Key);
                    if (currentDateTimeAttributeMetadata == null) continue;

                    var correctedDateTimeValue = currentDateTimeAttributeMetadata.DateTimeBehavior == DateTimeBehavior.DateOnly || currentDateTimeAttributeMetadata.Format == DateTimeFormat.DateOnly ? timeZone.ConvertToUTC(dateTimeValue.Date) :
                        currentDateTimeAttributeMetadata.DateTimeBehavior == DateTimeBehavior.UserLocal ? timeZone.ConvertToUTC(dateTimeValue) : dateTimeValue;
                    entity[attribute.Key] = correctedDateTimeValue;
                }
            }
            return entity;
        }

        public Entity Retrieve(string entityName, Guid id, ColumnSet columnSet)
        {
            var entity = _service.Retrieve(entityName, id, columnSet);
            return ConvertToLocal(entity);
        }

        public EntityCollection RetrieveMultiple(QueryBase query)
        {
            var entityCollection = _service.RetrieveMultiple(query);
            for (var i = 0; i < entityCollection.Entities.Count; i++)
            {
                entityCollection.Entities[i] = ConvertToLocal(entityCollection.Entities[i]);
            }
            return entityCollection;
        }

        public void Update(Entity entity)
        {
            _service.Update(ConvertToUtc(entity));
        }
    }

    #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
}

Basically, we are implementing the Decorator Pattern in the class TimezoneOrganizationService. In this way, we can standardize the behavior of DateTime across the system without the need to change each of the implementations one by one. If you take a look at the above code, we are retrieving the DateTimeAttributeMetadata to change the default behavior of the DateTime attribute (on Create/Update/Retrieve/RetrieveMultiple).

Basically, here is the standardization that we enforce through the code:

Enforcing standardization through code

Here is a sample of how to call the TimezoneOrganizationService:

var connectionStrings = Startup.GetApplicationHost().Services.GetService<ConnectionStrings>()!;
var xrmConnection = new XrmConnection(connectionStrings);
var originalService = xrmConnection.GetServiceClient();
var service = new TimezoneOrganizationService(xrmConnection.GetServiceClient());

var data = new Entity("cr7c0_datebehaviortest");

data["cr7c0_testname"] = "1990, 9, 7, 15, 30, 0, DateTimeKind.Unspecified ToUTC";
data["cr7c0_dateonlysample"] = new DateTime(1990, 9, 7, 15, 30, 0, DateTimeKind.Unspecified);
data["cr7c0_dateandtimeindependent"] = new DateTime(1990, 9, 7, 15, 30, 0, DateTimeKind.Unspecified);
data["cr7c0_dateandtimeuserlocal"] = new DateTime(1990, 9, 7, 15, 30, 0, DateTimeKind.Unspecified);
var id = service.Create(data);
Console.WriteLine($"Record created with Id: {id}");

var retrieved = originalService.Retrieve("cr7c0_datebehaviortest", id, new ColumnSet(true));
Console.WriteLine($"DateOnly Value: {retrieved.GetAttributeValue<DateTime>("cr7c0_dateonlysample")}");
Console.WriteLine($"DateTimeIndependent Value: {retrieved.GetAttributeValue<DateTime>("cr7c0_dateandtimeindependent")}");
Console.WriteLine($"DateTime Local Value: {retrieved.GetAttributeValue<DateTime>("cr7c0_dateandtimeuserlocal")}");

Console.WriteLine("---------------Retrieving with wrapped Service Client-------------------");

var retrievedWrap = service.Retrieve("cr7c0_datebehaviortest", id, new ColumnSet(true));
Console.WriteLine($"DateOnly Value: {retrievedWrap.GetAttributeValue<DateTime>("cr7c0_dateonlysample")}");
Console.WriteLine($"DateTimeIndependent Value: {retrievedWrap.GetAttributeValue<DateTime>("cr7c0_dateandtimeindependent")}");
Console.WriteLine($"DateTime Local Value: {retrievedWrap.GetAttributeValue<DateTime>("cr7c0_dateandtimeuserlocal")}");

And here is the screenshot of the result:

Demo result