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.Answer1:
Java uses <a href="http://www.iana.org/time-zones" rel="nofollow">IANA time zones</a>.
Mappings exist for Microsoft Windows to IANA time zones in the <a href="http://unicode.org/repos/cldr/trunk/common/supplemental/windowsZones.xml" rel="nofollow">CLDR supplemental data</a>.
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 <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spregionalsettings.timezones.aspx" rel="nofollow">here</a>, and a more updated (unofficial) list <a href="http://johnlivingstontech.blogspot.com/2011/01/sharepoint-spregionalsettingsglobaltime.html" rel="nofollow">here</a>.
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, <a href="https://stackoverflow.com/q/17348807/634824" rel="nofollow">as described here</a>.
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.<ul><li><a href="https://gist.github.com/mj1856/9542228" rel="nofollow">CSV spreadsheet for the full mapping</a></li> <li><a href="https://gist.github.com/mj1856/9542180" rel="nofollow">JSON for SharePoint ID to IANA TZ name</a> - ready for you to use in any language</li> </ul>
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:<ul><li>
SharePoint zones 33 and 81 are separate, but matched to the single "SA Western Standard Time" windows zone. They map to IANA zones
SharePoint zones 41 and 92 are separate, but matched to the single "Magadan Standard Time" windows zone. They map to IANA zones
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, <a href="http://en.wikipedia.org/wiki/List_of_tz_database_time_zones" rel="nofollow">http://en.wikipedia.org/wiki/List_of_tz_database_time_zones</a> 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",""