4

I have written an Angular site using the SharePoint 2010 REST Api. To query the list I use listdata.svc, for example:

https://server/sites/mysite/_vti_bin/listdata.svc/MyListName?$select=Id%2CName%2CTitle%2CMyDescription%2CCreated%2CModified%2CPath%2CCreatedBy%2FAccount%2CModifiedBy%2FAccount&$expand=CreatedBy%2CModifiedBy&$orderby=Name

I am also sending in a few headers but mainly Accept: application/json;charset=utf-8;odata=verbose

In my application I display the modified date the problem is that the dates I get back are incorrect. The dates come back in JSON as the following format:

"Modified":"\/Date(1444141058000)\/"

That translates to "Tue, 06 Oct 2015 14:17:38 GMT" but the problem is that the time I modified that file in SharePoint was 06 Oct 2015 2:17:38 Pacific time.

What I think is happening is that SharePoint is returning the date back to me localized to the server's time zone (Pacific) but isnt converting the JSON to UTC or specifying what offset it is using.

In the query API I see the DateInUtc attribute but I dont see anyting equivalent in the listdata.svc api.

Has anyone run into this problem or have insights on how to fix this?

2

This has to be a bug in the ListData.svc API. The time on the server should not matter, nor should the regional settings of the SPWeb or the individual user. Why does the API not just return the UTC ISO string for the DateTime? It seems to just return ticks representing local (server? site? user?) time, but without a timezone indicator. So javascript date parsers treat it as UTC, resulting in a 'double' offset.

If I set a datetime field (anywhere - through JSOM, ListData.svc, or just the SP UI) it reflects properly in sharepoint. For example: 8/16/2016 12:36 PM, ET (-400).

ListData.svc returns back this in JSON: RequestDate:"/Date(1471350997000)/"

Parse results (Date ctor and moment.js):

new Date(1471350997000) //-> Tue Aug 16 2016 08:36:37 GMT-0400 (Eastern Daylight Time)

moment("/Date(1471350997000)/").toDate() //-> Tue Aug 16 2016 08:36:37 GMT-0400 (Eastern Daylight Time)

This is clearly not the same DateTime that is stored in the SharePoint field. The date is parsed as GMT-0400, resulting in a double offset (actual time is 12:36 GMT-0400, but the returned value is parsed as 08:36 GMT-0400.)

If you can make assumptions about the locations of your users, you can fix this, but if you have to support users in many timezones, this becomes very difficult to manage.

The best 'general' formula I've found (I'm not quite sure if this 100% accounts for all time zones, daylight savings, etc) is the following:

var offset = moment().utcOffset()
if(offset < 0){
    moment("/Date(1471350997000)/").subtract(offset, 'minutes').format()
}
else {
   moment("/Date(1471350997000)/").add(offset, 'minutes').format()
}

basically, we are accounting for the 'double' offset that sharepoint is performing by subtracting or adding the current browser offset from/to the parsed time.

  • wow i've been fighting for hours trying to figure out, WHY IS THIS SHOWING 6pm the night before??!??. Thanks Sean! +1 – Mike Dec 1 '16 at 18:03
1

SP is sending you its local date time. It is right. No if you need to convert it into your local date time, then use to get Date

toLocalDateString()

For time

toLocaleTimeString()

I have translated your Modified in the following way

var jsonDateTime = "\/Date(1444141058000)\/";
var result = new Date(parseInt(jsonDateTime.substr(6)));

It gave me my local DateTime directly

Tue Oct 06 2015 20:17:38 GMT+0600 (Bangladesh Standard Time)
  • This is how I naively tried to parse the timestamp originally. The problem is that the timestamp given by sharepoint is in the server's local time so when you use the timestamp integer to construct the Date object you dont get the correct value. For me when I parse that I get: Tue Oct 06 2015 08:17:38 GMT-0600 (Mountain Daylight Time) but that is not correct. It should be 3:17 PM Mountain Daylight Time. – gamelover42 Oct 7 '15 at 15:15
  • What should be returned from SharePoint is the UTC timestamp which would be /Date(1444166258000)/ – gamelover42 Oct 7 '15 at 15:27
  • Yes ....... It is UTC. Try toLocaleTimeString() and let me know what it returns – Atish Dipongkor - MVP Oct 7 '15 at 16:43
  • The call to toLocaleString gives '10/6/2015, 8:17:38 AM' which is not correct – gamelover42 Oct 7 '15 at 19:42
  • Check your and server data time – Atish Dipongkor - MVP Oct 8 '15 at 2:30
1

I found the problem. Each site in SharePoint has a time zone under its regional settings. What I have observed is that the listdata.svc uses the set time zone to calculate the time zone offset for dates that are returned by listdata.svc in either XML or JSON. The problem is that in the case of XML no offset is specified and in the case of JSON the dates should always be in UTC because that is what that constructor on the Date object expects.

The following two articles document how to change the time zone settings in detail but basically you have to change it in two places, the web application (these are default values for new sites) and in the Site itself

  1. Open central administration.
  2. Click "Manage web applications"
  3. Select the web application entry in the list
  4. Click "General Settings" in the ribbon.
  5. Set the "Default Time Zone" property to "(UTC) Coordinated Universal Time"
  6. Click "OK".
  7. Next for each site open the site's URL e.g. http://mysharepointsite/sites/foo.
  8. In the menu under "Site Actions" click "Site Settings"
  9. In the "Site Administation" group click "Regional Settings"
  10. Change the "Time Zone" property to "(UTC) Coordinated Universal Time".
  11. Click "OK".

Alternatively each site can be changed using a powershell script. Below are a couple of articles on the subject. See the second link below for a sample powershell script.

I should add that this may not be an ideal solution in every case because this will also affect how SharePoint's built-in list web parts display their dates. In my case we are exclusively using Angular and REST for our pages and do not use any SharePoint web parts.

0

I've encountered this behavior as well. My Sharepoint Online instance is also returning this date format when I use listdata.svc for REST queries. (I'm using the listdata.svc because the new API doesn't fully support paging, but that's another story...)

Anyway, given a data format like yours, you can correct the issue if you use moment.js, which I highly recommend.

This code will resolve the issue by incrementing the time by the local offset. (Of note this will only work if the client and the server are in the same time zone, but in my case that is an acceptable assumption.)

    function parseDate(dateString) {

        var date = moment(dateString);

        date.add(date.utcOffset() * -1, 'm');

        return date;
    }
  • I tried this solution initially and you are correct that this method only works if the server and browser are in the same timezone. In my case users can be anywhere so this was not a good solution. – gamelover42 Aug 13 '16 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.