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.
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