Need help using the official IANA timezone database in SQL

Question

I am using T-SQL queries to retrieve data from an Azure SQL database for customer data visualization.

I need a way to use an IANA timezone eg. Europe/Copenhagen to convert a UTC time eg. 10-08-2020 12:32:00 to local time eg. 10-08-2020 14:32:00 in SQL.

(Note that two hours have been added to the UTC time, because the UTC to Copenhagen offset at that time is two hours due to daylight saving.)

The official IANA website has a downloadable timezone database, but I fail to understand how to implement it, specifically in a SQL context.

I have spent a great deal of time trying to crack this challenge, and I welcome any useful input. There seems to be no support for the IANA timezone standard in the Microsoft ecosystem whatsoever.

Too bad you're stuck with SQL Server. Zoneinfo does a great job of timezone conversion. It can convert historical timestamps from UTC to local time and back using the historical daylight time rules, not just the currently active daylight time flag. And, MySQL implements that stuff correctly. Just sayin' — O. Jones, Aug 10 '20 at 15:04
@O.Jones Zoneinfo is a Python module, right? Lots of modules have timezone conversion, including Pandas and pytz. I have implemented several of these with success, but I can't use them in a SQL context - hence the above question. — Mike, Aug 11 '20 at 06:20
I wasn't using the word zoneinfo to refer to a python module. It's also the name of the IANA timezone database. — O. Jones, Aug 11 '20 at 10:04
Hi. This has been asked many times already. [Here is the most direct duplicate](https://stackoverflow.com/q/61260857/634824). Please upvote the answer there, so I can mark it as duplicate of this. It has several different options for you to consider. Thanks, and good luck. — Matt Johnson-Pint, Aug 11 '20 at 16:26

Sander · Accepted Answer · 2020-08-12T05:54:33.993

Microsoft SQL Server

There is time zone support in Microsoft SQL Server, but perhaps not for the IANA standard. Time zone information is stored in 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

These offsets can be combined with datetime values like shown here.

You could (manually) create a mapping table that maps the IANA time zones from your data set to the time zones available in sys.time_zone_info. An alternative would be a query with a large CASE statement to select the most accurate time zone available.

IANA downloads

Importing the data from the files that can be downloaded on the IANA website directly in SQL would be a big challenge. I had a quick look at the 'README' file contained in the tzdata2020a.tar.gz download (use your favourite zip application to unpack the files). It contains some UNIX style commands to install time zones on an OS (extract below).

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

The actual time zone information looks like the data below (extract from the file 'europe' for Denmark). I am afraid this would require an extensive script to parse and import the required data in 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

Hi. Two things. 1) One cannot apply the *current* offset of a time zone to an arbitrary date/time, as you suggested. The offset may in fact be different for that date/time. Instead - one should use the `AT TIME ZONE` sql statement to apply the named time zone. 2) In the second part, the script and functions you suggest is already available as my [SQL Server Time Zone Support](https://github.com/mj1856/SqlServerTimeZoneSupport) utility, which relies on existing parsers from Noda Time. (A direct parsing from IANA sources is possible, but IMHO would be overkill for this purpose.) — Matt Johnson-Pint, Aug 11 '20 at 16:29
1) I agree, the link in my answer has been updated to a proper example. 2) Nice extension, had not seen that one before. I upvoted your answer on the previous / duplicate question. — Sander, Aug 12 '20 at 06:00
@Sander Thanks! This seems to be the most elegant, robust and scalable solution to the challenge: "You could (manually) create a mapping table that maps the IANA time zones from your data set to the time zones available in sys.time_zone_info." I am generating a table that maps Windows tz to IANA tz and using native windows functions to look up the right value. — Mike, Aug 13 '20 at 10:55

score 3 · Answer 2 · answered Mar 10 '21 at 10:51

little late but for me I resolved by mapping between windows and timezone database.

following SQL result is a mapping. There is a column name SQL_Names apply a join with [sys].[Time_Zone_Info] table to get SQL Time Zone detail below is the sample.

Note: the JSON is not covering all the timezone due to character limits here but you may import a full list from the below links.

try out:

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]

furthermore, refer to the below links:

List of tz database time zones

Windows Timezone

Need help using the official IANA timezone database in SQL

2 Answers2