2

I'm looking to convert Sharepoint TimeZone information into a Java TimeZone object for use with my application which is using the Sharepoint REST API. I understand that Sharepoint stores all it's timestamps in UTC but, when using the REST API, some of the timestamps are returned time zone adjusted and some are not. The format is the same for both except, as you would expect, the UTC values end with a "Z" and the time zone adjusted values do not. So, it's easy enough to convert those to Java DateTime objects but if I want to consistently return UTC values to my callers, I will have to adjust the "non-Z" values to UTC. Sharepoint allows me to get the configured TimeZone information for the Sharepoint server but what I need is a mapping from that information to the Java TimeZone ids. What Sharepoint provides through REST looks like this:

Description: (GMT-07:00) Mountain Time (US and Canada) Bias: 420 Daylight Bias: -60 Standard Bias: 0

It doesn't provide any information that might indicate, say, when DST starts etc. but I figured I wouldn't need to worry about that as long as I can map the Sharepoint "Description" to a Java TimeZone id. So, I'm hoping that someone has run into this need before or perhaps has some other suggestion as to how I might get the UTC values I need from Sharepoint's REST API.

Thomas Doman
  • 556
  • 7
  • 19
  • Much web searching. :) I've found similar things where people are trying to map Exchange TZs (which are different from the Sharepoint versions in the information they provide and the names they use) to Java TimeZone ids and a mapper that takes Olsen values and maps them to Java TimeZone ids. I'm not surprised Microsoft doesn't even follow a standard within their own products but I am surprised they haven't at least provided a way for application developers to map their non-standard identifiers to some standard. So, short of sitting down and trying to hack out my own map, nothing else. – Thomas Doman Mar 13 '14 at 21:38
  • 1
    With Sharepoint you can do a request asking for the time to be in UTC with ``(see there maybe: http://msdn.microsoft.com/en-us//library/office/dn292554%28v=office.15%29.aspx). That way you can more easily deal with it. – AymKdn Mar 14 '14 at 08:34
  • Thanks Aym, that's tantalizing. I'll have to see if that's something I could send on a GET. It's funny that for File entries I can get both a TZ adjusted version ("Modified" and "Created") and non-TZ version ("Last Modification" and "Creation" or something like that, I'm on the road ATM so I don't have the exact names and locations to reference). But for Folders I can ONLY get the TZ adjusted versions (with the same element names as for Files). Anyway, I'll see if there are some payload body options or perhaps something on the query string that I might use. – Thomas Doman Mar 14 '14 at 15:28

2 Answers2

3

Java uses IANA time zones.

Mappings exist for Microsoft Windows to IANA time zones in the CLDR supplemental data.

However, Sharepoint time zones are not quite the same as regular Windows time zones. Instead of using string time zone keys like the ones found in the Windows registry (or via the Id property of .NET's TimeZoneInfo class), they use integer ids that are specific to Sharepoint. You can find a partial list here, and a more updated (unofficial) list here.

The Sharepoint time zone description loosely matches the DisplayName of the Windows time zone, but it's not a perfect match. It's entirely possible that some of the Sharepoint time zones are not defined as Windows time zones.

Assuming you mapped each Sharepoint SPTimeZone numeric id to a TimeZoneInfo id, then you could use the CLDR data to convert from there to IANA time zones. And if you can run .NET code, then you can do this quite easily with the Noda Time library, as described here.

UPDATE

I went ahead and mapped these to reasonable equivalents. These aren't guaranteed to be 100% accurate, but I think it will give you what you need.

These are mostly matched using the roughly equivalent Windows time zone, then to the IANA zone using the primary CLDR mapping. The only exceptions were:

  • SharePoint zones 33 and 81 are separate, but matched to the single "SA Western Standard Time" windows zone. They map to IANA zones America/La_Paz and America/Manaus respectively.

  • SharePoint zones 41 and 92 are separate, but matched to the single "Magadan Standard Time" windows zone. They map to IANA zones Asia/Magadan and Asia/Kamchatka respectively.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Matt, what a fantastic response! Thank you very much for this thorough mapping. It seems unlikely I'll be able to get Sharepoint to only return me UTC values (though I'm still holding out a little hope based on AymKdn's comment and link above) but this will allow me to quickly create my own mapper. Thanks again! – Thomas Doman Mar 14 '14 at 15:34
  • Matt, I added a few below that I got from my Sharepoint 2013 environment. Was the list you gave from Sharepoint 2010? I haven't finished setting up my Sharepoint 2010 system to try it out yet but I'm wondering if they came out of the gate with 10 less time zones. Anyway, what do you think of the mappings I suggested for those 10? Several of them are a bit of a "best guess". – Thomas Doman Mar 19 '14 at 15:55
  • I updated the GIST links with the new zones. See also my edits and comments in your answer. – Matt Johnson-Pint Mar 19 '14 at 19:33
1

I've started using Matt's mappings and they look spot on. However, I noticed with Sharepoint 2013 that there are 10 more on the end of the list Matt provided. So, using the lovely list of IANA Time Zones here, http://en.wikipedia.org/wiki/List_of_tz_database_time_zones I have added these to Matt's list with my best guess from the IANA list (yep, just a best guess so if you have feedback, let me know).

"SharePoint ID","SharePoint Description","IANA Time Zone"
"95","(UTC-11:00) Coordinated Universal Time-11","Etc/GMT+11"
"96","(UTC-02:00) Coordinated Universal Time-02","Etc/GMT+2"
"97","(UTC+12:00) Coordinated Universal Time+12","Etc/GMT-12"
"98","(UTC+02:00) Damascus","Asia/Damascus"
"99","(UTC+12:00) Magadan","Asia/Magadan"
"100","(UTC+03:00) Kaliningrad, Minsk","Europe/Kaliningrad"
"101","(UTC+02:00) Istanbul","Europe/Istanbul"
"102","(UTC+06:00) Dhaka","Asia/Dhaka"
"103","(UTC-03:00) Salvador","America/Bahia"
"104","(UTC+02:00) E. Europe","Europe/Chisinau"
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Thomas Doman
  • 556
  • 7
  • 19
  • ... as an example of "best guess" ... you can't really get any more in the center of the Eastern Europe time zone than Istanbul though there are many choices in that area from Athens, Greece to Helsinki, Finland. – Thomas Doman Mar 18 '14 at 20:16
  • I made some edits to your guesses. In general, the `Etc/GMT...` zones should be used for fixed-offset zones. (The sign is intentionally reversed in the name.) The other changes are based on the CLDR mappings. – Matt Johnson-Pint Mar 19 '14 at 19:24
  • The last one, zone 104, is a bit strange because it doesn't align with any real-world time zone at all, so there is no corresponding entry in the IANA database. The CLDR used to list it as mapped to `Asia/Nicosia` (Cyprus), but that was [determined to be incorrect](http://unicode.org/cldr/trac/ticket/6973) and was recently changed. One could argue that if a user has SharePoint TZ 104, or if they have set "E. Europe" time zone, then they have done so in error. – Matt Johnson-Pint Mar 19 '14 at 19:25
  • Again, thank you very much. The fixed offset mappings make complete sense. If someone chooses those, they must be in some outlier region where strict fixed-offset is the best choice. Also, I looked at the ticket you referenced, that makes sense. For me, it sounds like, rather than throwing an exception, my best bet would be to map it to "Etc/GMT-2" (just in case, since I'm just a consumer) but it makes sense for your GISTs to remain unspecified since that looks like the current official state. – Thomas Doman Mar 19 '14 at 19:54
  • Sure, except `Etc/GMT-2` has no DST, and "E. Europe" *does*, just not for anything that aligns exactly to *current* DST rules. I'm surprised to see it at the end of the list. It probably exists only for historical reasons. You could probably continue to use `Asia/Nicosia`, as it will only be off by an hour twice annually, but the correct response would probably be to kick back an error to force the user to update to a more reasonable time zone. – Matt Johnson-Pint Mar 19 '14 at 19:58
  • Heh, I was just editing my last comment based on that. I will definitely log an error in my service but I'm just a consumer and can't really force them to make a better choice. Good to know though that "E. Europe" at least historically implied some kind of DST rule as well. I guess I'll go w/ 'Asia/Nicosia' and log my error but it sounds like it's not going to be a common choice in that region. – Thomas Doman Mar 19 '14 at 20:07
  • Note, `E.Europe` actually maps to `Europe/Chisinau`. Updated. – Matt Johnson-Pint Jul 03 '19 at 16:20