Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Handle conversion between time zones in SQL Server - part 2


By:   |   Last Updated: 2014-03-10   |   Comments (3)   |   Related Tips: 1 | 2 | 3 | More > Dates

Problem

In my last tip on time zone conversions, I demonstrated how to use a small calendar table, in conjunction with two user-defined functions, to accurately convert datetime values between UTC and Eastern Time, taking Daylight Saving Time into account. Of course in most environments you need more flexibility; specifically, the ability to switch between any two time zones.

Solution

In this tip, I'm going to expand upon the calendar table I described earlier, adding support for multiple time zones. I'm actually going to create two tables: a time zone table, with mostly metadata about the time zone across all calendar years, and a calendar table with a specific row for each year in every supported time zone.

In the interests of simplicity and brevity, I'm going to include the major American time zones (Eastern, Central, Mountain, and Pacific), as well as UTC and GMT/British Summer Time. To balance this simplification, I've also added two time zones that have half-hour offsets (Newfoundland and Central Australia (Darwin)); the latter is on the other side of the date line (so ahead of UTC, unlike North America) and does not observe Daylight Saving Time at all. This will provide a well-rounded sample of the types of time zones you may need to support, without drowning you in an exhaustive sea of every single time zone on earth.

CREATE TABLE dbo.TimeZones
(
  TimeZoneID TINYINT NOT NULL PRIMARY KEY,
  StandardDescription VARCHAR(64) NOT NULL UNIQUE, 
  DaylightDescription VARCHAR(64) NOT NULL UNIQUE,
  StandardOffsetMinutes SMALLINT NOT NULL,
  DaylightOffsetMinutes SMALLINT NOT NULL
);
INSERT dbo.TimeZones VALUES
(1,'UTC','UTC',0,0),
(2,'Eastern Standard Time','Eastern Daylight Time',   -300,-240),
(3,'Central Standard Time','Central Daylight Time',   -360,-300),
(4,'Mountain Standard Time','Mountain Daylight Time', -420,-360),
(5,'Pacific Standard Time','Pacific Daylight Time',   -480,-420),
(6,'Newfoundland Standard Time','Newfoundland Daylight Time', -210,-150),
(7,'Greenwich Mean Time','British Summer Time',       -60,0),
(8,'Australia Central Standard Time','Australia Central Standard Time',570,570);
CREATE TABLE dbo.TZCalendar
(
  [Year]           DATE NOT NULL,
  TimeZoneID       TINYINT NOT NULL FOREIGN KEY 
                   REFERENCES dbo.TimeZones(TimeZoneID),
  UTC_DST_Start    SMALLDATETIME NOT NULL,
  UTC_DST_End      SMALLDATETIME NOT NULL,
  Local_DST_Start  SMALLDATETIME NOT NULL,
  Local_DST_End    SMALLDATETIME NOT NULL
  PRIMARY KEY ([Year], TimeZoneID)
);

As with the previous tip, the most complicated part of this scenario is populating the calendar table. I won't go into how many iterations this took to get right; the iterations could make for a tip or blog post all on their own, though a lot of the logic that drove this query was explained in the first tip in this series. For the North American time zones, the calculations for when DST starts mimic those described earlier; for time zones that don't shift for DST (including UTC, obviously), we just pretend that DST lasts all year, but the "DST offset" is actually the same as the standard offset.

SET DATEFIRST 7;
;WITH cte(d,p) AS 
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-2000+51) DATEADD(YEAR,number,'20000101'),
    p = CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0
    FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number
),
tz(tz,som,dom,ch,cm,cd1,cd2,locs,loce) AS 
(
  SELECT TimeZoneID, StandardOffsetMinutes, DaylightOffsetMinutes,
   CASE TimeZoneID WHEN 7 THEN 0   ELSE 2   END, -- 1:00 AM or 2:00 AM
   CASE TimeZoneID WHEN 7 THEN 3   ELSE 2   END, -- April or March
   CASE TimeZoneID WHEN 7 THEN -7  ELSE 7   END, -- Week earlier or second week
   CASE TimeZoneID WHEN 7 THEN -7  ELSE 0   END, -- Week earlier or first week
   CASE TimeZoneID WHEN 7 THEN 120 ELSE 60  END, 
   CASE TimeZoneID WHEN 7 THEN 60  ELSE -60 END
  FROM dbo.TimeZones
),
cal(d,tz,som,dom,utcstart,utcend,locs,loce) AS
(
    SELECT cte.d,tz.tz,tz.som,tz.dom,
    -- UTC start of DST (start of year for areas that don't observe)
    CASE WHEN tz.tz IN (1,8) THEN cte.d
     ELSE 
     -- US/Canada: 
       -- First Sunday in April (< 2007)
       -- Second Sunday in March (since 2007)
     -- England: Last Sunday in March (since 1995)
       DATEADD(MINUTE,-tz.som,DATEADD(HOUR,tz.ch,
        DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,tz.cm
        -CASE WHEN tz.tz <> 7 THEN p ELSE 0 END,cte.d))+1)%7+tz.cd1
        -CASE WHEN tz.tz <> 7 THEN 7*p ELSE 0 END,DATEADD(MONTH,tz.cm
        +CASE WHEN tz.tz <> 7 THEN p ELSE 0 END,cte.d))))
     END,
     -- UTC end of DST (start of *following* year for areas that don't observe)
     CASE WHEN tz.tz IN (1,8) THEN DATEADD(YEAR, 1, cte.d)
     ELSE 
     -- US/Canada:
        -- Last Sunday in October (< 2007) or 
        -- First Sunday in November (since 2007)
     -- England: Last Sunday in October:
       DATEADD(MINUTE,-tz.dom,DATEADD(HOUR,tz.ch, 
        DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,cte.d))
        +1)%7+tz.cd2-CASE WHEN tz.tz <> 7 THEN (7*p) ELSE 0 END,
        DATEADD(MONTH,10,cte.d)))) 
    END,locs,loce
    FROM cte CROSS JOIN tz
)
INSERT dbo.TZCalendar
SELECT d,tz,utcstart,utcend, 
 CASE WHEN tz IN (1,8) THEN utcstart ELSE DATEADD(MINUTE,som+locs,utcstart) END,
 CASE WHEN tz IN (1,8) THEN utcend   ELSE DATEADD(MINUTE,dom+loce,utcend)   END
FROM cal ORDER BY d;

This creates a table with 520 rows (65 years across 8 time zones); it weighs in at only 40 KB. Again, as with our smaller calendar table from the previous tip, this table should be in memory all of the time, as long as it is being referenced often enough. The data looks like this:

Year        TimeZoneID  UTC_DST_Start     UTC_DST_End       Local_DST_Start   Local_DST_End
----------  ----------  ----------------  ----------------  ----------------  ---------------- 
2000-01-01  1           2000-01-01 00:00  2001-01-01 00:00  2000-01-01 00:00  2001-01-01 00:00
2000-01-01  2           2000-04-06 07:00  2000-10-29 06:00  2000-04-06 03:00  2000-10-29 01:00
2000-01-01  3           2000-04-06 08:00  2000-10-29 07:00  2000-04-06 03:00  2000-10-29 01:00
2000-01-01  4           2000-04-06 09:00  2000-10-29 08:00  2000-04-06 03:00  2000-10-29 01:00
2000-01-01  5           2000-04-06 10:00  2000-10-29 09:00  2000-04-06 03:00  2000-10-29 01:00
2000-01-01  6           2000-04-06 05:30  2000-10-29 04:30  2000-04-06 03:00  2000-10-29 01:00
2000-01-01  7           2000-03-26 01:00  2000-10-29 00:00  2000-03-26 02:00  2000-10-29 01:00
2000-01-01  8           2000-01-01 00:00  2001-01-01 00:00  2000-01-01 00:00  2001-01-01 00:00
...
2014-01-01  1           2014-01-01 00:00  2015-01-01 00:00  2014-01-01 00:00  2015-01-01 00:00
2014-01-01  2           2014-03-09 07:00  2014-11-02 06:00  2014-03-09 03:00  2014-11-02 01:00
2014-01-01  3           2014-03-09 08:00  2014-11-02 07:00  2014-03-09 03:00  2014-11-02 01:00
2014-01-01  4           2014-03-09 09:00  2014-11-02 08:00  2014-03-09 03:00  2014-11-02 01:00
2014-01-01  5           2014-03-09 10:00  2014-11-02 09:00  2014-03-09 03:00  2014-11-02 01:00
2014-01-01  6           2014-03-09 05:30  2014-11-02 04:30  2014-03-09 03:00  2014-11-02 01:00
2014-01-01  7           2014-03-30 01:00  2014-10-26 00:00  2014-03-30 02:00  2014-10-26 01:00
2014-01-01  8           2014-01-01 00:00  2015-01-01 00:00  2014-01-01 00:00  2015-01-01 00:00
...
2064-01-01  1           2064-01-01 00:00  2065-01-01 00:00  2064-01-01 00:00  2065-01-01 00:00
2064-01-01  2           2064-03-09 07:00  2064-11-02 06:00  2064-03-09 03:00  2064-11-02 01:00
2064-01-01  3           2064-03-09 08:00  2064-11-02 07:00  2064-03-09 03:00  2064-11-02 01:00
2064-01-01  4           2064-03-09 09:00  2064-11-02 08:00  2064-03-09 03:00  2064-11-02 01:00
2064-01-01  5           2064-03-09 10:00  2064-11-02 09:00  2064-03-09 03:00  2064-11-02 01:00
2064-01-01  6           2064-03-09 05:30  2064-11-02 04:30  2064-03-09 03:00  2064-11-02 01:00
2064-01-01  7           2064-03-30 01:00  2064-10-26 00:00  2064-03-30 02:00  2064-10-26 01:00
2064-01-01  8           2064-01-01 00:00  2065-01-01 00:00  2064-01-01 00:00  2065-01-01 00:00

This time, we'll create a single function that takes in a date/time in any of the supported time zones, and converts it to any of the other supported time zones. The simplest way to think about this is to convert the source time to UTC, then convert to the target time zone. So that's what the function will do (and it does still take into account edge cases, for example it returns NULL if you pass in 2014-03-09 02:30 and say that was in the Eastern time zone):

CREATE FUNCTION dbo.ConvertBetweenTimeZones
(
  @Origin   DATETIME,
  @SourceTZ TINYINT,
  @TargetTZ TINYINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN 
 (
  SELECT 
     Origin = @Origin, 
     src.OriginTimeZone,
     [Target] = DATEADD(MINUTE, CASE 
       WHEN src.UTC >= trg.UTC_DST_Start 
        AND src.UTC < trg.UTC_DST_End THEN tz.DaylightOffsetMinutes 
       ELSE tz.StandardOffsetMinutes END, src.UTC),
     TargetTimeZone = CASE 
       WHEN src.UTC >= trg.UTC_DST_Start 
        AND src.UTC < trg.UTC_DST_End THEN tz.DaylightDescription
       ELSE tz.StandardDescription END
  FROM 
  (
    SELECT 
      src.[Year],
      UTC = DATEADD(MINUTE, -CASE 
        WHEN @Origin >= src.Local_DST_Start 
         AND @Origin < src.Local_DST_End THEN t.DaylightOffsetMinutes 
        WHEN @Origin >= DATEADD(HOUR,-1,src.Local_DST_Start) 
         AND @Origin < src.Local_DST_Start THEN NULL
        ELSE t.StandardOffsetMinutes END, @Origin),
      OriginTimeZone = CASE 
        WHEN @Origin >= src.Local_DST_Start 
         AND @Origin < src.Local_DST_End THEN t.DaylightDescription
        ELSE t.StandardDescription END 
    FROM dbo.TZCalendar AS src
    INNER JOIN dbo.TimeZones AS t 
    ON src.TimeZoneID = t.TimeZoneID
    WHERE src.TimeZoneID = @SourceTZ 
      AND t.TimeZoneID = @SourceTZ
      AND CONVERT(DATE,DATEADD(MINUTE,t.StandardOffsetMinutes,@Origin)) 
          >= src.[Year]
      AND CONVERT(DATE,DATEADD(MINUTE,t.StandardOffsetMinutes,@Origin)) 
          < DATEADD(YEAR, 1, src.[Year])
  ) AS src
  INNER JOIN dbo.TZCalendar AS trg
     ON CONVERT(DATE,src.UTC) >= trg.[Year] 
    AND CONVERT(DATE,src.UTC) < DATEADD(YEAR, 1, trg.[Year])
  INNER JOIN dbo.TimeZones AS tz
  ON trg.TimeZoneID = tz.TimeZoneID
  WHERE trg.TimeZoneID = @TargetTZ
  AND tz.TimeZoneID = @TargetTZ
);

And we can use the function as follows (again, I've chosen values that hit our edge cases):

DECLARE @d TABLE
(
  ID INT,
  Origin DATETIME,
  SourceTimeZone TINYINT,
  TargetTimeZone TINYINT
);
INSERT @d VALUES
(1, '2014-03-09 03:00', 2, 1),(2, '2014-03-09 03:00', 2, 8),
(3, '2014-03-09 03:00', 8, 1),(4, '2014-03-09 03:00', 8, 2),
(5, '2014-03-09 02:30', 2, 1),(6, '2014-11-02 05:30', 1, 2),
(7, '2014-11-02 06:30', 8, 2),(8, '2014-11-02 02:30', 8, 1),
(9, '2014-11-02 05:30', 8, 2),(10,'2014-11-02 06:30', 7, 2);
SELECT d.Origin, f.OriginTimeZone, f.[Target], f.TargetTimeZone
FROM @d AS d 
OUTER APPLY dbo.ConvertBetweenTimeZones
(d.Origin, d.SourceTimeZone, d.TargetTimeZone) AS f;

Results:

Results of the conversions

Feel free to try the function with any other input date/time values and time zones (at least of those that I've populated for you). You can compare the results with online services like Helloka's World Time Buddy.

You may wish to include other information in the output, for example the abbreviation of the destination time zone (which you could easily add to the TimeZones table), or a flag indicating that a Daylight Saving Time adjustment took place. You could also probably come up with ways to short circuit the function (for example, if the source and target time zone are the same) or to make the logic smarter, such that you don't have to switch to UTC first. In my next installment, I'll talk about enhancing this function to take into account the preferred time zone of users.

Next Steps


Last Updated: 2014-03-10


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, August 11, 2016 - 4:34:32 PM - Will Back To Top

 Thanks for this tip. I implemented this today and it will be a huge help!!

 


Friday, September 11, 2015 - 3:44:32 AM - Gert Back To Top

Hi

Thx for this post, it was really helpful.
I was wondering if you ever have created a function where you could use this setup, but say that the origin value is in the "standard"-mode all year.

For example: we have a situation where we will have a lot of metered values from around the world that needs to be converted both to UTC and to the local time for that timezone. The metered values will always start out in standard time (or what we call "normal" or "Winter" time). So then I would like to say to the function that the "origin"-date is in normal/standard time, but please convert it to UTC or local time.

I guess that was not so easy to understand, so please let me know if I explained poorly and I will try to refrase.

I think I might find a way With Your solution as a starting point, but if you already have done this, I would appreciate a pointer.

Again, thank you for sharing.


Friday, July 18, 2014 - 4:39:23 PM - James Back To Top

Great article. Thank you for posting this.


Learn more about SQL Server tools