Posted on:
Categories: SharePoint
Description:

​I am working on a project that requires me to pass the start and end date of a SharePoint calendar event to an external web service. This integration takes place in a remote event receiver and because of data integrity, it happens in the before events, namely ItemAdding, ItemUpdating and ItemDeleting.

In before events, item field values are available in the AfterProperties collection. So to get the event start date, we would use AfterProperties[“EventDate”] and for the end date, we would use AfterProperties[“EndDate”]. Here comes the first challenge. If you do a ToString to either element, you get something like “2015-08-31T17:00:00Z”. Don’t be fooled by the format (ends with a Z). The time will always be equal to the time the user selects in the new or edit event page but the time zone is not UTC.

Before we do anything, we need to convert the field to a DateTime object. We cannot use DateTime.Parse because the method will treat the time as UTC time to begin with. We use DateTimeOffset.Parse instead. 

var eventDate = DateTimeOffset.Parse(itemEventProperties.AfterProperties[“EventDate”].ToString()).DateTime

DateTimeOffset represents instantaneous time, i.e. when we do a parse, it will ignore the seemingly UTC time format. All that’s left is finding what time zone the 5pm is in.

I don’t know what goes behind the scene but SharePoint determines time zone in the following order. First it will use the time zone set in your user profile, editable through the About Me link below your user name on the top right of all SharePoint pages. If you choose to use regional settings defined by site administrators, SharePoint will use the web’s regional settings (Site Settings > Site Administration > Regional Settings > Time Zone).

In CSOM, we can get user profile properties from the PeopleManager object or through the REST API SP.UserProfiles.PeopleManager. There are two properties that are of relevance here, SPS-RegionalSettings-FollowWeb and SPS-TimeZone. The former is set to true if the user follows the web’s regional settings while the latter stores the description of a SharePoint time zone set by the user, e.g. “(UTC-08:00) Pacific Time (US and Canada)”. Once a time zone is set, SPS-TimeZone will retain the value even if the user switches back to follow the web's regional settings.

Although you can extract UTC offset from the time zone description, you need to include daylight saving time in your calculation. The proper way is to convert that to a time zone object, specifically the TimeZoneInfo object. The web service I am calling also requires the TimeZoneInfo object when you pass in a date/time.

The TimeZoneInfo object has a method called GetSystemTimeZones. To list out all the system time zones, run the following in a console program: 

foreach (var timeZoneInfo in TimeZoneInfo.GetSystemTimeZones()) {
 Console.WriteLine(timeZoneInfo.DisplayName);
}

The DisplayName property of a TimeZoneInfo object returns a similar description so we should be able to get a TimeZoneInfo object by looping through all the system time zones and matching the DisplayName with what returns from SharePoint user profile. Well, not so quickly. SharePoint uses its own time zone object that has a slightly different set of descriptions. You can list out all the SharePoint time zones in a console program using 

var spTimeZones = SPRegionalSettings.GlobalTimeZones
foreach (var spTimeZone in spTimeZones)
{
 Console.WriteLine(spTimeZone.Description);
}

If you compare the two sets of descriptions, the majority of them matches, assuming you have the latest system and SharePoint updates. There are some differences and they are shown below:

SharePoint Time Zone ​System Time Zone
​(UTC-08:00) Pacific Time (US and Canada) ​(UTC-08:00) Pacific Time (US & Canada)
​(UTC-07:00) Mountain Time (US and Canada) ​(UTC-07:00) Mountain Time (US & Canada)
​(UTC-06:00) Central Time (US and Canada) ​(UTC-06:00) Central Time (US & Canada)
​(UTC-05:00) Eastern Time (US and Canada) ​(UTC-05:00) Eastern Time (US & Canada)
​(UTC-05:00) Bogota, Lima, Quito ​(UTC-05:00) Bogota, Lima, Quito, Rio Branco
​(UTC-02:00) Mid-Atlantic ​(UTC-02:00) Mid-Atlantic - Old
​(UTC-01:00) Cape Verde Is. ​(UTC-01:00) Cabo Verde Is.
​(UTC+02:00) Athens, Bucharest, Istanbul ​(UTC+02:00) Athens, Bucharest
​(UTC+05:00) Tashkent ​(UTC+05:00) Ashgabat, Tashkent


We could build an internal mapping table which I would advise against or perform several rounds of matching. To begin, get the user profile time zone property and replace “ and “ with “ & “ and use that as a base for matching. The first round of matching will be an exact match, follows by a “contains” match if no matches are found. The “contains” match is two way, i.e. test if SharePoint’s time zone description contains system’s time zone description and vice versa. If there are still no matches, remove the offset component in both description and do a two-way “contains” match again.

Changes to time zone and daylight saving information are deployed through system and SharePoint updates. This last round of matching is necessary to account for the fact that they may not be in sync or you may not have latest updates of one or the other deployed in your environment. That still leaves (UTC-01:00) Cape Verde Is. You could add this time zone in your matching logic or ignore it you know for sure you don’t have users in that time zone.

As mentioned before, if the user chooses to use regional settings defined by site administrators, SharePoint will fall back to the web’s regional settings. The following CSOM codes will return the time zone description of the web’s regional settings and you can pass that through the same matching logic to retrieve a TimeZoneInfo object. 

var regionalSettings = context.Web.RegionalSettings;
context.Load(regionalSettings.TimeZone);
context.ExecuteQuery();
spTimeZoneDescription = regionalSettings.TimeZone.Description;

[Updated 2015-09-15] Microsoft has confirmed the issue with dates in AfterProperties not showing in UTC time for calendar lists which are what I used here. There are no issue with custom lists. Hopefully there will be a hotfix to address this.