In one of the systems I work on we have the problem of needing to track the different timezones that our users are in. In doing this we need to account for daylight savings time.
I see code like this used in many places:
select ...
dbo.fn_convertFromUTC(@StartUTC, @timeZone) as startLocal,
dbo.fn_convertFromUTC(@EndUTC, @timeZone) as endLocal
from ...
and thought this might have an impact on performance since the conversion function will fire twice for each row in the result set. I came up with a function that takes 3 parameters: @start, @end (both in UTC time) and @timeZone and returns a table like the one below. You would then joint to this table to do the conversion. This will allow for the same functionality, but will only be fired once or twice per result set.
startTime endTime offset isHr2
3/1/07 7:00 3/11/07 6:59 -5 0
3/11/07 7:00 11/4/07 6:59 -4 0
11/4/07 7:00 11/4/07 7:59 -5 1
11/4/07 8:00 11/5/07 9:00 -5 0
--Here is some test code that shows how this would be used:
declare @startViewUTC DateTime, @endViewUTC DateTime, @timeZone char(4)
set @startViewUTC = '3/1/2007 7:00am'
set @endViewUTC = '11/5/2007 9:00am'
set @timeZone = 'EPT' -- Eastern Prevailing Time
select ai.startTime,
ai.endTime,
dateadd(hh, tzStart.offset,ai.startTime) as startLocal,
dateadd(hh, tzEnd.offset,ai.endTime) as endLocal,
tzStart.isHr2, tzStart.offset, tzEnd.isHr2, tzEnd.offset
from dbo.fn_getAllIntervals(@startViewUTC, @endViewUTC, 3) ai
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone) tzStart
on ai.startTime between tzStart.startTime and tzStart.endTime
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone) tzEnd
on ai.endTime between tzEnd.startTime and tzEnd.endTime
--And here is the functions that are used to return the time zone offset table:
-- First we need to have a function that returns the Daylight Saving Start DateTime for a given year.
CREATE ALTER FUNCTION dbo.fn_getDSTStart (
@year int
)
RETURNS datetime
AS
BEGIN
DECLARE @dstStart datetime
IF @year < 2007
SET @dstStart = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw,
Cast(@year AS varchar) + '0401')) % 7, Cast(@year AS varchar) + '0401'))
ELSE
SET @dstStart = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw,
Cast(@year AS varchar) + '0308')) % 7,Cast(@year AS varchar) + '0308'))
RETURN @dstStart
END
CREATE FUNCTION dbo.fn_getDSTEnd (
@year int
)
RETURNS datetime
AS
BEGIN
DECLARE @dstEnd datetime
IF @year < 2007
SET @dstEnd = DateAdd(mi, 120, DateAdd(dd, 1 - DatePart(dw,
Cast(@year AS varchar) + '1031'), Cast(@year AS varchar) + '1031'))
ELSE
SET @dstEnd = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw,
Cast(@year AS varchar) + '1101')) % 7,Cast(@year AS varchar) + '1101'))
RETURN @dstEnd
END
GO
CREATE FUNCTION dbo.fn_getTimeZoneOffsets (
@start datetime,
@end datetime,
@timeZone varchar(4)
)
RETURNS @timeZoneOffsets TABLE
(startTime datetime,
endTime datetime,
offset int,
isHr2 bit)
AS
BEGIN
declare @defaultOffset int
--obtain the Standard Time, GMT Offset (this is not a complete list of timezones.)
Select @defaultOffset =
case @timeZone
when 'GMT' then 0
when 'AST' then -4
when 'ADT' then -3
when 'EST' then -5
when 'EDT' then -4
when 'CST' then -6
when 'CDT' then -5
when 'MST' then -7
when 'MDT' then -6
when 'PST' then -8
when 'PDT' then -7
when 'AKST' then -9
when 'AKDT' then -8
when 'EPT' then -5
when 'CPT' then -6
when 'MPT' then -7
when 'PPT' then -8
else 0
end
IF @timeZone IN ('CPT', 'EPT', 'MPT', 'PPT')
BEGIN
DECLARE @offsetTime int,
@dstStart datetime,
@dstEnd datetime,
@yearStart datetime,
@yearEnd datetime,
@prevYearEnd datetime,
@startYear int,
@endYear int,
@Year int
set @startYear = year(@start)
set @endYear = year(@end)
set @Year = @startYear
-- keep in mind these dates are all in GMT.
--break the year into each part that has a different value
while @Year <= @endYear
begin
set @dstStart = dateadd(hh,@defaultOffset*-1,dbo.fn_getDSTStart(@Year))
set @dstEnd = dateadd(hh,@defaultOffset*-1,dbo.fn_getDSTEnd(@Year))
set @yearStart = cast('01/01/'+cast(@Year as char(4)) as DateTime)
set @yearEnd = cast('12/31/'+cast(@Year as char(4))+' 23:59:59.000' as DateTime)
set @yearStart = case when @yearStart < @start then @start else @yearStart end
set @yearEnd = case when @yearEnd < @end then @yearEnd else @end end
if(@prevYearEnd is null)
begin
insert into @timeZoneOffsets
Select @yearStart, dateadd(ss,-1,@dstStart), @defaultOffset, 0 isHr2
end
else
begin
update @timeZoneOffsets
set endTime=dateadd(ss,-1,@dstStart)
where endTime=@prevYearEnd
end
set @prevYearEnd = @yearEnd
insert into @timeZoneOffsets
Select @dstStart, dateadd(ss,-1,@dstEnd), @defaultOffset + 1, 0 isHr2
insert into @timeZoneOffsets
Select @dstEnd, dateadd(ss,-1,dateadd(hh,1,@dstEnd)), @defaultOffset, 1 isHr2
insert into @timeZoneOffsets
Select dateadd(hh,1,@dstEnd), @yearEnd, @defaultOffset, 0 isHr2
set @Year = @Year + 1
end
delete from @timeZoneOffsets
where endTime < @start
or startTime > @end
update @timeZoneOffsets
set startTime = @start
where startTime = (select min(startTime) from @timeZoneOffsets)
update @timeZoneOffsets
set endTime = @end
where endTime = (select max(endTime) from @timeZoneOffsets)
END
ELSE
insert into @timeZoneOffsets
Select @start, @end, @defaultOffset, 0 isHr2
RETURN
END
GO
Subscribe