Timezone Issues when working with dates in SharePoint’s REST services

I was working on a SharePoint vacation planner  that would show employees down the left side of the page and Dates accross the top. The Sharepoint calendar does not provide such a format (as far as I know) so I decided to build it using javascript/jsRender and listdata.svc.  Users click on a cell in the Grid and it shows them a popup to enter info about there absence.

attendance

It worked great until a user  in  Asia accessed the calendar. All the dates were off by One day (i.e. a vacation day scheduled for July 5 would show up as july 4 when Viewed in Asia).

I queried my conrtent database with

SELECT *
FROM [AllUserData]
WITH(NOLOCK)
where [tp_ListId] =(
SELECT tp_ID
FROM [AllLists]
WITH(NOLOCK)
where [tp_WebId]  = (
SELECT  [Id] FROM [AllWebs] WITH(NOLOCK)where FullUrl = ‘HR/Attendance2’)
and tp_Title = ‘Absencses’)
and tp_DeleteTransactionId=0x

and found that the Vacation date was stored as 2013-07-05 04:00:00.000 when It was created from my custom Javascript UI but when I added a new vacation day via the Listforms it was stored as 2013-07-05 00:00:00.000.

As we know SharePoint stores all  its dates in UTC. So when I created a date in my Javascript code it was 2013-07-05 00:00:00 Eastern Time. When SharePoint got that date it converted it to UTC and hence I ended up with 2013-07-05 04:00:00.000 in my content database.

I created the following method to ‘Adjust’  my dates  before sending them to sharepoint so that they would be stored in the database  with the 00 hour

self.fromUtc = function (inputDate) {         debugger;         var localTime = inputDate.getTime();         var localOffset = inputDate.getTimezoneOffset() * 60000;         var utc = localTime – localOffset;         var retval = new Date(utc);         return retval;

};

Now when I created a new vacation date  from my custom Javascript UI it was properly stored  as 2013-07-05 00:00:00.000.

But I stll had issues. Although the dates were being stored properly in the database they were not appearing properly for my frien in Asia. All the days wer still off by one.  When the data above was sent to my browser via json ( it sends down the number of milliseconnds since Jan 1 1970 UTC)  javascript created the date Object in the users local Timezone (which was UTC -8:00). So my vacation day on 2013-07-05 00:00:00.000 was created in his browser as  2013-07-04 16:00:00.000  I created the following method to ‘Adjust’  my dates  after reading them from SharePoint so that they would be displayed ‘properly’ (i.e. as originally entered)

 self.toUtc = function (inputDate) {         var localTime = inputDate.getTime();         var localOffset=inputDate.getTimezoneOffset() * 60000;         var utc = localTime + localOffset;         var retval = new Date(utc);         debugger;         return retval;

};

Now my dates are stored and displayed as I expected.  It’s interesting to note that if SharePoint stored dates with  a time of 12 noon rather than  midnite, then when the timezone adjustment was made in javascript, the date would be correct still. This might make a nice custom Column. (Or maybe somebody has done it already?)

Also, I’m using datajs(http://datajs.codeplex.com/) to read the data from sharepoint. It has a setting called recognizeDates which automatically converts the dates in JSON. It would be nice if this feature were added to that library.

 

IMPORTANT NOTE: I learned after  deploying to production… the regional settings fro you site MUST be set to UTCC for this to work !!!

Advertisements
This entry was posted in javascript, sharepoint and tagged , , , , . Bookmark the permalink.

3 Responses to Timezone Issues when working with dates in SharePoint’s REST services

  1. sla12 says:

    Great!! Thanks

  2. Pingback: Convert UTC Date returned from SharePoint API to Local time using JavaScript and C# | reshmeeauckloo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s