39362

Converting Sharepoint TimeZone information to a Java TimeZone

Question:

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>.

<strong>UPDATE</strong>

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 America/La_Paz and America/Manaus respectively.

</li> <li>

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.

</li> </ul>

Answer2:

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",""

Recommend

  • JavaScript Intl API gives different results in Chrome
  • Can not permit Java encoding names in Tomcat6
  • Guess timezone name for custom date
  • Show fields for a Lucene/Elasticsearch index
  • Is it possible to use UTC Offset instead of timezone name on iCal.net?
  • Stale Java classes when using ColdFusion 10's custom Java loader
  • What is http response status of 508?
  • The binary operator LessThan is not defined for the types 'System.Nullable`1[System.DateTime]&#
  • Maintaining unique objects for names with concurrent delete
  • how to handle large size of update query in mysql with laravel
  • Google Cloud SQL and time zones
  • SQL Server Integrated Security from an Azure Web Site
  • How to show a specific VC , more like navigate to a stack of VC
  • What are zone turns?
  • Furthest-point Voronoi diagram in Java
  • Adding independent aspx/asmx pages into DotNetNuke
  • Merge Module leaving files during uninstall
  • powershell Get-Counter -ComputerName parameter on Windows 7
  • Xamarin MonoAndroid Azure mobile service InsertAsync
  • Why does java tzupdater add leap seconds?
  • Private IP address in reserved subnet range
  • How To Customize ASP.NET Chart Databound To SqlDataSource
  • how do i write assembly code from c#?
  • Updating both a ConcurrentHashMap and an AtomicInteger safely
  • Redirect STDERR in OPEN pipe comand. Perl Linux
  • Silverlight DependencyProperty.SetCurrentValue Equivalent
  • Zurb Foundation _global.scss meta styles for js?
  • Xcode 4 NSLog Macro link in Xcode 3
  • Join two tables and save into third-sql
  • How to make Safari send if-modified-since header?
  • SVN: Merging two branches together
  • Hibernate gives error error as “Access to DialectResolutionInfo cannot be null when 'hibernate.
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How to stop GridView from loading again when I press back button?
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • File not found error Google Drive API
  • Qt: Run a script BEFORE make