- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在使用 T-SQL 查询从 Azure SQL 数据库检索数据以实现客户数据可视化。
我需要一种使用 IANA 时区
的方法,例如。 Europe/Copenhagen
转换 UTC 时间
例如。 10-08-2020 12:32:00
到 本地时间
例如。 10-08-2020 14:32:00
在 SQL
中。
(注意UTC时间加了两个小时,因为当时UTC到哥本哈根的时差是夏令时两个小时。)
official IANA website有一个downloadable timezone database ,但我不明白如何实现它,特别是在 SQL 上下文中。
我花了很多时间试图破解这个挑战,我欢迎任何有用的输入。 Microsoft 生态系统中似乎不支持 IANA 时区标准。
最佳答案
有点晚了,但对我来说,我通过在窗口和时区数据库之间进行映射来解决。
后面的SQL结果是一个映射。有一个列名称 SQL_Names 应用与 [sys].[Time_Zone_Info] 表的连接以获取 SQL 时区详细信息,下面是示例。
注意:由于此处的字符限制,JSON 并未涵盖所有时区,但您可以从以下链接导入完整列表。
尝试:
DECLARE @TZ_Json VARCHAR(MAX)
SET @TZ_Json = '[{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Abidjan","TZTerritory":"CI"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Accra","TZTerritory":"GH"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Addis_Ababa","TZTerritory":"ET"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Algiers","TZTerritory":"DZ"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Asmera","TZTerritory":"ER"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Bamako","TZTerritory":"ML"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Bangui","TZTerritory":"CF"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Banjul","TZTerritory":"GM"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Bissau","TZTerritory":"GW"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Blantyre","TZTerritory":"MW"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Brazzaville","TZTerritory":"CG"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Bujumbura","TZTerritory":"BI"},{"SQLName":"Egypt Standard Time","TZName":"Africa\/Cairo","TZTerritory":"001"},{"SQLName":"Egypt Standard Time","TZName":"Africa\/Cairo","TZTerritory":"EG"},{"SQLName":"Morocco Standard Time","TZName":"Africa\/Casablanca","TZTerritory":"001"},{"SQLName":"Morocco Standard Time","TZName":"Africa\/Casablanca","TZTerritory":"MA"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Conakry","TZTerritory":"GN"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Dakar","TZTerritory":"SN"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Dar_es_Salaam","TZTerritory":"TZ"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Djibouti","TZTerritory":"DJ"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Douala","TZTerritory":"CM"},{"SQLName":"Morocco Standard Time","TZName":"Africa\/El_Aaiun","TZTerritory":"EH"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Freetown","TZTerritory":"SL"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Gaborone","TZTerritory":"BW"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Harare","TZTerritory":"ZW"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Johannesburg","TZTerritory":"ZA"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Johannesburg","TZTerritory":"001"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Juba","TZTerritory":"SS"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Kampala","TZTerritory":"UG"},{"SQLName":"Sudan Standard Time","TZName":"Africa\/Khartoum","TZTerritory":"001"},{"SQLName":"Sudan Standard Time","TZName":"Africa\/Khartoum","TZTerritory":"SD"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Kigali","TZTerritory":"RW"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Kinshasa","TZTerritory":"CD"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Lagos","TZTerritory":"001"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Lagos","TZTerritory":"NG"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Libreville","TZTerritory":"GA"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Lome","TZTerritory":"TG"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Luanda","TZTerritory":"AO"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Lubumbashi","TZTerritory":"CD"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Lusaka","TZTerritory":"ZM"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Malabo","TZTerritory":"GQ"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Maputo","TZTerritory":"MZ"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Maseru","TZTerritory":"LS"},{"SQLName":"South Africa Standard Time","TZName":"Africa\/Mbabane","TZTerritory":"SZ"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Mogadishu","TZTerritory":"SO"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Monrovia","TZTerritory":"LR"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Nairobi","TZTerritory":"001"},{"SQLName":"E. Africa Standard Time","TZName":"Africa\/Nairobi","TZTerritory":"KE"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Ndjamena","TZTerritory":"TD"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Niamey","TZTerritory":"NE"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Nouakchott","TZTerritory":"MR"},{"SQLName":"Greenwich Standard Time","TZName":"Africa\/Ouagadougou","TZTerritory":"BF"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Porto-Novo","TZTerritory":"BJ"},{"SQLName":"Sao Tome Standard Time","TZName":"Africa\/Sao_Tome","TZTerritory":"001"},{"SQLName":"Sao Tome Standard Time","TZName":"Africa\/Sao_Tome","TZTerritory":"ST"},{"SQLName":"Libya Standard Time","TZName":"Africa\/Tripoli","TZTerritory":"001"},{"SQLName":"Libya Standard Time","TZName":"Africa\/Tripoli","TZTerritory":"LY"},{"SQLName":"W. Central Africa Standard Time","TZName":"Africa\/Tunis","TZTerritory":"TN"},{"SQLName":"Namibia Standard Time","TZName":"Africa\/Windhoek","TZTerritory":"001"},{"SQLName":"Namibia Standard Time","TZName":"Africa\/Windhoek","TZTerritory":"NA"},{"SQLName":"Aleutian Standard Time","TZName":"America\/Adak","TZTerritory":"001"},{"SQLName":"Aleutian Standard Time","TZName":"America\/Adak","TZTerritory":"US"},{"SQLName":"Alaskan Standard Time","TZName":"America\/Anchorage","TZTerritory":"001"},{"SQLName":"Alaskan Standard Time","TZName":"America\/Anchorage America\/Juneau America\/Metlakatla America\/Nome America\/Sitka America\/Yakutat","TZTerritory":"US"},{"SQLName":"SA Western Standard Time","TZName":"America\/Anguilla","TZTerritory":"AI"},{"SQLName":"SA Western Standard Time","TZName":"America\/Antigua","TZTerritory":"AG"},{"SQLName":"Tocantins Standard Time","TZName":"America\/Araguaina","TZTerritory":"001"},{"SQLName":"Tocantins Standard Time","TZName":"America\/Araguaina","TZTerritory":"BR"},{"SQLName":"SA Western Standard Time","TZName":"America\/Aruba","TZTerritory":"AW"},{"SQLName":"Paraguay Standard Time","TZName":"America\/Asuncion","TZTerritory":"001"},{"SQLName":"Paraguay Standard Time","TZName":"America\/Asuncion","TZTerritory":"PY"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"Bahia Standard Time","TZName":"America\/Bahia","TZTerritory":"001"},{"SQLName":"Bahia Standard Time","TZName":"America\/Bahia","TZTerritory":"BR"},{"SQLName":"SA Western Standard Time","TZName":"America\/Barbados","TZTerritory":"BB"},{"SQLName":"Central America Standard Time","TZName":"America\/Belize","TZTerritory":"BZ"},{"SQLName":"SA Western Standard Time","TZName":"America\/Blanc-Sablon","TZTerritory":"CA"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Bogota","TZTerritory":"CO"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Bogota","TZTerritory":"001"},{"SQLName":"Argentina Standard Time","TZName":"America\/Buenos_Aires","TZTerritory":"001"},{"SQLName":"Argentina Standard Time","TZName":"America\/Buenos_Aires America\/Argentina\/La_Rioja America\/Argentina\/Rio_Gallegos America\/Argentina\/Salta America\/Argentina\/San_Juan America\/Argentina\/San_Luis America\/Argentina\/Tucuman America\/Argentina\/Ushuaia America\/Catamarca America\/Cordoba America\/Juju","TZTerritory":"AR"},{"SQLName":"Eastern Standard Time (Mexico)","TZName":"America\/Cancun","TZTerritory":"001"},{"SQLName":"Eastern Standard Time (Mexico)","TZName":"America\/Cancun","TZTerritory":"MX"},{"SQLName":"Venezuela Standard Time","TZName":"America\/Caracas","TZTerritory":"001"},{"SQLName":"Venezuela Standard Time","TZName":"America\/Caracas","TZTerritory":"VE"},{"SQLName":"SA Eastern Standard Time","TZName":"America\/Cayenne","TZTerritory":"001"},{"SQLName":"SA Eastern Standard Time","TZName":"America\/Cayenne","TZTerritory":"GF"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Cayman","TZTerritory":"KY"},{"SQLName":"Central Standard Time","TZName":"America\/Chicago","TZTerritory":"001"},{"SQLName":"Central Standard Time","TZName":"America\/Chicago America\/Indiana\/Knox America\/Indiana\/Tell_City America\/Menominee America\/North_Dakota\/Beulah America\/North_Dakota\/Center America\/North_Dakota\/New_Salem","TZTerritory":"US"},{"SQLName":"Mountain Standard Time (Mexico)","TZName":"America\/Chihuahua","TZTerritory":"001"},{"SQLName":"Mountain Standard Time (Mexico)","TZName":"America\/Chihuahua America\/Mazatlan","TZTerritory":"MX"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Coral_Harbour","TZTerritory":"CA"},{"SQLName":"Central America Standard Time","TZName":"America\/Costa_Rica","TZTerritory":"CR"},{"SQLName":"US Mountain Standard Time","TZName":"America\/Creston America\/Dawson_Creek America\/Fort_Nelson","TZTerritory":"CA"},{"SQLName":"Central Brazilian Standard Time","TZName":"America\/Cuiaba","TZTerritory":"001"},{"SQLName":"Central Brazilian Standard Time","TZName":"America\/Cuiaba America\/Campo_Grande","TZTerritory":"BR"},{"SQLName":"SA Western Standard Time","TZName":"America\/Curacao","TZTerritory":"CW"},{"SQLName":"UTC","TZName":"America\/Danmarkshavn","TZTerritory":"GL"},{"SQLName":"Mountain Standard Time","TZName":"America\/Denver","TZTerritory":"001"},{"SQLName":"Mountain Standard Time","TZName":"America\/Denver America\/Boise","TZTerritory":"US"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"SA Western Standard Time","TZName":"America\/Dominica","TZTerritory":"DM"},{"SQLName":"Mountain Standard Time","TZName":"America\/Edmonton America\/Cambridge_Bay America\/Inuvik America\/Yellowknife","TZTerritory":"CA"},{"SQLName":"Central America Standard Time","TZName":"America\/El_Salvador","TZTerritory":"SV"},{"SQLName":"SA Eastern Standard Time","TZName":"America\/Fortaleza America\/Belem America\/Maceio America\/Recife America\/Santarem","TZTerritory":"BR"},{"SQLName":"Greenland Standard Time","TZName":"America\/Godthab","TZTerritory":"001"},{"SQLName":"Greenland Standard Time","TZName":"America\/Godthab","TZTerritory":"GL"},{"SQLName":"Turks And Caicos Standard Time","TZName":"America\/Grand_Turk","TZTerritory":"001"},{"SQLName":"Turks And Caicos Standard Time","TZName":"America\/Grand_Turk","TZTerritory":"TC"},{"SQLName":"SA Western Standard Time","TZName":"America\/Grenada","TZTerritory":"GD"},{"SQLName":"SA Western Standard Time","TZName":"America\/Guadeloupe","TZTerritory":"GP"},{"SQLName":"Central America Standard Time","TZName":"America\/Guatemala","TZTerritory":"GT"},{"SQLName":"Central America Standard Time","TZName":"America\/Guatemala","TZTerritory":"001"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Guayaquil","TZTerritory":"EC"},{"SQLName":"SA Western Standard Time","TZName":"America\/Guyana","TZTerritory":"GY"},{"SQLName":"Atlantic Standard Time","TZName":"America\/Halifax","TZTerritory":"001"},{"SQLName":"Atlantic Standard Time","TZName":"America\/Halifax America\/Glace_Bay America\/Goose_Bay America\/Moncton","TZTerritory":"CA"},{"SQLName":"Cuba Standard Time","TZName":"America\/Havana","TZTerritory":"001"},{"SQLName":"Cuba Standard Time","TZName":"America\/Havana","TZTerritory":"CU"},{"SQLName":"US Mountain Standard Time","TZName":"America\/Hermosillo","TZTerritory":"MX"},{"SQLName":"US Eastern Standard Time","TZName":"America\/Indianapolis","TZTerritory":"001"},{"SQLName":"US Eastern Standard Time","TZName":"America\/Indianapolis America\/Indiana\/Marengo America\/Indiana\/Vevay","TZTerritory":"US"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Jamaica","TZTerritory":"JM"},{"SQLName":"SA Western Standard Time","TZName":"America\/Kralendijk","TZTerritory":"BQ"},{"SQLName":"SA Western Standard Time","TZName":"America\/La_Paz","TZTerritory":"BO"},{"SQLName":"SA Western Standard Time","TZName":"America\/La_Paz","TZTerritory":"001"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Lima","TZTerritory":"PE"},{"SQLName":"Pacific Standard Time","TZName":"America\/Los_Angeles","TZTerritory":"001"},{"SQLName":"Pacific Standard Time","TZName":"America\/Los_Angeles","TZTerritory":"US"},{"SQLName":"SA Western Standard Time","TZName":"America\/Lower_Princes","TZTerritory":"SX"},{"SQLName":"Central America Standard Time","TZName":"America\/Managua","TZTerritory":"NI"},{"SQLName":"SA Western Standard Time","TZName":"America\/Manaus America\/Boa_Vista America\/Porto_Velho","TZTerritory":"BR"},{"SQLName":"SA Western Standard Time","TZName":"America\/Marigot","TZTerritory":"MF"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"SA Western Standard Time","TZName":"America\/Martinique","TZTerritory":"MQ"},{"SQLName":"Central Standard Time","TZName":"America\/Matamoros","TZTerritory":"MX"},{"SQLName":"Central Standard Time (Mexico)","TZName":"America\/Mexico_City","TZTerritory":"001"},{"SQLName":"Central Standard Time (Mexico)","TZName":"America\/Mexico_City America\/Bahia_Banderas America\/Merida America\/Monterrey","TZTerritory":"MX"},{"SQLName":"Saint Pierre Standard Time","TZName":"America\/Miquelon","TZTerritory":"001"},{"SQLName":"Saint Pierre Standard Time","TZName":"America\/Miquelon","TZTerritory":"PM"},{"SQLName":"Montevideo Standard Time","TZName":"America\/Montevideo","TZTerritory":"001"},{"SQLName":"Montevideo Standard Time","TZName":"America\/Montevideo","TZTerritory":"UY"},{"SQLName":"SA Western Standard Time","TZName":"America\/Montserrat","TZTerritory":"MS"},{"SQLName":"Eastern Standard Time","TZName":"America\/Nassau","TZTerritory":"BS"},{"SQLName":"Eastern Standard Time","TZName":"America\/New_York","TZTerritory":"001"},{"SQLName":"Eastern Standard Time","TZName":"America\/New_York America\/Detroit America\/Indiana\/Petersburg America\/Indiana\/Vincennes America\/Indiana\/Winamac America\/Kentucky\/Monticello America\/Louisville","TZTerritory":"US"},{"SQLName":"UTC-02","TZName":"America\/Noronha","TZTerritory":"BR"},{"SQLName":"Mountain Standard Time","TZName":"America\/Ojinaga","TZTerritory":"MX"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Panama","TZTerritory":"PA"},{"SQLName":"SA Eastern Standard Time","TZName":"America\/Paramaribo","TZTerritory":"SR"},{"SQLName":"US Mountain Standard Time","TZName":"America\/Phoenix","TZTerritory":"001"},{"SQLName":"US Mountain Standard Time","TZName":"America\/Phoenix","TZTerritory":"US"},{"SQLName":"SA Western Standard Time","TZName":"America\/Port_of_Spain","TZTerritory":"TT"},{"SQLName":"Haiti Standard Time","TZName":"America\/Port-au-Prince","TZTerritory":"001"},{"SQLName":"Haiti Standard Time","TZName":"America\/Port-au-Prince","TZTerritory":"HT"},{"SQLName":"SA Western Standard Time","TZName":"America\/Puerto_Rico","TZTerritory":"PR"},{"SQLName":"Magallanes Standard Time","TZName":"America\/Punta_Arenas","TZTerritory":"001"},{"SQLName":"Magallanes Standard Time","TZName":"America\/Punta_Arenas","TZTerritory":"CL"},{"SQLName":"Canada Central Standard Time","TZName":"America\/Regina","TZTerritory":"001"},{"SQLName":"Canada Central Standard Time","TZName":"America\/Regina America\/Swift_Current","TZTerritory":"CA"},{"SQLName":"SA Pacific Standard Time","TZName":"America\/Rio_Branco America\/Eirunepe","TZTerritory":"BR"},{"SQLName":"Pacific SA Standard Time","TZName":"America\/Santiago","TZTerritory":"001"},{"SQLName":"Pacific SA Standard Time","TZName":"America\/Santiago","TZTerritory":"CL"},{"SQLName":"SA Western Standard Time","TZName":"America\/Santo_Domingo","TZTerritory":"DO"},{"SQLName":"E. South America Standard Time","TZName":"America\/Sao_Paulo","TZTerritory":"001"},{"SQLName":"E. South America Standard Time","TZName":"America\/Sao_Paulo","TZTerritory":"BR"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"Azores Standard Time","TZName":"America\/Scoresbysund","TZTerritory":"GL"},{"SQLName":"SA Western Standard Time","TZName":"America\/St_Barthelemy","TZTerritory":"BL"},{"SQLName":"Newfoundland Standard Time","TZName":"America\/St_Johns","TZTerritory":"001"},{"SQLName":"Newfoundland Standard Time","TZName":"America\/St_Johns","TZTerritory":"CA"},{"SQLName":"SA Western Standard Time","TZName":"America\/St_Kitts","TZTerritory":"KN"},{"SQLName":"SA Western Standard Time","TZName":"America\/St_Lucia","TZTerritory":"LC"},{"SQLName":"SA Western Standard Time","TZName":"America\/St_Thomas","TZTerritory":"VI"},{"SQLName":"SA Western Standard Time","TZName":"America\/St_Vincent","TZTerritory":"VC"},{"SQLName":"Central America Standard Time","TZName":"America\/Tegucigalpa","TZTerritory":"HN"},{"SQLName":"Atlantic Standard Time","TZName":"America\/Thule","TZTerritory":"GL"},{"SQLName":"Pacific Standard Time (Mexico)","TZName":"America\/Tijuana","TZTerritory":"001"},{"SQLName":"Pacific Standard Time (Mexico)","TZName":"America\/Tijuana America\/Santa_Isabel","TZTerritory":"MX"},{"SQLName":"Eastern Standard Time","TZName":"America\/Toronto America\/Iqaluit America\/Montreal America\/Nipigon America\/Pangnirtung America\/Thunder_Bay","TZTerritory":"CA"},{"SQLName":"SA Western Standard Time","TZName":"America\/Tortola","TZTerritory":"VG"},{"SQLName":"Pacific Standard Time","TZName":"America\/Vancouver","TZTerritory":"CA"},{"SQLName":"Yukon Standard Time","TZName":"America\/Whitehorse","TZTerritory":"001"},{"SQLName":"Yukon Standard Time","TZName":"America\/Whitehorse America\/Dawson","TZTerritory":"CA"},{"SQLName":"Central Standard Time","TZName":"America\/Winnipeg America\/Rainy_River America\/Rankin_Inlet America\/Resolute","TZTerritory":"CA"},{"SQLName":"Central Pacific Standard Time","TZName":"Antarctica\/Casey","TZTerritory":"AQ"},{"SQLName":"SE Asia Standard Time","TZName":"Antarctica\/Davis","TZTerritory":"AQ"},{"SQLName":"West Pacific Standard Time","TZName":"Antarctica\/DumontDUrville","TZTerritory":"AQ"},{"SQLName":"West Asia Standard Time","TZName":"Antarctica\/Mawson","TZTerritory":"AQ"},{"SQLName":"New Zealand Standard Time","TZName":"Antarctica\/McMurdo","TZTerritory":"AQ"},{"SQLName":"SA Eastern Standard Time","TZName":"Antarctica\/Rothera Antarctica\/Palmer","TZTerritory":"AQ"},{"SQLName":"E. Africa Standard Time","TZName":"Antarctica\/Syowa","TZTerritory":"AQ"},{"SQLName":"Central Asia Standard Time","TZName":"Antarctica\/Vostok","TZTerritory":"AQ"},{"SQLName":"W. Europe Standard Time","TZName":"Arctic\/Longyearbyen","TZTerritory":"SJ"},{"SQLName":"Arab Standard Time","TZName":"Asia\/Aden","TZTerritory":"YE"},{"SQLName":"Central Asia Standard Time","TZName":"Asia\/Almaty","TZTerritory":"001"},{"SQLName":"Central Asia Standard Time","TZName":"Asia\/Almaty Asia\/Qostanay","TZTerritory":"KZ"},{"SQLName":"Jordan Standard Time","TZName":"Asia\/Amman","TZTerritory":"001"},{"SQLName":"Jordan Standard Time","TZName":"Asia\/Amman","TZTerritory":"JO"},{"SQLName":"West Asia Standard Time","TZName":"Asia\/Ashgabat","TZTerritory":"TM"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"Arabic Standard Time","TZName":"Asia\/Baghdad","TZTerritory":"001"},{"SQLName":"Arabic Standard Time","TZName":"Asia\/Baghdad","TZTerritory":"IQ"},{"SQLName":"Arab Standard Time","TZName":"Asia\/Bahrain","TZTerritory":"BH"},{"SQLName":"Azerbaijan Standard Time","TZName":"Asia\/Baku","TZTerritory":"001"},{"SQLName":"Azerbaijan Standard Time","TZName":"Asia\/Baku","TZTerritory":"AZ"},{"SQLName":"SE Asia Standard Time","TZName":"Asia\/Bangkok","TZTerritory":"TH"},{"SQLName":"SE Asia Standard Time","TZName":"Asia\/Bangkok","TZTerritory":"001"},{"SQLName":"Altai Standard Time","TZName":"Asia\/Barnaul","TZTerritory":"001"},{"SQLName":"Altai Standard Time","TZName":"Asia\/Barnaul","TZTerritory":"RU"},{"SQLName":"Middle East Standard Time","TZName":"Asia\/Beirut","TZTerritory":"001"},{"SQLName":"Middle East Standard Time","TZName":"Asia\/Beirut","TZTerritory":"LB"},{"SQLName":"Central Asia Standard Time","TZName":"Asia\/Bishkek","TZTerritory":"KG"},{"SQLName":"Singapore Standard Time","TZName":"Asia\/Brunei","TZTerritory":"BN"},{"SQLName":"India Standard Time","TZName":"Asia\/Calcutta","TZTerritory":"001"},{"SQLName":"India Standard Time","TZName":"Asia\/Calcutta","TZTerritory":"IN"},{"SQLName":"Transbaikal Standard Time","TZName":"Asia\/Chita","TZTerritory":"001"},{"SQLName":"Transbaikal Standard Time","TZName":"Asia\/Chita","TZTerritory":"RU"},{"SQLName":"Sri Lanka Standard Time","TZName":"Asia\/Colombo","TZTerritory":"001"},{"SQLName":"Sri Lanka Standard Time","TZName":"Asia\/Colombo","TZTerritory":"LK"},{"SQLName":"Syria Standard Time","TZName":"Asia\/Damascus","TZTerritory":"001"},{"SQLName":"Syria Standard Time","TZName":"Asia\/Damascus","TZTerritory":"SY"},{"SQLName":"Bangladesh Standard Time","TZName":"Asia\/Dhaka","TZTerritory":"001"},{"SQLName":"Bangladesh Standard Time","TZName":"Asia\/Dhaka","TZTerritory":"BD"},{"SQLName":"Tokyo Standard Time","TZName":"Asia\/Dili","TZTerritory":"TL"},{"SQLName":"Arabian Standard Time","TZName":"Asia\/Dubai","TZTerritory":"001"},{"SQLName":"Arabian Standard Time","TZName":"Asia\/Dubai","TZTerritory":"AE"},{"SQLName":"West Asia Standard Time","TZName":"Asia\/Dushanbe","TZTerritory":"TJ"},{"SQLName":"West Bank Standard Time","TZName":"Asia\/Hebron","TZTerritory":"001"},{"SQLName":"West Bank Standard Time","TZName":"Asia\/Hebron Asia\/Gaza","TZTerritory":"PS"},{"SQLName":"China Standard Time","TZName":"Asia\/Hong_Kong","TZTerritory":"HK"},{"SQLName":"W. Mongolia Standard Time","TZName":"Asia\/Hovd","TZTerritory":"001"},{"SQLName":"W. Mongolia Standard Time","TZName":"Asia\/Hovd","TZTerritory":"MN"},{"SQLName":"North Asia East Standard Time","TZName":"Asia\/Irkutsk","TZTerritory":"001"},{"SQLName":"North Asia East Standard Time","TZName":"Asia\/Irkutsk","TZTerritory":"RU"},{"SQLName":"SE Asia Standard Time","TZName":"Asia\/Jakarta Asia\/Pontianak","TZTerritory":"ID"},{"SQLName":"Tokyo Standard Time","TZName":"Asia\/Jayapura","TZTerritory":"ID"},{"SQLName":"Israel Standard Time","TZName":"Asia\/Jerusalem","TZTerritory":"001"},{"SQLName":"Israel Standard Time","TZName":"Asia\/Jerusalem","TZTerritory":"IL"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"Afghanistan Standard Time","TZName":"Asia\/Kabul","TZTerritory":"001"},{"SQLName":"Afghanistan Standard Time","TZName":"Asia\/Kabul","TZTerritory":"AF"},{"SQLName":"Russia Time Zone 11","TZName":"Asia\/Kamchatka","TZTerritory":"001"},{"SQLName":"Russia Time Zone 11","TZName":"Asia\/Kamchatka Asia\/Anadyr","TZTerritory":"RU"},{"SQLName":"Pakistan Standard Time","TZName":"Asia\/Karachi","TZTerritory":"001"},{"SQLName":"Pakistan Standard Time","TZName":"Asia\/Karachi","TZTerritory":"PK"},{"SQLName":"Nepal Standard Time","TZName":"Asia\/Katmandu","TZTerritory":"001"},{"SQLName":"Nepal Standard Time","TZName":"Asia\/Katmandu","TZTerritory":"NP"},{"SQLName":"North Asia Standard Time","TZName":"Asia\/Krasnoyarsk","TZTerritory":"001"},{"SQLName":"North Asia Standard Time","TZName":"Asia\/Krasnoyarsk Asia\/Novokuznetsk","TZTerritory":"RU"},{"SQLName":"Singapore Standard Time","TZName":"Asia\/Kuala_Lumpur Asia\/Kuching","TZTerritory":"MY"},{"SQLName":"Arab Standard Time","TZName":"Asia\/Kuwait","TZTerritory":"KW"},{"SQLName":"China Standard Time","TZName":"Asia\/Macau","TZTerritory":"MO"},{"SQLName":"Magadan Standard Time","TZName":"Asia\/Magadan","TZTerritory":"001"},{"SQLName":"Magadan Standard Time","TZName":"Asia\/Magadan","TZTerritory":"RU"},{"SQLName":"Singapore Standard Time","TZName":"Asia\/Makassar","TZTerritory":"ID"},{"SQLName":"Singapore Standard Time","TZName":"Asia\/Manila","TZTerritory":"PH"},{"SQLName":"Arabian Standard Time","TZName":"Asia\/Muscat","TZTerritory":"OM"},{"SQLName":"GTB Standard Time","TZName":"Asia\/Nicosia Asia\/Famagusta","TZTerritory":"CY"},{"SQLName":"N. Central Asia Standard Time","TZName":"Asia\/Novosibirsk","TZTerritory":"001"},{"SQLName":"N. Central Asia Standard Time","TZName":"Asia\/Novosibirsk","TZTerritory":"RU"},{"SQLName":"Omsk Standard Time","TZName":"Asia\/Omsk","TZTerritory":"001"},{"SQLName":"Omsk Standard Time","TZName":"Asia\/Omsk","TZTerritory":"RU"},{"SQLName":"West Asia Standard Time","TZName":"Asia\/Oral Asia\/Aqtau Asia\/Aqtobe Asia\/Atyrau","TZTerritory":"KZ"},{"SQLName":"SE Asia Standard Time","TZName":"Asia\/Phnom_Penh","TZTerritory":"KH"},{"SQLName":"North Korea Standard Time","TZName":"Asia\/Pyongyang","TZTerritory":"001"},{"SQLName":"North Korea Standard Time","TZName":"Asia\/Pyongyang","TZTerritory":"KP"},{"SQLName":"Arab Standard Time","TZName":"Asia\/Qatar","TZTerritory":"QA"},{"SQLName":"Qyzylorda Standard Time","TZName":"Asia\/Qyzylorda","TZTerritory":"001"},{"SQLName":"Qyzylorda Standard Time","TZName":"Asia\/Qyzylorda","TZTerritory":"KZ"},{"SQLName":"Myanmar Standard Time","TZName":"Asia\/Rangoon","TZTerritory":"001"},{"SQLName":"Myanmar Standard Time","TZName":"Asia\/Rangoon","TZTerritory":"MM"},{"SQLName":"Arab Standard Time","TZName":"Asia\/Riyadh","TZTerritory":"SA"},{"SQLName":"Arab Standard Time","TZName":"Asia\/Riyadh","TZTerritory":"001"},{"SQLName":"SE Asia Standard Time","TZName":"Asia\/Saigon","TZTerritory":"VN"},{"SQLName":"Sakhalin Standard Time","TZName":"Asia\/Sakhalin","TZTerritory":"001"},{"SQLName":"Sakhalin Standard Time","TZName":"Asia\/Sakhalin","TZTerritory":"RU"},'
SET @TZ_Json = @TZ_Json + '{"SQLName":"Korea Standard Time","TZName":"Asia\/Seoul","TZTerritory":"001"},{"SQLName":"Korea Standard Time","TZName":"Asia\/Seoul","TZTerritory":"KR"},{"SQLName":"China Standard Time","TZName":"Asia\/Shanghai","TZTerritory":"001"},{"SQLName":"China Standard Time","TZName":"Asia\/Shanghai","TZTerritory":"CN"},{"SQLName":"Singapore Standard Time","TZName":"Asia\/Singapore","TZTerritory":"SG"},{"SQLName":"Singapore Standard Time","TZName":"Asia\/Singapore","TZTerritory":"001"},{"SQLName":"Russia Time Zone 10","TZName":"Asia\/Srednekolymsk","TZTerritory":"001"},{"SQLName":"Russia Time Zone 10","TZName":"Asia\/Srednekolymsk","TZTerritory":"RU"},{"SQLName":"Taipei Standard Time","TZName":"Asia\/Taipei","TZTerritory":"001"},{"SQLName":"Taipei Standard Time","TZName":"Asia\/Taipei","TZTerritory":"TW"},{"SQLName":"West Asia Standard Time","TZName":"Asia\/Tashkent","TZTerritory":"001"},{"SQLName":"West Asia Standard Time","TZName":"Asia\/Tashkent Asia\/Samarkand","TZTerritory":"UZ"},{"SQLName":"Georgian Standard Time","TZName":"Asia\/Tbilisi","TZTerritory":"001"},{"SQLName":"Georgian Standard Time","TZName":"Asia\/Tbilisi","TZTerritory":"GE"},{"SQLName":"Iran Standard Time","TZName":"Asia\/Tehran","TZTerritory":"001"},{"SQLName":"Iran Standard Time","TZName":"Asia\/Tehran","TZTerritory":"IR"},{"SQLName":"Bangladesh Standard Time","TZName":"Asia\/Thimphu","TZTerritory":"BT"},{"SQLName":"Tokyo Standard Time","TZName":"Asia\/Tokyo","TZTerritory":"001"},{"SQLName":"Tokyo Standard Time","TZName":"Asia\/Tokyo","TZTerritory":"JP"},{"SQLName":"Tomsk Standard Time","TZName":"Asia\/Tomsk","TZTerritory":"001"},{"SQLName":"Tomsk Standard Time","TZName":"Asia\/Tomsk","TZTerritory":"RU"},{"SQLName":"Ulaanbaatar Standard Time","TZName":"Asia\/Ulaanbaatar","TZTerritory":"001"},{"SQLName":"Ulaanbaatar Standard Time","TZName":"Asia\/Ulaanbaatar Asia\/Choibalsan","TZTerritory":"MN"},{"SQLName":"Central Asia Standard Time","TZName":"Asia\/Urumqi","TZTerritory":"CN"},{"SQLName":"SE Asia Standard Time","TZName":"Asia\/Vientiane","TZTerritory":"LA"},{"SQLName":"Vladivostok Standard Time","TZName":"Asia\/Vladivostok","TZTerritory":"001"},{"SQLName":"Vladivostok Standard Time","TZName":"Asia\/Vladivostok Asia\/Ust-Nera","TZTerritory":"RU"},{"SQLName":"Yakutsk Standard Time","TZName":"Asia\/Yakutsk","TZTerritory":"001"},{"SQLName":"Yakutsk Standard Time","TZName":"Asia\/Yakutsk Asia\/Khandyga","TZTerritory":"RU"},{"SQLName":"Ekaterinburg Standard Time","TZName":"Asia\/Yekaterinburg","TZTerritory":"001"},{"SQLName":"Ekaterinburg Standard Time","TZName":"Asia\/Yekaterinburg","TZTerritory":"RU"},{"SQLName":"Caucasus Standard Time","TZName":"Asia\/Yerevan","TZTerritory":"001"},{"SQLName":"Caucasus Standard Time","TZName":"Asia\/Yerevan","TZTerritory":"AM"},{"SQLName":"Azores Standard Time","TZName":"Atlantic\/Azores","TZTerritory":"PT"},{"SQLName":"Azores Standard Time","TZName":"Atlantic\/Azores","TZTerritory":"001"},{"SQLName":"Atlantic Standard Time","TZName":"Atlantic\/Bermuda","TZTerritory":"BM"},{"SQLName":"GMT Standard Time","TZName":"Atlantic\/Canary","TZTerritory":"ES"}]'
/* To Get the full list refer windows-time-zone http link below */
SELECT j.TZName,j.TZTerritory, j.SQLName, tz.[current_utc_offset],tz.[is_currently_dst]
FROM (
SELECT SQLName,TZName,TZTerritory FROM OPENJSON(@TZ_Json)
WITH (
SQLName VARCHAR(200) '$.SQLName',
TZName VARCHAR(200) '$.TZName',
TZTerritory VARCHAR(200) '$.TZTerritory')
)j INNER JOIN sys.[time_zone_info]tz ON j.[SQLName]=tz.[name]
此外,请引用以下链接:
关于sql - 在 SQL 中使用官方 IANA 时区数据库需要帮助,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/63340460/
我正在尝试为用户找到一个用户友好的时区列表 选择他们当前的时区。时区将具有通用名称,例如 太平洋时间(美国和加拿大) . 此列表应将文本映射到 IANA 标准名称,例如 美国/洛杉矶 . 它还将删除用
我们希望在我们的网站上将 Windows 时区转换为 OLSON 时区。然而,奥尔森时区的问题在于它们使用大陆来命名而不是国家/地区名称,例如: America/New_York Asia/Kolka
我在应用程序中使用 NodaTime,我需要用户从下拉列表中选择他们的时区。我有以下软要求: 1) 该列表仅包含对当前和不久的将来真实地点合理有效的选择。应过滤掉历史时区、模糊时区和通用时区。 2)
我们想在我们的网站上将 windows 时区转换为 OLSON 时区。然而,OLSON 时区的问题在于它们使用大洲而不是国家名称来命名,例如: America/New_York Asia/Kolkat
IANA 数据库和 Java tzdb.dat 2019c 之间存在夏令时差异 非洲/卡萨布兰卡时区的数据库让我很困惑。可能还有其他的,但我找到了这个。据我所知,IANA 时区数据库清楚地表明摩洛哥(
我有一个多人游戏软件,它接近成熟,有望很快进入公开测试阶段。对于非正式的私有(private)测试,我一直在使用我喜欢的端口号,它属于用户端口范围,1024-49151。我想知道我是否应该向 IANA
我正在尝试将来自前端的值映射到 ZoneId 类,如下所示: Optional.ofNullable(timeZone).map(ZoneId::of).orElse(null) 对于大多数时区,它工
如果指定 IANA,Javascript 允许您查看另一个时区的时间该时区的给定名称。例如: let strTime = new Date().toLocaleString("en-US", {tim
我想知道在哪里可以访问 IANA 注册表? 是否在操作系统中实现? 应用程序如何知道要连接到哪个端口,确切地说是在何处比较端口号? 有些应用程序的端口号是硬编码的,有些是您输入的,但是比较的具体位置在
我正在开发一个支持多时区用户的 Nodejs 系统。目前,我正在使用 moment.tz.names()获取可用时区的列表。但我发现列表中的某些时区已被此 link 弃用.我还找到了this data
我在 webapp here 中看到关于处理时间的出色总结.但是,它没有明确解决以下情况。我想要一种基于 UTC + Location(iana 时区位置 - America/New_York)的日期
我正在使用 T-SQL 查询从 Azure SQL 数据库检索数据以实现客户数据可视化。 我需要一种使用 IANA 时区 的方法,例如。 Europe/Copenhagen 转换 UTC 时间 例如。
var easternTimeZone = DateTimeZoneProviders.Tzdb[timeZoneIdentifier]; 使用 NodaTime 我应该如何验证当字符串 timeZo
在我们的 ASP.NET MVC 5 应用程序中,我们有带有时区 ID (IANA) 的用户配置文件。 public class User { public int Id { get; set
给定两个不同的 IANA (aka "tz database" or "Olson") time zones ,如何确定它们是否代表同一区域? 例如,Asia/Kolkata 和Asia/Calcut
如 the timezone tag wiki 中所述,有两种不同风格的时区。 由 Microsoft 提供的用于 Windows 和 .Net 的 TimeZoneInfo类(在 Windows 上
如 the timezone tag wiki 中所述,有两种不同风格的时区。 由 Microsoft 提供的用于 Windows 和 .Net 的 TimeZoneInfo类(在 Windows 上
我有IANA时区代码,如“亚洲/加尔各答”,我需要在 JavaScript 中获取时区偏移量,如 -330。我无法获得时区偏移,我们将不胜感激。 最佳答案 使用 native Date.toLocal
尝试更新 2 台计算机上的 Java 时区(巴西不再遵守 DST)。Windows 运行良好,但我在 Ubuntu 中遇到了一些问题。 这是我所做的: 下载的最新时区 (tzdata2019c):ht
我使用向服务器发送一些元数据的旧设备。我需要操纵 UTC 格式的日期时间。我还获取设备位于 POSIX 格式的时区信息,例如。柏林: CET-1CEST,M3.5.0/2,M10.5.0/3 但是,像
我是一名优秀的程序员,十分优秀!