mssqltips logo

Handle conversion between time zones in SQL Server - part 3

By:   |   Updated: 2014-04-01   |   Comments (2)   |   Related: 1 | 2 | 3 | More > Dates

Problem

In my first tip on time zone conversions, I demonstrated how to use a small calendar table and a user-defined function to easily translate date/time values between two time zones, incorporating offsets due to Daylight Saving Time. Another thing you may want to support is the ability to convert to a user's preferred time zone without requiring them to specify it every time.

Solution

In this tip, I'm going to expand upon the previous examples by adding a Users table, where each user has a pre-defined preferred time zone. I'll also show how to use the user-defined function to show all attendees of a meeting when the meeting will occur in their own time zone.

So first, we'll create a Users table, and populate it with some users. Remember from the previous tip that we have a set of 8 time zones we currently support:

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.Users
(
  UserID INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(32) NOT NULL,
  TimeZoneID TINYINT NOT NULL FOREIGN KEY
    REFERENCES dbo.TimeZones(TimeZoneID)
  /* , other columns, surely */
);
INSERT dbo.Users(UserID,Name,TimeZoneID) VALUES
(1,N'Bob',2),   -- New York office
(2,N'Frank',5), -- San Francisco office
(3,N'Nigel',7); -- London office

Now we can change our previous function to also take the UserID, and only require either the source or the destination TimeZoneID. This way, the user can specify a local time to convert to a destination time zone, or a time in a different time zone to convert to their own, and in both cases they wouldn't have to specify their own TimeZoneID. For example:

CREATE FUNCTION dbo.ConvertBetweenTimeZones2
(
  @Origin   DATETIME,
  @SourceTZ TINYINT = NULL,
  @TargetTZ TINYINT = NULL,
  @UserID   INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN 
 (
  WITH z AS 
  (
    SELECT 
      SourceTZ = COALESCE(@SourceTZ, TimeZoneID),
      TargetTZ = COALESCE(@TargetTZ, TimeZoneID)
    FROM dbo.Users WHERE UserID = @UserID
  )
  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
    INNER JOIN z ON src.TimeZoneID = z.SourceTZ 
      AND t.TimeZoneID = z.SourceTZ
    WHERE 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
  INNER JOIN z ON trg.TimeZoneID = z.TargetTZ
  AND tz.TimeZoneID = z.TargetTZ
);

Just for a quick sample usage, we can see how we would convert a date/time value in UTC to each of our three users' time zones:

SELECT u.UserID, f.Origin, f.OriginTimeZone,
    f.[Target], f.TargetTimeZone
  FROM dbo.Users AS u
  OUTER APPLY dbo.ConvertBetweenTimeZones2
  ('20140309 03:45', 1, u.TimeZoneID, u.UserID) AS f;

Results:

UserID  Origin            OriginTimeZone  Target            TargetTimeZone
------  ----------------  --------------  ----------------  ---------------------
1       2014-03-09 03:45  UTC             2014-03-08 22:45  Eastern Standard Time
2       2014-03-09 03:45  UTC             2014-03-08 19:45  Pacific Standard Time
3       2014-03-09 03:45  UTC             2014-03-09 02:45  Greenwich Mean Time

It's useful to note, though, that pulling the TimeZoneID from the Users table is, by design, not required. This gives the same results, since the TimeZoneID is pulled in by the function:

SELECT u.UserID, f.Origin, f.OriginTimeZone,
    f.[Target], f.TargetTimeZone
  FROM dbo.Users AS u
  OUTER APPLY dbo.ConvertBetweenTimeZones2
  ('20140309 03:45', 1, NULL, u.UserID) AS f;
------------------------^^^^

Not very helpful in this sample usage, but let's consider a more elaborate example; let's say we have a Meetings table, which stores information about a meeting (including the scheduled time, in UTC of course), and a MeetingUsers table, which indicates all of the users who will be participating in the meeting.

CREATE TABLE dbo.Meetings
(
  MeetingID INT PRIMARY KEY,
  Description NVARCHAR(255),
  ScheduledTime DATETIME NOT NULL,
  TimeZoneID TINYINT NOT NULL DEFAULT (1)
    FOREIGN KEY REFERENCES dbo.TimeZones(TimeZoneID)
  /* other columns too, surely */
);
INSERT dbo.Meetings VALUES
(1,N'Discussion on HR Policies','20140208 11:00',1),
(2,N'Review of new HR Policies','20140415 11:30',1);
CREATE TABLE dbo.MeetingUsers
(
  MeetingID INT NOT NULL FOREIGN KEY 
    REFERENCES dbo.Meetings(MeetingID),
  UserID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.Users(UserID),
  PRIMARY KEY (MeetingID,UserID) 
);
INSERT dbo.MeetingUsers VALUES
(1,1),(1,3),(2,1),(2,2),(2,3);

So now, we want to derive the meetings, which users are involved, and what time zone each user should expect to attend the meeting. We can do this with this simple query:

SELECT m.Description, mu.UserID, f.[Target], f.TargetTimeZone
FROM dbo.Meetings AS m
INNER JOIN dbo.MeetingUsers AS mu
ON m.MeetingID = mu.MeetingID
OUTER APPLY dbo.ConvertBetweenTimeZones2
(m.ScheduledTime,m.TimeZoneID,NULL,mu.UserID) AS f;

Results:

Description                UserID  Target            TargetTimeZone
-------------------------  ------  ----------------  ---------------------
Discussion on HR Policies  1       2014-02-08 06:00  Eastern Standard Time
Discussion on HR Policies  3       2014-02-08 10:00  Greenwich Mean Time
Review of new HR Policies  1       2014-04-15 07:30  Eastern Daylight Time
Review of new HR Policies  2       2014-04-15 04:30  Pacific Daylight Time
Review of new HR Policies  3       2014-04-15 11:30  British Summer Time

Since the function knows how to get the TimeZoneID of the user, we don't have to add joins to pull the source or target time zone from the Users table (and while you could argue that you'd also need to pull the user name, that could potentially be added to the output of the above query and be satisfied by a skinnier index than one that includes the TimeZoneID column).

For an application that performs a lot of queries that convert date/time values between time zones based on user preferences, this simplification can pay off across a lot of code. And in cases where the source or target time zone is known and shouldn't depend on any specific user, you can still choose to use the previous version of the function.

Next Steps


Last Updated: 2014-04-01


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist 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.





Wednesday, April 02, 2014 - 10:06:43 AM - Aaron Bertrand Back To Top

Hi Gabriel,

(1) I did not use DateTimeOffset because it is not DST-aware. Jon Skeet has some explanation here - if I have to add all kinds of extra things to handle DST, what does DateTimeOffset buy me?

(2) It is a common misconception that any T-SQL function will be faster when implemented as a CLR function. This is not true; CLR certainly has its beneficial use cases, but typically strict data access is not one of them (I employ it for a few scenarios that are slower, ***bersome or impossible in T-SQL, such as string splitting, file system interaction, and RegEx).

Aaron


Wednesday, April 02, 2014 - 9:04:21 AM - Gabriel I. Ruiz Back To Top

Hi Aron;

I read the code and it looks good.  Thanks for sharing it with us.

I wonder why you did not use the Data Type 'DateTimeOffset', which

includes provisions for time zone offset. It was introduce in SQL Srver 2008.

Also, As I understand it, and please correct me if I am wrong,

The best way to implement UDFs is through the CLR (Common Language Runtime)

using C# or something similar.  I believe UDF implemented in the CLR

run faster and are more efficient than T-SQL UDF.  I wonder if anyone else

out there has any oppinion about this.  Thanks     Gabe Ruiz 



download

























get free sql tips

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.



Learn more about SQL Server tools