Generating DST Conversion Table In SQL Server
Introduction
I ran into a situation where a database was using 3 different time zones to store datetime data in SQL Server. The data in the database was being imported into a data warehouse where it needed to be standardized to one time zone for reporting purposes. After scouring the web, I didn't really find anything that would meet my requirements. This is my attempt of creating a script that could generate a table to be used for this purpose.
I detail in this blog how I went through the process of creating the script for this. However, if you just want the code, you can skip to the section The Final Script. This was my first go at this script so I may make changes to it over time. If you have any feedback on how I can optimize this, feel free to leave me feedback.
Time Zones In the United States
There are several time zones used by the United States, but I am going to focus on the continental United States as that is what I was needing this script for.
I am only going into the basics of our time zones. Time zone laws have changed over the years. In the U.S. there are four standard time zones: Pacific Standard Time (-8), Mountain Standard Time (-7), Central Standard Time (-6), and Eastern Standard time (-5).
In 1966 Daylight Savings Time was introduced into law. This adjusted the time zones by 1 hour for several months of the year changing the time zones to be Pacific Daylight Time (-7), Mountain Daylight Time (-6), Central Daylight Time (-5), and Eastern Daylight time (-4). This has been observed by nearly every state in the U.S. There are some states that do not recognize Daylight Savings Time and stay in the same time zone year round.
In 2007, The U.S. adjusted their law to change when Daylight Savings Time took affect during the year. There is currently proposals of making Daylight Savings Time permanent to where states would keep the same time zone year-round.
Designing the Script
Now that we have a brief history of time zones, we have an idea of our requirements for the script. One additional thing to add to this is time change happens at 2 AM in each time zone. This means at 2 AM in Eastern Time Zone, time will be adjusted, but it will not happen in Central Time Zone for another hour later. Keeping this in mind, it is not as simple as calculating the changes to one time zone and then adjusting the others at the same time. Each time zone will need to independently be calculated. Since it is changed at 2:00 AM, we will need to calculate time to the hour. You can calculate this at a much lower granularity, but your table will be very large.
In order to do this, we need a consistent time standard to calculate from. In the IT world we use Universal Time Coordinated or UTC for most things. So this is a great standard to use as our base for conversions.
Since we are using UTC, we will need to be sure to have a UTC datetime column in our table. Additionally, we will include the year as well.
Moving on from UTC to the separate time zones, we will need a datetime field for each time zone we want to calculate. In order to calculated the times we will need to have the time zone offsets, so we will go ahead and store those as well for good measure.
Now that we have some background knowledge of time zones in the U.S. and we have a general idea of our end table layout, we can start building out our script.
Building the Script
First, we want to determine what date we want to start with and what date to end with on our table. Time zone laws changed in 2007, so for this example we will start before that just to check it all works. We will go ahead and use January 1, 2000 as our start date. For the end date, we will add a year to our current year to give ourselves so padding at the end of our timespan. We will then use December 31, 2023 since it is currently 2022 when this post was written.
Now let's write some code for it.
DECLARE @StartDate DATE = '2000-01-01'; -- Start-date
DECLARE @EndDate DATE = CAST(YEAR(GETDATE()) + 2 AS CHAR(4)) + '-01-01'; -- End-Date
Now we can script out our table we are going to dump our generated data into at the end of the script.
CREATE TABLE [dbo].[TimeTable] (
[utc_datetime] [datetime] NOT NULL,
[utc_int] [bigint] NULL,
[utc_year] [int] NULL,
[pacific_timezone_offset] [smallint] NULL,
[pacific_current_offset] [smallint] NULL,
[pacific_datetime] [datetime] NULL,
[mountain_timezone_offset] [smallint] NULL,
[mountain_current_offset] [smallint] NULL,
[mountain_datetime] [datetime] NULL,
[central_timezone_offset] [smallint] NULL,
[central_current_offset] [smallint] NULL,
[central_datetime] [datetime] NULL,
[eastern_timezone_offset] [smallint] NULL,
[eastern_current_offset] [smallint] NULL,
[eastern_datetime] [datetime] NULL,
CONSTRAINT [PK_TimeTable] PRIMARY KEY CLUSTERED ([utc_datetime] ASC) -- Add primary key on the utc_datetime field
);
Now we can start with our time logic. The first thing we need to do is generate a list of every hour from our start date to our end date. This part gets a little complicated so I will try to add some commentary as of what is going on and why we are doing it.
SELECT
YEAR(CalculatedDate) AS [utc_year]
, CAST(
CONCAT( -- Create a string to join the date and time together
CONVERT(NVARCHAR, CalculatedDate, 23), -- convert date to yyyy-mm-dd format
' ', -- Insert space between date and time
[date_hour] - 1, -- The hour minus 1 since our list is 1 - 24 (Time actually gets recorded as 0 - 23)
':00:00' -- Add hour and seconds for formatting purposes
)
AS DATETIME)
AS [utc_datetime]
FROM (
SELECT CalculatedDate = CAST(DATEADD(DAY, rn - 1, @StartDate) AS DATE)
FROM (
-- This section creates a number list from 1 - n to represent each day from the @StartDate to the @EndDate
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) AS x
) d
-- Using a CROSS APPLY, we can create a join of the each day from the @StartDate to the @EndDate with a list of hours
-- in each day (24) to get a datetime value for each hour of each day from @StartDate to @EndDate
CROSS APPLY (
-- This section creates a number list from 1 - 24 to represent the number of hours in a day.
SELECT TOP 24 [date_hour] = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) t
Additionally to dates, we will need the different time zones in a list so we can calculate the offsets per time zone. We will calculate current offset as well as just the "Standard Time" offset that rest of the world recognizes that time zone to be. I have this logic in a singe CTE (Common Table Expression), but it is complicated enough I am going to break it apart in sections. You can see it all put back together in the script at the end.
This is where we generate the actual time zone offsets.
SELECT
-- Get names of time zones
CASE
WHEN [timezone_offset] = 4 THEN 'Eastern'
WHEN [timezone_offset] = 5 THEN 'Central'
WHEN [timezone_offset] = 6 THEN 'Mountain'
WHEN [timezone_offset] = 7 THEN 'Pacific'
END AS [timezone]
-- Convert offsets to negative
, [timezone_offset] * -1 AS [timezone_offset]
FROM (
SELECT
-- We add 3 since the first timezone in the US is Eastern Standard Time at -4 offset
-- We will convert it to negative later
[time_zone] + 3 AS [timezone_offset]
FROM (
-- Get a list of 1 - 4 to represent the timezones we need
SELECT TOP 4 [time_zone] = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) tmz
) tmz
Now that we have our time zones, we need to figure out when the daylight savings changes take place.
SELECT
[calendar_year]
, [timezone]
, [timezone_offset]
, CASE
WHEN [calendar_year] < 1966 THEN NULL -- Before 1966 there was no DST, so we just return null
WHEN [calendar_year] >= 2007 THEN [start_date] -- 2007 there was a change in law and this is the one we calculated
ELSE DATEADD(WEEK, 3, [start_date]) -- From 1966-2006 DST started 3 weeks later than it currently does
END AS [start_datetime]
, CASE
WHEN [calendar_year] < 1966 THEN NULL -- Before 1966 there was no DST, so we just return null
WHEN [calendar_year] >= 2007 THEN [end_date] -- 2007 there was a change in law and this is the one we calculated
ELSE DATEADD(WEEK, -1, [end_date]) -- From 1966-2006 DST ended 1 week earlier than it currently does
END AS [end_datetime]
FROM (
SELECT
[calendar_year]
, tmz.timezone
, tmz.timezone_offset
-- Get time zone offset and add that to appropriate hours to be able to figure out exactly what hour time changed
-- in that particular time zone
, CASE DATEPART(dw, [start_week])
WHEN 1 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 171, [start_week])
WHEN 2 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 315, [start_week])
WHEN 3 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 291, [start_week])
WHEN 4 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 267, [start_week])
WHEN 5 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 243, [start_week])
WHEN 6 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 219, [start_week])
WHEN 7 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 195, [start_week])
END AS [start_date]
, CASE DATEPART(dw, [end_week])
WHEN 1 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 2, [end_week])
WHEN 2 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 146, [end_week])
WHEN 3 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 122, [end_week])
WHEN 4 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 98, [end_week])
WHEN 5 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 74, [end_week])
WHEN 6 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 50, [end_week])
WHEN 7 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 26, [end_week])
END AS [end_date]
FROM (
SELECT
[calendar_year]
, CONCAT(convert(varchar, calendar_year), '/03/01') AS [start_week] -- Setup the base datetime to calculate from
, CONCAT(convert(varchar, calendar_year), '/11/01') AS [end_week] -- Setup the base datetime to calculate from
FROM (
SELECT YEAR(@StartDate) - 1 + rn AS [calendar_year]
FROM (
SELECT TOP (YEAR(GETDATE()) - YEAR(@StartDate) + 2) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) y
) y
) dst
The Final Script
Here is the script when we put it all together.
DECLARE @StartDate DATE = '2000-01-01'; -- Start-date
DECLARE @EndDate DATE = CAST(YEAR(GETDATE()) + 2 AS CHAR(4)) + '-01-01'; -- End-Date
DROP TABLE IF EXISTS [dbo].[TimeTable];
CREATE TABLE [dbo].[TimeTable] (
[utc_datetime] [datetime] NOT NULL,
[utc_int] [bigint] NULL,
[utc_year] [int] NULL,
[pacific_timezone_offset] [smallint] NULL,
[pacific_current_offset] [smallint] NULL,
[pacific_datetime] [datetime] NULL,
[mountain_timezone_offset] [smallint] NULL,
[mountain_current_offset] [smallint] NULL,
[mountain_datetime] [datetime] NULL,
[central_timezone_offset] [smallint] NULL,
[central_current_offset] [smallint] NULL,
[central_datetime] [datetime] NULL,
[eastern_timezone_offset] [smallint] NULL,
[eastern_current_offset] [smallint] NULL,
[eastern_datetime] [datetime] NULL,
CONSTRAINT [PK_TimeTable] PRIMARY KEY CLUSTERED ([utc_datetime] ASC)
);
DROP TABLE IF EXISTS #DateTimeTable;
SELECT
YEAR(CalculatedDate) AS [utc_year]
, CAST(CONCAT(CONVERT(NVARCHAR, CalculatedDate, 23), ' ', [date_hour] - 1, ':00:00') AS DATETIME) AS [utc_datetime]
INTO #DateTimeTable
FROM (
SELECT CalculatedDate = CAST(DATEADD(DAY, rn - 1, @StartDate) AS DATE)
FROM (
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) AS x
) d
CROSS APPLY (
SELECT TOP 24 [date_hour] = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) t
;WITH TimezoneData AS (
SELECT
[utc_datetime]
, CASE
WHEN YEAR([utc_datetime]) < 1966 THEN NULL
WHEN [utc_datetime] < [start_datetime] OR [utc_datetime] >= [end_datetime] THEN [timezone_offset] - 1
ELSE [timezone_offset]
END AS [current_offset]
, [timezone]
, [timezone_offset]
, [start_datetime]
, [end_datetime]
FROM #DateTimeTable d
INNER JOIN (
SELECT
[calendar_year]
, [timezone]
, [timezone_offset]
, CASE
WHEN [calendar_year] < 1966 THEN NULL
WHEN [calendar_year] >= 2007 THEN [start_date]
ELSE DATEADD(WEEK, 3, [start_date])
END AS [start_datetime]
, CASE
WHEN [calendar_year] < 1966 THEN NULL
WHEN [calendar_year] >= 2007 THEN [end_date]
ELSE DATEADD(WEEK, 3, [end_date])
END AS [end_datetime]
FROM (
SELECT
[calendar_year]
, tmz.timezone
, tmz.timezone_offset
, CASE DATEPART(dw, [start_week])
WHEN 1 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 171, [start_week])
WHEN 2 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 315, [start_week])
WHEN 3 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 291, [start_week])
WHEN 4 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 267, [start_week])
WHEN 5 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 243, [start_week])
WHEN 6 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 219, [start_week])
WHEN 7 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 195, [start_week])
END AS [start_date]
, CASE DATEPART(dw, [end_week])
WHEN 1 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 2, [end_week])
WHEN 2 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 146, [end_week])
WHEN 3 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 122, [end_week])
WHEN 4 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 98, [end_week])
WHEN 5 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 74, [end_week])
WHEN 6 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 50, [end_week])
WHEN 7 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 26, [end_week])
END AS [end_date]
FROM (
SELECT
[calendar_year]
, CONCAT(convert(varchar, calendar_year), '/03/01') AS [start_week]
, CONCAT(convert(varchar, calendar_year), '/11/01') AS [end_week]
FROM (
SELECT YEAR(@StartDate) - 1 + rn AS [calendar_year]
FROM (
SELECT TOP (YEAR(GETDATE()) - YEAR(@StartDate) + 2) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) y
) y
) c
CROSS APPLY (
SELECT
CASE
WHEN [timezone_offset] = 4 THEN 'Eastern'
WHEN [timezone_offset] = 5 THEN 'Central'
WHEN [timezone_offset] = 6 THEN 'Mountain'
WHEN [timezone_offset] = 7 THEN 'Pacific'
END AS [timezone]
, [timezone_offset] * -1 AS [timezone_offset]
FROM (
SELECT
[time_zone] + 3 AS [timezone_offset]
FROM (
SELECT TOP 4 [time_zone] = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
ORDER BY s1.[object_id]
) tmz
) tmz
) tmz
) dst
) dst ON d.utc_year = dst.calendar_year
)
INSERT INTO [dbo].[TimeTable] (
[utc_datetime]
, [utc_int]
, [utc_year]
, [pacific_timezone_offset]
, [pacific_current_offset]
, [pacific_datetime]
, [mountain_timezone_offset]
, [mountain_current_offset]
, [mountain_datetime]
, [central_timezone_offset]
, [central_current_offset]
, [central_datetime]
, [eastern_timezone_offset]
, [eastern_current_offset]
, [eastern_datetime]
)
SELECT DISTINCT
d.utc_datetime AS [utc_datetime]
, CONVERT(BIGINT, (DATEDIFF(ss, '01-01-1970 00:00:00', d.utc_datetime))) * 1000 AS [utc_int]
, d.utc_year AS [utc_year]
, p.timezone_offset AS [pacific_timezone_offset]
, p.current_offset AS [pacific_current_offset]
, DATEADD(hh, p.current_offset, d.utc_datetime) AS [pacific_datetime]
, m.timezone_offset AS [mountain_timezone_offset]
, m.current_offset AS [mountain_current_offset]
, DATEADD(hh, m.current_offset, d.utc_datetime) AS [mountain_datetime]
, c.timezone_offset AS [central_timezone_offset]
, c.current_offset AS [central_current_offset]
, DATEADD(hh, c.current_offset, d.utc_datetime) AS [central_datetime]
, e.timezone_offset AS [eastern_timezone_offset]
, e.current_offset AS [eastern_current_offset]
, DATEADD(hh, e.current_offset, d.utc_datetime) AS [eastern_datetime]
FROM #DateTimeTable d
LEFT OUTER JOIN TimezoneData e ON d.utc_datetime = e.utc_datetime AND e.timezone = 'Eastern'
LEFT OUTER JOIN TimezoneData c ON d.utc_datetime = c.utc_datetime AND c.timezone = 'Central'
LEFT OUTER JOIN TimezoneData m ON d.utc_datetime = m.utc_datetime AND m.timezone = 'Mountain'
LEFT OUTER JOIN TimezoneData p ON d.utc_datetime = p.utc_datetime AND p.timezone = 'Pacific'
ORDER BY d.utc_datetime;
Conclusion
This script will create a table populated with each hour of the day from a particular start date to December 31 of the year after this script was ran. It will show the times adjusted to Daylight Savings Time when it is in effect.
Feel free to use this script and modify it as needed. Let me know if you found it useful. I will also be keeping a copy of this script on my GitHub where I will likely be making any updates to it. Feel free to star or watch the project there.
I don't have a comments section yet, so feel free to send me feedback on this blog.
Kevin is a data engineer and is the Business Intelligence Practice Lead at Software Design Partners specializing in data warehousing. He is a father, an occasional gamer, and lover of many different types of music.
The opinions expressed on this site are my own and may not represent my employer's view.
About this blog...
Generating Daylight Savings Time and Standard Time Conversion Table For Multiple Time Zones