It’s Time – Checking and Adjusting for Daylight Saving Time
Ever need to join data sources from different time zones? Here’s a scenario I recently faced, and how I worked around it.
I needed to join several tables together from two different data sources. One data source was captured in UTC, while the other was local time zone, including Daylight Saving Time (DST) considerations. An additional challenge was that the data source that was in local time zone, can be in different time zones. I did have access to a geography dimension that had the time zones and the number of hours offset from UTC.
I know that there are SQL Server specific time offset options, but for a variety of reasons, I needed a more generic option to deal with the time zone and DST factors. That meant I needed to create an approach to check to see if a datetime was in Daylight Saving Time or not. I thought I would be able to find a reference table, but that meant I would need to be able to build one.
Here’s the code I wrote to build a temp table that had the DST Start and DST End datetimes for each year I needed to compare, and you can see how I did the compare to change the time to DST:
IF OBJECT_ID('tempdb..#DST_Date') IS NOT NULL DROP TABLE #DST_Date; CREATE TABLE #DST_Date( Date_Key datetime NOT NULL , DstStart datetime NOT NULL , DstEnd datetime NOT NULL ) ON [PRIMARY] declare @i INT , @date DATETIME , @enddate DATETIME , @StartDate DATETIME , @year INT , @StartOfMarch DATETIME , @StartOfNovember DATETIME , @DstStart DATETIME , @DstEnd DATETIME set @i = (select DATEDIFF(dd,'2018-01-01','2018-01-01')) -- Set the starting date of the table set @StartDate ='2018-01-01' set @enddate = DATEADD(yy,(DATEDIFF(yy,'2018-01-01', getdate())) + 1,'2018-12-31') -- One year more than current year while @i <= (select DATEDIFF(dd,'2018-01-01',@enddate)) -- Set the ending date of the table begin set @year = DATEPART(YYYY,(DATEADD(dd,@i,@StartDate))) SET @StartOfMarch = DATEADD(MONTH, 2, DATEADD(YEAR, @year - 1900, 0)) SET @StartOfNovember = DATEADD(MONTH, 10, DATEADD(YEAR, @year - 1900, 0)); SET @DstStart = DATEADD(HOUR, 2, DATEADD(day, ( ( 15 - DATEPART(dw, @StartOfMarch) ) % 7 ) + 7, @StartOfMarch)) SET @DstEnd = DATEADD(HOUR, 2, DATEADD(day, ( ( 8 - DATEPART(dw, @StartOfNovember) ) % 7 ), @StartOfNovember)) SET @date = (DATEADD(dd,@i,@StartDate)) INSERT INTO #DST_Date ( [Date_Key], [DstStart], [DstEnd] ) VALUES ( DATEADD(dd,@i,@StartDate), @DstStart, @DstEnd ) set @i = @i + 1 end; DECLARE @testDate datetime = '2018-03-11 01:01:00.000', @testDSTDate datetime = '2018-03-11 02:01:00.000' select Date_Key, DstStart, DstEnd , @testDate AS testDate , CASE WHEN @testDate Between DstStart AND DstEnd THEN 1 ELSE 0 END AS IN_DST, CASE WHEN @testDate Between DstStart AND DstEnd THEN (DATEADD(ss,(3600), @testDate)) ELSE @testDate END AS testDateDST_Time, @testDSTDate AS testDSTDate , CASE WHEN @testDSTDate Between DstStart AND DstEnd THEN 1 ELSE 0 END AS DSTin_DST, CASE WHEN @testDSTDate Between DstStart AND DstEnd THEN (DATEADD(ss,(3600), @testDSTDate)) ELSE @testDSTDate END AS testDSTDateDST_Time from #DST_Date where CAST( @testDate as DATE) = Date_Key SELECT * FROM #DST_Date
Here is the output of the test and a sample of the DST_Test temp table: