Нужна помощь в использовании официальной базы данных часовых поясов IANA в SQL.

Я использую запросы T-SQL для извлечения данных из базы данных Azure SQL для визуализации данных клиентов.

Мне нужен способ использовать IANA timezone, например. Europe/Copenhagen для преобразования UTC time например. 10-08-2020 12:32:00 до local time например. 10-08-2020 14:32:00 в SQL.

(Обратите внимание, что ко времени UTC было добавлено два часа, потому что смещение UTC относительно Копенгагена в это время составляет два часа из-за перехода на летнее время.)

официальный веб-сайт IANA имеет загружаемая база данных часовых поясов, но я не понимаю, как это реализовать, особенно в контексте SQL.

Я потратил много времени, пытаясь решить эту задачу, и я приветствую любой полезный вклад. Кажется, что в экосистеме Microsoft вообще нет поддержки стандарта часового пояса IANA.


sql tsql datetime timezone iana
person Mike    schedule 10.08.2020    source источник
comment
Жаль, что вы застряли с SQL Server. Zoneinfo отлично справляется с преобразованием часовых поясов. Он может преобразовывать исторические метки времени из UTC в местное время и обратно, используя исторические правила летнего времени, а не только текущий активный флаг летнего времени. И MySQL правильно реализует это. Просто говорю'   -  person O. Jones    schedule 10.08.2020
comment
@O.Jones Zoneinfo — это модуль Python, верно? Многие модули имеют преобразование часового пояса, включая Pandas и pytz. Я успешно реализовал несколько из них, но не могу использовать их в контексте SQL - отсюда и вопрос выше.   -  person Mike    schedule 11.08.2020
comment
Я не использовал слово zoneinfo для обозначения модуля Python. Это также название базы данных часовых поясов IANA.   -  person O. Jones    schedule 11.08.2020
comment
Привет. Это спрашивали уже много раз. Вот самый прямой дубликат. Пожалуйста, проголосуйте за ответ, чтобы я мог пометить его как дубликат этого. У него есть несколько различных вариантов, которые вы можете рассмотреть. Спасибо и удачи.   -  person Matt Johnson-Pint    schedule 11.08.2020

Ответы (2)


arrow_upward
2
arrow_downward

Microsoft SQL Server

В Microsoft SQL Server есть поддержка часовых поясов, но, возможно, не для стандарта IANA. Информация о часовом поясе хранится в sys.time_zone_info.

select tzi.*
from sys.time_zone_info tzi
where tzi.name like '%copenhagen%'
   or tzi.name like '%denmark%'
   or tzi.name like '%europe%'

-- result (dst = daylight saving time)
name                             current_utc_offset is_currently_dst
-------------------------------- ------------------ ----------------
W. Europe Standard Time          +02:00             1
Central Europe Standard Time     +02:00             1
Central European Standard Time   +02:00             1
E. Europe Standard Time          +03:00             1

Эти смещения можно комбинировать со значениями datetime, как показано здесь.

Вы можете (вручную) создать таблицу сопоставления, которая сопоставляет часовые пояса IANA из вашего набора данных с часовыми поясами, доступными в sys.time_zone_info. Альтернативой может быть запрос с большим оператором CASE для выбора наиболее точного доступного часового пояса.

Загрузки IANA

Импорт данных из файлов, которые можно загрузить с веб-сайта IANA, непосредственно в SQL был бы большой проблемой. Я быстро просмотрел файл «README», содержащийся в tzdata2020a.tar.gz загрузке (используйте ваше любимое zip-приложение для распаковки файлов). Он содержит некоторые команды в стиле UNIX для установки часовых поясов в ОС (выдержка ниже).

make TOPDIR=$HOME/tzdir install
$HOME/tzdir/usr/bin/zdump -v America/Los_Angeles

Фактическая информация о часовом поясе выглядит так, как показано ниже (выдержка из файла 'europe' для Дании). Боюсь, для этого потребуется обширный сценарий для разбора и импорта необходимых данных в Microsoft SQL Server.

# Denmark, Faroe Islands, and Greenland

# From Jesper Nørgaard Welen (2005-04-26):
# http://www.hum.aau.dk/~poe/tid/tine/DanskTid.htm says that the law
# [introducing standard time] was in effect from 1894-01-01....
# The page http://www.retsinfo.dk/_GETDOCI_/ACCN/A18930008330-REGL
# confirms this, and states that the law was put forth 1893-03-29.
#
# The EU [actually, EEC and Euratom] treaty with effect from 1973:
# http://www.retsinfo.dk/_GETDOCI_/ACCN/A19722110030-REGL
#
# This provoked a new law from 1974 to make possible summer time changes
# in subsequent decrees with the law
# http://www.retsinfo.dk/_GETDOCI_/ACCN/A19740022330-REGL
#
# It seems however that no decree was set forward until 1980.  I have
# not found any decree, but in another related law, the effecting DST
# changes are stated explicitly to be from 1980-04-06 at 02:00 to
# 1980-09-28 at 02:00.  If this is true, this differs slightly from
# the EU rule in that DST runs to 02:00, not 03:00.  We don't know
# when Denmark began using the EU rule correctly, but we have only
# confirmation of the 1980-time, so I presume it was correct in 1981:
# The law is about the management of the extra hour, concerning
# working hours reported and effect on obligatory-rest rules (which
# was suspended on that night):
# http://www.retsinfo.dk/_GETDOCI_/ACCN/C19801120554-REGL

# From Jesper Nørgaard Welen (2005-06-11):
# The Herning Folkeblad (1980-09-26) reported that the night between
# Saturday and Sunday the clock is set back from three to two.

# From Paul Eggert (2005-06-11):
# Hence the "02:00" of the 1980 law refers to standard time, not
# wall-clock time, and so the EU rules were in effect in 1980.

# Rule  NAME    FROM    TO  TYPE    IN  ON  AT  SAVE    LETTER/S
Rule    Denmark 1916    only    -   May 14  23:00   1:00    S
Rule    Denmark 1916    only    -   Sep 30  23:00   0   -
Rule    Denmark 1940    only    -   May 15   0:00   1:00    S
Rule    Denmark 1945    only    -   Apr  2   2:00s  1:00    S
Rule    Denmark 1945    only    -   Aug 15   2:00s  0   -
Rule    Denmark 1946    only    -   May  1   2:00s  1:00    S
Rule    Denmark 1946    only    -   Sep  1   2:00s  0   -
Rule    Denmark 1947    only    -   May  4   2:00s  1:00    S
Rule    Denmark 1947    only    -   Aug 10   2:00s  0   -
Rule    Denmark 1948    only    -   May  9   2:00s  1:00    S
Rule    Denmark 1948    only    -   Aug  8   2:00s  0   -
#
# Zone  NAME        STDOFF  RULES   FORMAT  [UNTIL]
Zone Europe/Copenhagen   0:50:20 -  LMT 1890
             0:50:20 -  CMT 1894 Jan  1 # Copenhagen MT
             1:00   Denmark CE%sT   1942 Nov  2  2:00s
             1:00   C-Eur   CE%sT   1945 Apr  2  2:00
             1:00   Denmark CE%sT   1980
             1:00   EU  CE%sT
Zone Atlantic/Faroe -0:27:04 -  LMT 1908 Jan 11 # Tórshavn
             0:00   -   WET 1981
             0:00   EU  WE%sT
person Sander    schedule 10.08.2020
comment
Привет. Две вещи. 1) Нельзя применить текущее смещение часового пояса к произвольной дате/времени, как вы предложили. На самом деле смещение может быть другим для этой даты/времени. Вместо этого следует использовать оператор AT TIME ZONE sql для применения именованного часового пояса. 2) Во второй части скрипт и функции, которые вы предлагаете, уже доступны в моей Поддержка часовых поясов SQL Server утилита, которая опирается на существующие парсеры от Noda Time. (Возможен прямой анализ из источников IANA, но ИМХО для этой цели было бы излишним.) - person Matt Johnson-Pint; 11.08.2020
comment
1) Я согласен, ссылка в моем ответе была обновлена ​​до правильного примера. 2) Хорошее расширение, такого раньше не видел. Я проголосовал за ваш ответ на предыдущий / повторяющийся вопрос. - person Sander; 12.08.2020
comment
@Сандер Спасибо! Это кажется наиболее элегантным, надежным и масштабируемым решением проблемы: вы можете (вручную) создать таблицу сопоставления, которая сопоставляет часовые пояса IANA из вашего набора данных с часовыми поясами, доступными в sys.time_zone_info. Я создаю таблицу, которая сопоставляет Windows tz с IANA tz и использует собственные функции Windows для поиска правильного значения. - person Mike; 13.08.2020

arrow_upward
1
arrow_downward

немного поздно, но для меня я решил сопоставление между окнами и базой данных часовых поясов.

следующий результат 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]

кроме того, обратитесь к ссылкам ниже:

Список часовых поясов базы данных tz

Часовой пояс Windows

person Haseeb    schedule 10.03.2021