Time Zones and Performance

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

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