Converting Microsoft Timezone to standards timezone data in rails

Question

I have a rails app that connects to a SQL Server DB, and there's a windows desktop app that connects to the same DB. The Windows app is using the Microsoft windows timezone database so I get a timezone name like Pacific Daylight Time. In rails, the ActiveSupport::TimeZone class gives us a mapping hash where the key would be like Pacific Time (US & Canada) and the value would be like America/Los_Angeles. Is there anything already out there that will convert the windows format to the standards format so I can use Time.zone?

Or maybe is there a timezone gem that will allow me to configure rails to use the windows format?

Here's an array I've started to help with the conversion, but there's a bit I'm not 100% sure on.

  DAYLIGHT = [
{name: 'Dateline Daylight Time',          offset: -12,  alt: 'International Date Line West'},
{name: 'UTC-11',                          offset: -11,  alt: ''},
{name: 'Hawaiian Daylight Time',          offset: -10,  alt: 'Hawaii'},
{name: 'Alaskan Daylight Time',           offset: -9,   alt: 'Alaska'},
{name: 'Pacific Daylight Time (Mexico)',  offset: -8,   alt: ''},
{name: 'Pacific Daylight Time',           offset: -8,   alt: 'Pacific Time (US & Canada)'},
{name: 'US Mountain Daylight Time',       offset: -7,   alt: 'Mountain Time (US & Canada)'},
{name: 'Mountain Daylight Time (Mexico)', offset: -7,   alt: ''},
{name: 'Mountain Daylight Time',          offset: -7,   alt: 'Mountain Time (US & Canada)'},
{name: 'Central America Daylight Time',   offset: -6,   alt: 'Central Time (US & Canada)'},
{name: 'Central Daylight Time',           offset: -6,   alt: 'Central Time (US & Canada)'},
{name: 'Central Daylight Time (Mexico)',  offset: -6,   alt: ''},
{name: 'Canada Central Daylight Time',    offset: -6,   alt: ''},
{name: 'SA Pacific Daylight Time',        offset: -5,   alt: ''},
{name: 'Eastern Daylight Time',           offset: -5,   alt: 'Eastern Time (US & Canada)'},
{name: 'US Eastern Daylight Time',        offset: -5,   alt: 'Eastern Time (US & Canada)'},
{name: 'Venezuela Daylight Time',         offset: -4.5, alt: ''},
{name: 'Paraguay Daylight Time',          offset: -4,   alt: ''},
{name: 'Atlantic Daylight Time',          offset: -4,   alt: ''},
{name: 'Central Brazilian Daylight Time', offset: -4,   alt: ''},
{name: 'SA Western Daylight Time',        offset: -4,   alt: ''},
{name: 'Pacific SA Daylight Time',        offset: -4,   alt: ''},
{name: 'Newfoundland Daylight Time',      offset: -3.5, alt: ''},
{name: 'E. South America Daylight Time',  offset: -3,   alt: ''},
{name: 'Argentina Daylight Time',         offset: -3,   alt: ''},
{name: 'SA Eastern Daylight Time',        offset: -3,   alt: ''},
{name: 'Greenland Daylight Time',         offset: -3,   alt: ''},
{name: 'Montevideo Daylight Time',        offset: -3,   alt: ''},
{name: 'Bahia Daylight Time',             offset: -3,   alt: ''},
{name: 'UTC-02',                          offset: -2,   alt: ''},
{name: 'Mid-Atlantic Daylight Time',      offset: -2,   alt: ''},
{name: 'Azores Daylight Time',            offset: -1,   alt: ''},
{name: 'Cabo Verde Daylight Time',        offset: -1,   alt: ''},
{name: 'Morocco Daylight Time',           offset: 0,    alt: ''},
{name: 'Coordinated Universal Time',      offset: 0,    alt: ''},
{name: 'GMT Daylight Time',               offset: 0,    alt: ''},
{name: 'Greenwich Daylight Time',         offset: 0,    alt: ''},
{name: 'W. Europe Daylight Time',         offset: 1,    alt: ''},
{name: 'Central Europe Daylight Time',    offset: 1,    alt: ''},
{name: 'Romance Daylight Time',           offset: 1,    alt: ''},
{name: 'Central European Daylight Time',  offset: 1,    alt: ''},
{name: 'W. Central Africa Daylight Time', offset: 1,    alt: ''},
{name: 'Namibia Daylight Time',           offset: 1,    alt: ''},
{name: 'Jordan Daylight Time',            offset: 2,    alt: ''},
{name: 'GTB Daylight Time',               offset: 2,    alt: ''},
{name: 'Middle East Daylight Time',       offset: 2,    alt: ''},
{name: 'Egypt Daylight Time',             offset: 2,    alt: ''},
{name: 'Syria Daylight Time',             offset: 2,    alt: ''},
{name: 'E. Europe Daylight Time',         offset: 2,    alt: ''},
{name: 'South Africa Daylight Time',      offset: 2,    alt: ''},
{name: 'FLE Daylight Time',               offset: 2,    alt: ''},
{name: 'Turkey Daylight Time',            offset: 2,    alt: ''},
{name: 'Jerusalem Daylight Time',         offset: 2,    alt: ''},
{name: 'Russia TZ 1 Daylight Time',       offset: 2,    alt: ''},
{name: 'Libya Daylight Time',             offset: 2,    alt: ''},
{name: 'Arabic Daylight Time',            offset: 3,    alt: ''},
{name: 'Arab Daylight Time',              offset: 3,    alt: ''},
{name: 'Belarus Daylight Time',           offset: 3,    alt: ''},
{name: 'Russia TZ 2 Daylight Time',       offset: 3,    alt: ''},
{name: 'E. Africa Daylight Time',         offset: 3,    alt: ''},
{name: 'Iran Daylight Time',              offset: 3.5,  alt: ''},
{name: 'Arabian Daylight Time',           offset: 4,    alt: ''},
{name: 'Azerbaijan Daylight Time',        offset: 4,    alt: ''},
{name: 'Russia TZ 3 Daylight Time',       offset: 4,    alt: ''},
{name: 'Mauritius Daylight Time',         offset: 4,    alt: ''},
{name: 'Georgian Daylight Time',          offset: 4,    alt: ''},
{name: 'Caucasus Daylight Time',          offset: 4,    alt: ''},
{name: 'Afghanistan Daylight Time',       offset: 4.5,  alt: ''},
{name: 'West Asia Daylight Time',         offset: 5,    alt: ''},
{name: 'Russia TZ 4 Daylight Time',       offset: 5,    alt: ''},
{name: 'Pakistan Daylight Time',          offset: 5,    alt: ''},
{name: 'India Daylight Time',             offset: 5.5,  alt: ''},
{name: 'Sri Lanka Daylight Time',         offset: 5.5,  alt: ''},
{name: 'Nepal Daylight Time',             offset: 5.75, alt: ''},
{name: 'Central Asia Daylight Time',      offset: 6,    alt: ''},
{name: 'Bangladesh Daylight Time',        offset: 6,    alt: ''},
{name: 'Russia TZ 5 Daylight Time',       offset: 6,    alt: ''},
{name: 'Myanmar Daylight Time',           offset: 6.5,  alt: ''},
{name: 'SE Asia Daylight Time',           offset: 7,    alt: ''},
{name: 'Russia TZ 6 Daylight Time',       offset: 7,    alt: ''},
{name: 'China Daylight Time',             offset: 8,    alt: ''},
{name: 'Russia TZ 7 Daylight Time',       offset: 8,    alt: ''},
{name: 'Malay Peninsula Daylight Time',   offset: 8,    alt: ''},
{name: 'W. Australia Daylight Time',      offset: 8,    alt: ''},
{name: 'Taipei Daylight Time',            offset: 8,    alt: ''},
{name: 'Ulaanbaatar Daylight Time',       offset: 8,    alt: ''},
{name: 'Tokyo Daylight Time',             offset: 9,    alt: ''},
{name: 'Korea Daylight Time',             offset: 9,    alt: ''},
{name: 'Russia TZ 8 Daylight Time',       offset: 9,    alt: ''},
{name: 'Cen. Australia Daylight Time',    offset: 9.5,  alt: ''},
{name: 'AUS Central Daylight Time',       offset: 9.5,  alt: ''},
{name: 'E. Australia Daylight Time',      offset: 10,   alt: ''},
{name: 'AUS Eastern Daylight Time',       offset: 10,   alt: ''},
{name: 'West Pacific Daylight Time',      offset: 10,   alt: ''},
{name: 'Tasmania Daylight Time',          offset: 10,   alt: ''},
{name: 'Magadan Daylight Time',           offset: 10,   alt: ''},
{name: 'Russia TZ 9 Daylight Time',       offset: 10,   alt: ''},
{name: 'Russia TZ 10 Daylight Time',      offset: 11,   alt: ''},
{name: 'Central Pacific Daylight Time',   offset: 11,   alt: ''},
{name: 'Russia TZ 11 Daylight Time',      offset: 12,   alt: ''},
{name: 'New Zealand Daylight Time',       offset: 12,   alt: ''},
{name: 'UTC+12',                          offset: 12,   alt: ''},
{name: 'Fiji Daylight Time',              offset: 12,   alt: ''},
{name: 'Kamchatka Daylight Time',         offset: 12,   alt: ''},
{name: 'Tonga Daylight Time',             offset: 13,   alt: ''},
{name: 'Samoa Daylight Time',             offset: 13,   alt: ''},
{name: 'Line Islands Daylight Time',      offset: 14,   alt: ''}
]

Answer 1

Improved Answer

The functionality described in my original answer (below) is now available in my TimeZoneConverter library for .NET. All the hard work is done for you, and it is kept updated with changes to the world's time zones. The examples in the project readme show how to convert between Windows, IANA, and Rails identifiers.

Since the app described in the question is in Rails, I suggest running a background job in .NET to convert Windows time zone IDs to those needed in your Rails app and keep them in a separate column.

---

Original Answer

The resource you're looking for is the Windows to IANA mapping file maintained as part of the Unicode CLDR project. The file is located within the CLDR release at common/supplemental/windowsZones.xml, and you can find the current "development version" of it here.

Be aware of a few things:

• The file does get updated as new time zones come out from Microsoft and from IANA, or occasionally when governments change their time zone rules significantly enough that a different mapping applies.

• The offsets in your list would just be the standard offset - that is, the one that applies when daylight saving time is not in effect. A "time zone" is composed of the standard offsets, the daylight offsets, and the specific dates and times of the DST transitions, along with the history of changes for the offsets and transitions.

• The Rails time zone names should not be used anywhere outside of Rails. My understanding is that they were actually created before Rails decided to use standard IANA/Olson time zones, then later when the Ruby tzinfo gem became viable, the Rails zones were retrofitted through the MAPPING constant shown on this page, then some were added afterwards as individual complaints arose. If possible, just use the IANA time zones directly, via the tzinfo gem. If not, then you will have two layers of mapping to go through (Microsoft -> IANA -> Rails).

• I do not know of a Rails-specific implementation of the CLDR mappings. I checked a couple of CLDR for Rails projects, and found they didn't include that particular portion of the CLDR. However, if you have control over the Windows side of things, then you might consider using the .NET implementation I describe in this answer. You would do the Windows to IANA conversion on the Windows side, then either use the IANA zones directly with tzinfo, or map to Rails zones in your Rails app.

• Also realize that the number of Rails time zones are significantly less than those supported by IANA. I haven't checked, but it's likely that some of the Windows zones map to IANA zones that aren't in Rails. Also, there are some Rails zones that have multiple Rails entries, but only one IANA mapping - essentially making them equivalent aliases.

• Both Rails and Windows use "UTC" as a zone id, which Rails maps to "Etc/UTC", but CLDR maps to "Etc/GMT". You'll have to handle this one manually.

• Lastly, recognize that the CLDR uses "stable" identifiers in a different way than IANA's "canonical" identifiers, which may cause some difficulties during the mapping.

  • As an example, consider that the Microsoft zone "India Standard Time" maps to "Asia/Calcutta" in the CLDR, because that's what the original IANA zone was. However, IANA changed the zone to "Asia/Kolkata", and set up a link for backward compatibility for "Asia/Calcutta". When you look in the Rails MAPPING constant, there are four Rails zones, "Chennai", "Kolkata", "Mumbai", and "New Delhi" - all of which are mapped to "Asia/Kolkata".

  • To overcome this, you will also need another file from the CLDR, common/bcp47/timezone.xml. You can use this file to find all of the links between aliased IANA zones, which can help you map back to the Rails zone.

So yes, it's possible - but it's not easy. ;)

I've performed the current mappings for you, which are as follows:

"Windows","IANA","Rails"
"W. Central Africa Standard Time","Africa/Algiers","West Central Africa"
"Egypt Standard Time","Africa/Cairo","Cairo"
"Morocco Standard Time","Africa/Casablanca","Casablanca"
"South Africa Standard Time","Africa/Harare","Harare"
"South Africa Standard Time","Africa/Johannesburg","Pretoria"
"Greenwich Standard Time","Africa/Monrovia","Monrovia"
"E. Africa Standard Time","Africa/Nairobi","Nairobi"
"Argentina Standard Time","America/Argentina/Buenos_Aires","Buenos Aires"
"SA Pacific Standard Time","America/Bogota","Bogota"
"Venezuela Standard Time","America/Caracas","Caracas"
"Central Standard Time","America/Chicago","Central Time (US & Canada)"
"Mountain Standard Time (Mexico)","America/Chihuahua","Chihuahua"
"Mountain Standard Time","America/Denver","Mountain Time (US & Canada)"
"Greenland Standard Time","America/Godthab","Greenland"
"Central America Standard Time","America/Guatemala","Central America"
"SA Western Standard Time","America/Guyana","Georgetown"
"Atlantic Standard Time","America/Halifax","Atlantic Time (Canada)"
"US Eastern Standard Time","America/Indiana/Indianapolis","Indiana (East)"
"Alaskan Standard Time","America/Juneau","Alaska"
"SA Western Standard Time","America/La_Paz","La Paz"
"SA Pacific Standard Time","America/Lima","Lima"
"SA Pacific Standard Time","America/Lima","Quito"
"Pacific Standard Time","America/Los_Angeles","Pacific Time (US & Canada)"
"Mountain Standard Time (Mexico)","America/Mazatlan","Mazatlan"
"Central Standard Time (Mexico)","America/Mexico_City","Guadalajara"
"Central Standard Time (Mexico)","America/Mexico_City","Mexico City"
"Central Standard Time (Mexico)","America/Monterrey","Monterrey"
"Montevideo Standard Time","America/Montevideo","Montevideo"
"Eastern Standard Time","America/New_York","Eastern Time (US & Canada)"
"US Mountain Standard Time","America/Phoenix","Arizona"
"Canada Central Standard Time","America/Regina","Saskatchewan"
"Pacific SA Standard Time","America/Santiago","Santiago"
"E. South America Standard Time","America/Sao_Paulo","Brasilia"
"Newfoundland Standard Time","America/St_Johns","Newfoundland"
"Pacific Standard Time","America/Tijuana","Tijuana"
"Central Asia Standard Time","Asia/Almaty","Almaty"
"Arabic Standard Time","Asia/Baghdad","Baghdad"
"Azerbaijan Standard Time","Asia/Baku","Baku"
"SE Asia Standard Time","Asia/Bangkok","Bangkok"
"SE Asia Standard Time","Asia/Bangkok","Hanoi"
"China Standard Time","Asia/Chongqing","Chongqing"
"Sri Lanka Standard Time","Asia/Colombo","Sri Jayawardenepura"
"Bangladesh Standard Time","Asia/Dhaka","Astana"
"Bangladesh Standard Time","Asia/Dhaka","Dhaka"
"China Standard Time","Asia/Hong_Kong","Hong Kong"
"North Asia East Standard Time","Asia/Irkutsk","Irkutsk"
"SE Asia Standard Time","Asia/Jakarta","Jakarta"
"Israel Standard Time","Asia/Jerusalem","Jerusalem"
"Afghanistan Standard Time","Asia/Kabul","Kabul"
"Russia Time Zone 11","Asia/Kamchatka","Kamchatka"
"Pakistan Standard Time","Asia/Karachi","Islamabad"
"Pakistan Standard Time","Asia/Karachi","Karachi"
"Nepal Standard Time","Asia/Kathmandu","Kathmandu"
"India Standard Time","Asia/Kolkata","Chennai"
"India Standard Time","Asia/Kolkata","Kolkata"
"India Standard Time","Asia/Kolkata","Mumbai"
"India Standard Time","Asia/Kolkata","New Delhi"
"North Asia Standard Time","Asia/Krasnoyarsk","Krasnoyarsk"
"Singapore Standard Time","Asia/Kuala_Lumpur","Kuala Lumpur"
"Arab Standard Time","Asia/Kuwait","Kuwait"
"Magadan Standard Time","Asia/Magadan","Magadan"
"Arabian Standard Time","Asia/Muscat","Abu Dhabi"
"Arabian Standard Time","Asia/Muscat","Muscat"
"N. Central Asia Standard Time","Asia/Novosibirsk","Novosibirsk"
"Myanmar Standard Time","Asia/Rangoon","Rangoon"
"Arab Standard Time","Asia/Riyadh","Riyadh"
"Korea Standard Time","Asia/Seoul","Seoul"
"China Standard Time","Asia/Shanghai","Beijing"
"Singapore Standard Time","Asia/Singapore","Singapore"
"Russia Time Zone 10","Asia/Srednekolymsk","Srednekolymsk"
"Taipei Standard Time","Asia/Taipei","Taipei"
"West Asia Standard Time","Asia/Tashkent","Tashkent"
"Georgian Standard Time","Asia/Tbilisi","Tbilisi"
"Iran Standard Time","Asia/Tehran","Tehran"
"Tokyo Standard Time","Asia/Tokyo","Osaka"
"Tokyo Standard Time","Asia/Tokyo","Sapporo"
"Tokyo Standard Time","Asia/Tokyo","Tokyo"
"Ulaanbaatar Standard Time","Asia/Ulaanbaatar","Ulaanbaatar"
"Central Asia Standard Time","Asia/Urumqi","Urumqi"
"Vladivostok Standard Time","Asia/Vladivostok","Vladivostok"
"Yakutsk Standard Time","Asia/Yakutsk","Yakutsk"
"Ekaterinburg Standard Time","Asia/Yekaterinburg","Ekaterinburg"
"Caucasus Standard Time","Asia/Yerevan","Yerevan"
"Azores Standard Time","Atlantic/Azores","Azores"
"Cape Verde Standard Time","Atlantic/Cape_Verde","Cape Verde Is."
"UTC-02","Atlantic/South_Georgia","Mid-Atlantic"
"Cen. Australia Standard Time","Australia/Adelaide","Adelaide"
"E. Australia Standard Time","Australia/Brisbane","Brisbane"
"AUS Central Standard Time","Australia/Darwin","Darwin"
"Tasmania Standard Time","Australia/Hobart","Hobart"
"AUS Eastern Standard Time","Australia/Melbourne","Canberra"
"AUS Eastern Standard Time","Australia/Melbourne","Melbourne"
"W. Australia Standard Time","Australia/Perth","Perth"
"AUS Eastern Standard Time","Australia/Sydney","Sydney"
"UTC","Etc/UTC","UTC"
"W. Europe Standard Time","Europe/Amsterdam","Amsterdam"
"GTB Standard Time","Europe/Athens","Athens"
"Central Europe Standard Time","Europe/Belgrade","Belgrade"
"W. Europe Standard Time","Europe/Berlin","Berlin"
"W. Europe Standard Time","Europe/Berlin","Bern"
"Central Europe Standard Time","Europe/Bratislava","Bratislava"
"Romance Standard Time","Europe/Brussels","Brussels"
"GTB Standard Time","Europe/Bucharest","Bucharest"
"Central Europe Standard Time","Europe/Budapest","Budapest"
"Romance Standard Time","Europe/Copenhagen","Copenhagen"
"GMT Standard Time","Europe/Dublin","Dublin"
"FLE Standard Time","Europe/Helsinki","Helsinki"
"Turkey Standard Time","Europe/Istanbul","Istanbul"
"Kaliningrad Standard Time","Europe/Kaliningrad","Kaliningrad"
"FLE Standard Time","Europe/Kiev","Kyiv"
"GMT Standard Time","Europe/Lisbon","Lisbon"
"Central Europe Standard Time","Europe/Ljubljana","Ljubljana"
"GMT Standard Time","Europe/London","Edinburgh"
"GMT Standard Time","Europe/London","London"
"Romance Standard Time","Europe/Madrid","Madrid"
"Belarus Standard Time","Europe/Minsk","Minsk"
"Russian Standard Time","Europe/Moscow","Moscow"
"Russian Standard Time","Europe/Moscow","St. Petersburg"
"Romance Standard Time","Europe/Paris","Paris"
"Central Europe Standard Time","Europe/Prague","Prague"
"FLE Standard Time","Europe/Riga","Riga"
"W. Europe Standard Time","Europe/Rome","Rome"
"Russia Time Zone 3","Europe/Samara","Samara"
"Central European Standard Time","Europe/Sarajevo","Sarajevo"
"Central European Standard Time","Europe/Skopje","Skopje"
"FLE Standard Time","Europe/Sofia","Sofia"
"W. Europe Standard Time","Europe/Stockholm","Stockholm"
"FLE Standard Time","Europe/Tallinn","Tallinn"
"W. Europe Standard Time","Europe/Vienna","Vienna"
"FLE Standard Time","Europe/Vilnius","Vilnius"
"Russian Standard Time","Europe/Volgograd","Volgograd"
"Central European Standard Time","Europe/Warsaw","Warsaw"
"Central European Standard Time","Europe/Zagreb","Zagreb"
"Samoa Standard Time","Pacific/Apia","Samoa"
"New Zealand Standard Time","Pacific/Auckland","Auckland"
"New Zealand Standard Time","Pacific/Auckland","Wellington"
"Tonga Standard Time","Pacific/Fakaofo","Tokelau Is."
"Fiji Standard Time","Pacific/Fiji","Fiji"
"Central Pacific Standard Time","Pacific/Guadalcanal","Solomon Is."
"West Pacific Standard Time","Pacific/Guam","Guam"
"Hawaiian Standard Time","Pacific/Honolulu","Hawaii"
"UTC+12","Pacific/Majuro","Marshall Is."
"UTC-11","Pacific/Midway","International Date Line West"
"UTC-11","Pacific/Midway","Midway Island"
"Central Pacific Standard Time","Pacific/Noumea","New Caledonia"
"UTC-11","Pacific/Pago_Pago","American Samoa"
"West Pacific Standard Time","Pacific/Port_Moresby","Port Moresby"
"Tonga Standard Time","Pacific/Tongatapu","Nuku'alofa"

Note that the above list includes entries where more than one Rails zone maps back to the same Windows zone. You may want to pick just one of these when mapping in the Windows-to-Rails direction.

Also, the above list does not include the zones that could not be mapped, which are as follows (CSV):

"Windows","IANA","Rails"
"","Pacific/Chatham","Chatham Is."
"Dateline Standard Time","Etc/GMT+12",""
"Pacific Standard Time (Mexico)","America/Santa_Isabel",""
"Eastern Standard Time (Mexico)","America/Cancun",""
"Paraguay Standard Time","America/Asuncion",""
"Central Brazilian Standard Time","America/Cuiaba",""
"SA Eastern Standard Time","America/Cayenne",""
"Bahia Standard Time","America/Bahia",""
"Namibia Standard Time","Africa/Windhoek",""
"Jordan Standard Time","Asia/Amman",""
"Middle East Standard Time","Asia/Beirut",""
"Syria Standard Time","Asia/Damascus",""
"E. Europe Standard Time","Etc/GMT-2",""
"Libya Standard Time","Africa/Tripoli",""
"Mauritius Standard Time","Indian/Mauritius",""
"Line Islands Standard Time","Pacific/Kiritimati",""

Answer 2

Thanks to @MattJohnson-Pint I were able to solve this problem after a very long research, however I used a simpler solution.

So I have a Rails app that needs to deal with iCalendar files coming from different Mail servers (Outlook too), which caused a problem trying to interpret Microsoft time zones.

I'm not able to post my complete code here due to property issues, but that's how it goes:

1) Download the windowsZone.xml mapping file from the CLDR project master at every start of the program. 2) While verifying a time zone String, check the Ruby time zones first:

ActiveSupport::TimeZone.all.any? { |tz| tz.name == tzid }

3) Then check the IANA time zone from the TZInfo gem:

TZInfo::TimezoneProxy.all.any? { |tz| tz.identifier == tzid }

4) If you do have a Microsoft time zone, check your mapping file, Nokogiri::XML is very helpful.

5) Otherwise send an error.

And this seems to work fine, I hope I were able to help, in case someone faces this problem in 2020+ :)