SharePoint Web Services and UTC time fun and games

SharePoint is designed that people can use it from multiple time zones. For example, you might have offices in London, Paris and New York. 0700 hours in New York is not the same time as 0700 in Paris, as many transatlantic telephone calls will attest to. Further, many nations have a ‘summer’ and ‘winter’ time, which seems to have been introduced to annoy dairy farmers and computer programmers.

Thus, SharePoint needs a common way to store these times. It does this using UTC time (Coordinated Universal Time). This is, near enough, GMT, and it’s also called Zulu by pilots, the military, and pedants. 0700Z (the Z represents ‘Zulu’ as in ISO8601) is the same time whether you’re in London or Sydney – just in Sydney, 0700 Zulu is actually in the evening.

Now, you don’t need to worry about this, ‘cos SharePoint is smart enough to translate your local time to UTC when you set a date and time, and to translate it back again when displaying the date to you. Thus, 0700 in New York would be stored as 1100Z in the SharePoint database, and then when you looked at that document’s properties, it would be converted back to 0700. If you looked at the document in, say, London, it would convert 1100Z to 1200. (Well, all this is as happens today – summer/winter time makes things really hairy).

So where am I going with this? I have an application where you fill in a date and time, and submit it to SharePoint. When I get the dates back, though, the time is out by an hour. I’m in the UK and it’s summertime, so my timezone is actually UTC+1; therefore, time conversions seem likely to be the issue.

I fill in the date to set on a document in SharePoint via my application.

We’re going to use the web services to update this. This page implies I need to submit a UTC time – indeed, most of the information I’ve found shows submitting a UTC. So, I do my UTC time conversion – watching in Visual Studio, you can see the adjustment by an hour in to UTC time:

Date being processed in Visual Studio

Okay, that’s correct. That time is submitted via the Lists.UpdateListItems method – but looking in SharePoint, the time appears as an hour too early.

Date In SharePoint - Note it is 1 hour out

So, I broke out my trusty SharePoint Manager – and it shows the time as an hour too early too:

Date In SharePoint Manager - Although this is supposed to be UTC, it is the same as the local time

Note that by formatting the date with a Z at the end it’s saying that this time is UTC. However, I just didn’t believe it – SharePoint isn’t showing me UTC time, it’s showing Local (UTC+1 remember), so they can’t both read 22:59. To check then, I decided to look in the database. (I wasn’t changing anything, so I figured this would be okay).

Date In SQL Server - note it has been adjusted by 2 hours from my original value

Right, now that is the UTC time – and it’s been adjusted for my time zone twice.

Therefore, even though when I submit the time to the Web Service I’m using UTC time, and saying that I’m using UTC time as I’m formatting the datetime with Z, the date time is being converted to UTC a second time. When I then view the time, it’s being converted back once.

And SharePoint Manager is just wrong; it’s displaying the local time, not the UTC one.

Still with me?

So, I decided to look into the UpdateListItems call – is there a way of specifying what the timezone being used is? In other words, can I force the web service to recognise that I’m using UTC time?

Looking through the MSDN documentation, all the examples seem to use UTC time. Eventually, I found information on the Field element description:

StorageTZ
Specifies how date/time values are stored. When StorageTZ is set to UTC, the date/time is handled as Coordinated Universal Time (UTC). To denote that a date/time value refers to “abstract” time, meaning that the date/time is stored in the database as entered by the user without conversion or storage of the local time zone, omit the StorageTZ attribute.

If that doesn’t make any sense to you, well, it doesn’t make sense to me either. From that I don’t know what I need to set StorageTZ to – do I need to say that I’m using UTC ‘cos I’m using UTC, or do I have to omit it ‘cos I don’t want any conversion? I have no idea from that documentation.

So, what happens if I don’t convert my datetime to UTC before submitting it over the Web Service? Well, it works – BUT I still have to format the date time as yyyy-MM-ddTHH:mm:ssZ ! If you format the time as just yyyy-MM-ddTHH:mm:ss then the Web Service returns an exception. So I have to declare my datetime as being UTC even when it isn’t and SharePoint isn’t treating it as one!

I’d be quite infuriated now then! I’ve just wasted a half day ‘cos some moron doesn’t get the idea of Universal Time, and didn’t wonder what that Z on the end of the string meant…

Advertisements
SharePoint Web Services and UTC time fun and games

8 thoughts on “SharePoint Web Services and UTC time fun and games

  1. Andy,

    It would seem that Microsoft suffers from this same problem when it comes to their alerts. You can see this thread on the forums http://forums.technet.microsoft.com/en-US/sharepointadmin/thread/7aeb296b-69d8-4500-a04a-d35b3199f9c3/ and more information on one of my blog postings http://liebrand.wordpress.com/2008/05/19/does-your-sharepoint-alerts-have-wrong-time.

    In the research I have done it does appear that they are applying the timezone twice.

    Paul Liebrand
    http://liebrand.wordpress.com

    Like

  2. Interesting. Well, if they’re going through their own web services, I’m not surprised – the format of the datetimes it accepts do specify that they’re UTC time. I’m sure I’m not the only person who’s taken this to mean that they have to submit using, well, UTC time.

    But it does sound exactly like your problem.

    Like

  3. Steve says:

    StorageTZ didn’t work for me, in fact for my list I got an exception stating that the field was not installed properly. Determined I found specifying the DateInUtc as an attribute to the batch element. Check it out! So far I only found this in the ListService Protocol Specification written by Microsoft.

    Enjoy!

    Like

  4. chuck says:

    Be careful. We experienced the same thing and applied the same workaround described above – i.e., don’t convert to UTC but when sending to SP say that it’s UTC. This works for clients in some timezones but not others. For the people where it doesn’t work, the date sent to SP is really being treated as UTC. Haven’t found an explanation for the inconsistency, maybe it has to do with the user’s regional settings for the site?

    Like

  5. chuck says:

    Yes, we did confirm that the issue is related to the SharePoint regional profile. Whenever you send a date via web services without a special property (I’ll mention it in a minute) then the timezone that’s specified in the profile is used by web services for the date, even if you specify UTC with the “Z”. So if you specify 2009-16-2009T4:00:00Z, and your regional settings are CDT, then the date is treated as Jan 16 2009 4:00 AM CDT.

    There’s an undocumented fix. Add the DateInUtc=”True” property to the batch element of the UpdateListItems xml. http://social.technet.microsoft.com/Forums/en-US/sharepointdevelopment/thread/9ac1ea25-46c6-4065-bce5-d2831cfeb6a2/

    Like

  6. sandeep says:

    I have been trying to update the list item datetime values in UTC format but doesn’t work at all. Even the fix mentioned above doesn’t work for me. Isn’t there any way that this is possible. “DateInUtc” parameter works flawlessly when using with webservice GetListItems method, but dont seem to go with UpdateListItems.

    Like

  7. Hmm. The UpdateListItems method worked for me, but did have the issue of apparent UTC times being converted to local time, as described above.

    Like

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