{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 Summer. ? 凉城 的问题《Converting Microsoft Timezone to standards timezon》','https://www.manongdao.com/q-637373.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
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: ''}
]
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
MAPPINGconstant 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
MAPPINGconstant, 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:
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):