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:
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.
So, I broke out my trusty SharePoint Manager – and it shows the time as an hour too early too:
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).
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:
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…