Musings and Solutions for SharePoint, O365, and Azure

2010 2013 2016 Library List REST SharePoint Area SharePoint Online SharePoint Version

SharePoint time, is not your time, is not their time.

April 17, 2017

SharePoint time, is not your time, is not their time.

If you develop client side solutions for SharePoint you’ve either run into this or you will run into the following scenario. SharePoint stores all its date/time fields in UTC time. The site collections, sites, and the users, can have their own time zone settings. If you’re using SharePoint out of the box because all the content is rendered on the server and pushed to the client with all the date/time translation has been done for you. This makes wonderful sense, except when you try and write JavaScript against those same data points. The REST endpoints that return the data for you give you the date string in a format that is specific to the regional settings of the person asking for them. Sadly, this doesn’t translate as well to JavaScript as you might like. I’ve set up a scenario to illustrate the point with a couple of manipulations you can make depending on your desired goals.

Scenario

I have two PC’s (ok, one is virtual 😊). I set my virtual machine’s time zone to Pacific Daylight Time (PDT) and my main machine is set to Eastern Daylight Time (EDT). Then I have a SharePoint site collection whose regional settings are set for Eastern Time (or UTC-5:00 aka EDT). I created a list with a title field, and two date fields one to show date/time and one to show just date. The date only field was to illustrate that the problem exists regardless of whether the user intentionally sets the time or not. I created an item in the list from my computer set to Eastern time… Then I went to my computer set to Pacific time and created a second item. I set the dates and times for both items the same from their respective UIs. Again, this is to illustrate that the local time of the computer has no bearing on what SharePoint sees the date/time as. Regardless of whom entered the item the dates are displayed based on the regional settings effective on the site.

I’ve written some code that I’m going to expose using a CEWP… the code does the following things:

  1. Read the regional settings of the site.
  2. Gets all the items in my SPDateTime list and loops through them, for each item…
  3. Get the Items Date field, create a JavaScript date object, display the date object and the string that was used to create it.
  4. Get the Items Date No Time field, create a JavaScript date object, display the date object and the string that was used to create it.
  5. Adjust the Items Date field into the time zone of the regional settings in effect on the server and display it.
  6. Adjust the Items Date field into UTC time and display it.

Ok, so let’s start with the computer in EDT and take a look at what our client side code does:

What you’re probably noticing right away is that everything looks great. It’s just what you’d expect. So, what’s the problem… well… if you’re developing client side code and all the time zone settings for all of your users and their computers are going to be in the same time zone… absolutely no problem at all.

The tricky part begins when we look at the computer where the time zone of the computer is set to PDT.

Ok, so what happened here is that when the date strings were passed into JavaScripts Date() function, the browser is actually then converting that date into the local time of the computer. So 4/15/2017 12:00 am becomes 4/14/2017 9:00 pm (3 hours earlier). Again, this makes perfect sense, but if you want the user to experience dates independent of time zone, you’re in trouble. This can often happen if you’re building SharePoint “applications” date/times as fixed points in time that will be used as comparators.

Ok, so let’s look at a couple of workarounds and depending on your scenario you’ll have to decide if either of them work for you. I’m not going to go into how those regional/personal settings work but I will provide you a link to where Gregory Zelfond, gives a nice explanation: Setting proper SharePoint Time Zone for users.

Adjust date to time zone of “server”

The first manipulation I made was to adjust the date field to the time zone of the “server”, when I say server I mean whatever regional setting is in effect for that “page”. I personally can’t come up with a ton of scenarios where this is useful with the exception of making comparisons. In our PDT example which changes 4/15/2017 12:00 am to 4/15/2017 3:00am, which would be midnight PDT. I readily admit this is an odd scenario but you may need it (I actually have).

Adjust to UTC time zone

The second, which I think is entirely more useful, is converting to UTC time which basically means were going to ignore the time zone entirely. So, for our scenario this means 4/15/2017 12:00am shows up as 4/15/2017 12:00am.

The Code

For this solution, we’re going to need to make two REST calls the first will be to get the regional time zone of the web we’re working in. To do that you need to make a GET request to:

/_api/Web/RegionalSettings/TimeZone

The response for this call is the following, where we will use the Bias, and DaylightBias to calculate the region the server is operating in so we can mimic the values the server displays:

The second is to get all the items in our test list. Below is the code to generate the various date/time values I outlined above. Keep in mind, this is only a small code snippet from inside the loop that is traversing the items returned from out afore mentioned list.
*Assume that data is an array of responses

For the full code sample, you can go to my github repo and look in the SPDateTime folder.

For completeness sake, I should mention that if you’re going to be doing a lot of date/time manipulation it might make sense to utilize the moment.js library which makes a bunch of this stuff significantly simpler. I tend to be a minimalist when it comes to libraries, only using one when I have use for it. But if it makes you more efficient by all means don’t be a martyr and reinvent the wheel.

Hope this can help a few people out there struggling with date/times in SharePoint client side solutions.

Happy Coding.


    Julie has been building software on primarily the Microsoft platform for over 20 years. With a degree in Electrical Engineering specializing in microprocessor system design from Worcester Polytechnic Institute she came at software initially from a very low level but quickly realized a love and aptitude for developing rich user experiences and solutions. She worked her way up through internal IT ranks reaching a Director of IT position before realizing more impact could be made in consulting and really focused in on the SharePoint platform in 2007. Since then her focus has been on the SharePoint platform, Office 365, Azure, and client side development. She’s the co-author of the Widget Wrangler JavaScript library and continues to try and help innovate SharePoint and Office 365 solutions for her clients. She is a 2017 recipient of the Microsoft MVP award for Office Servers and Services.


    10 comments
    1. Hi Julie. Thank you for the post - very helpful. Got a question on an issue that has again come back to bit me. :) I keep reading that SharePoint stores all dates in UTC - I understand that part and its great that it does that. but do you know what client-side solutions are suppose to provide on an update (ex. UpdateListItem)? I'm finding that when I send a date on the update that is already a UTC date (ex. 2017-10-30T23:00:00.000Z), sharepoint seems to still apply a conversion to this date, and thus double store it incorrectly... My assumption is that, like your solution above for display, I probably need to get the Web Regional timezone and then send the date in on the update in the timezone of Web... Any help you can provide would be awesome. Thanks.

      • Hi Paul, Thanks! Glad it was helpful. Your question is a really good one, and the quick answer is yes, assuming you've got an actual javascript date object... if you've just got a string from an input field you can just push it up there. It was a good enough question that I took the liberty to write an additional post on the topic, SharePoint REST Date/Time Updates

        • Thanks Julie... I saw you released a new post - I will read it tonight :) FYI... I found another trick... If you are still using SOAP operations to update an item (shame on me - I am), then you can add the following to the element of your update: `DateInUtc="True"`... This seems to tell SharePoint that the date I'm passing in is already in UTC and in turn it will not attempt to convert it again. In the mist of investigating the issue I had in my hands this past weekend (that lead to my question) and after reading your post, I also found out that my Queries to lists have a similar issue with dates... I always use UTC time, thinking that SP sees the "Z" is knows it must be a UTC date, but that is not the case as you know... for that one, there seems to be a CAML attribute available called `StorageTZ` which when used in combination with `IncludeTimeValue` will also allow you to send in UTC ISO8601 dates... Example: 2017-11-30T21:30:46Z Not sure if these two other options I found are applicable to the new REST APIs. That will be something I look into in the near future. Thanks again for the response.

    Leave a comment

    Leave a Reply

    %d bloggers like this: