T-SQL Tuesday #26: Second chances
January 10th, 2012
T-SQL Tuesday #26: Second chances
January 10th, 2012
 
 

This month's T-SQL Tuesday is being hosted by Dave Howard (@DaveH0ward), and the topic is "Second Chances." What he's letting us do is look back at all the previous T-SQL Tuesdays and pick a topic that we missed or maybe thought we needed another crack at.

I chose a recent topic that I missed due to travel, "Data Presentation," hosted by Robert Pearl in September 2011 (T-SQL Tuesday #22).

At my previous job, one of the most complicated pieces of presentation work we had to do was to provide users with data in their preferred time zone. We separated data by customer in separate databases, which would have made things relatively easy, if only all the users of each customer were in the same time zone. In that case, we could have just stored all the customer's data in that time zone.

But of course, customers had offices spread throughout the country and in fact the world, so we had to implement a way to show the same data, translated to local time, for users in different time zones. A complication to the requirement was that, based on the user's login to the web application, we needed to show the data in their preferred time zone even if they were using a computer in a different office – so we couldn't rely on the web browser's preferences or local machine's regional settings to determine the time zone. And in addition to these requirements, we also had to know the actual UTC offset for given dates, both in the past and in the future, since several of the supported time zones would observe daylight saving time.

In order to fulfill these requirements, we decided to implement the logic within the database. This way the application wouldn't have to know anything except who the user was, and the logic would work the same way regardless of which application was requesting the data (we also had several applications that weren't tied to the web application, that wouldn't always know the user, so each customer had a default to fall back on). The servers in the data center were all set to GMT and to *not* observe daylight savings time. This is actually on my informal best practices list because it prevents ever having to worry about data collisions or gaps twice a year.

So here is a skeleton of the schema and minimal data to demonstrate the functionality. First I'll let you create a dummy database to target:

USE [master];
GO
 
IF DB_ID('TSQLTuesdayAaron') IS NOT NULL
BEGIN
    DROP DATABASE TSQLTuesdayAaron;
END
GO
 
CREATE DATABASE TSQLTuesdayAaron;
GO
 
USE TSQLTuesdayAaron;

Now we need tables TimeZones and UTCOffsets:

CREATE TABLE dbo.TimeZones
(
    TimeZoneID TINYINT     PRIMARY KEY,
    Name       VARCHAR(32) NOT NULL UNIQUE
);
GO
 
INSERT dbo.TimeZones(TimeZoneID, Name) 
VALUES
    (1,'Eastern US Time'),
    (2,'Greenwich Mean Time');
 
CREATE TABLE dbo.UTCOffsets
(
    TimeZoneID TINYINT       NOT NULL,
    [Date]     SMALLDATETIME NOT NULL,
    Offset     SMALLINT      NOT NULL,
    PRIMARY KEY CLUSTERED(TimeZoneID, [Date])
);
GO
 
ALTER TABLE [dbo].[UTCOffSets] ADD CONSTRAINT [FK_utc_TZ] 
    FOREIGN KEY([TimeZoneID]) 
    REFERENCES [dbo].[TimeZones] ([TimeZoneID]);

For brevity, I'm just going to populate the transition days, e.g. when daylight savings time changes. In actuality, the table is a calendar table; each date from 2005-01-01 through 2030-12-31 is represented, once for each time zone:

INSERT dbo.UTCOffsets(TimeZoneID, [Date], Offset) 
VALUES
    (1,'2011-03-12',-5),
    (1,'2011-03-13',-4),
    (1,'2011-11-05',-4),
    (1,'2011-11-06',-5),
    (1,'2012-03-10',-5),
    (1,'2012-03-11',-4),
    (1,'2012-11-03',-4),
    (1,'2012-11-04',-5),
    (2,'2011-03-26', 0),
    (2,'2011-03-27', 1),
    (2,'2011-10-29', 1),
    (2,'2011-10-30', 0),
    (2,'2012-03-24', 0),
    (2,'2012-03-25', 1),
    (2,'2012-10-27', 1),
    (2,'2012-10-28', 0);

Next, we need some customers and users:

CREATE TABLE dbo.Customers
(
    CustomerID  INT          PRIMARY KEY,
    Name        NVARCHAR(64) NOT NULL UNIQUE,
    TimeZoneID  TINYINT      NOT NULL
);
GO
 
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [FK_c_TZ] 
    FOREIGN KEY([TimeZoneID]) 
    REFERENCES [dbo].[TimeZones] ([TimeZoneID]);
GO
 
INSERT dbo.Customers(CustomerID, Name, TimeZoneID)
VALUES
    (1, N'Teks', 1),
    (2, N'MSDN', 2);
GO
 
CREATE TABLE dbo.Users
(
    UserID     INT          PRIMARY KEY,
    Email      VARCHAR(320) NOT NULL UNIQUE,
    CustomerID INT          NOT NULL,
    TimeZoneID TINYINT      NOT NULL
);
GO
 
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_u_TZ] 
    FOREIGN KEY([TimeZoneID]) 
    REFERENCES [dbo].[TimeZones] ([TimeZoneID]);
 
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_u_Cust] 
    FOREIGN KEY([CustomerID])
    REFERENCES [dbo].[Customers] ([CustomerID]);
GO
 
INSERT dbo.Users(UserID, Email, CustomerID, TimeZoneID)
VALUES
    (1, '[email protected]', 1, 1),
    (2, '[email protected]', 1, 2),
    (3, '[email protected]', 2, 1);

And then functions to determine the offset for a given day and user or customer:

CREATE FUNCTION [dbo].[GetUTCOffSet_ByUserID]
(
    @UserID INT,
    @Date   SMALLDATETIME
)
RETURNS SMALLINT
AS
BEGIN
    RETURN
    (
        SELECT utc.Offset 
            FROM dbo.UTCOffsets AS utc
            INNER JOIN dbo.Users AS u
            ON utc.TimeZoneID = u.TimeZoneID
            WHERE utc.[Date] = DATEDIFF(DAY, 0, @Date)
            AND u.UserID = @UserID
    );
END
GO
 
CREATE FUNCTION [dbo].[GetUTCOffSet_ByCustomerID]
(
    @CustomerID  INT,
    @Date        SMALLDATETIME
)
RETURNS SMALLINT
AS
BEGIN
    RETURN
    (
        SELECT utc.Offset 
            FROM dbo.UTCOffsets AS utc
            INNER JOIN dbo.Customers AS c
            ON utc.TimeZoneID = c.TimeZoneID
            WHERE utc.[Date] = DATEDIFF(DAY, 0, @Date)
            AND c.CustomerID = @CustomerID
    );
END
GO

Given a date/time value and a user or company, we could now determine the offset that should be used, depending on the known CustomerID or UserID:

SELECT dbo.GetUTCOffset_ByCustomerID(1, '20111106'),
       dbo.GetUTCOffset_ByCustomerID(2, '20111029'),
       dbo.GetUTCOffset_ByUserID(1, '20111106'),
       dbo.GetUTCOffset_ByUserID(2, '20111029'),
       dbo.GetUTCOffset_ByUserID(3, '20111106');

Once a user is logged in, we could simply store their timezone in a session variable, so we wouldn't have to retrieve that value more than once. For any date we needed to display, we could just pass in the date and the timezone to the following function:

CREATE FUNCTION [dbo].[GetPresentationDateTime]
(
    @TimeZoneID TINYINT,
    @DateTime   SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
    RETURN
    (
        SELECT DATEADD(HOUR, @Offset, @DateTime);
    );
END
GO
 
SELECT dbo.GetPresentationDateTime(1, '20111106 04:32'),
       dbo.GetPresentationDateTime(2, '20111029 04:32');

We could have done all of the above in a single, overloaded function or procedure, but decided it would be better to make the access methods much more defined. You also might suggest DATETIMEOFFSET, but this solution was initially implemented in SQL Server 2005, long before the new date/time data types were introduced. Besides, I'm not sure how it would have helped, since most values were entered without any TZ awareness (e.g. GETUTCDATE()), relying on the system clock to store UTC date/time correctly. Most data did not need to be timezone aware until presented to the user, and since it could be different for every user, the value of storing one user's TZ information with the data was pretty weak.

The above didn't really help if we wanted to pull a report with lots of datetime values – we would have to call the scalar function for every row (and sometimes for multiple values) – in those cases we just wrote out a join the long way (if we didn't already know the offset) or used DATEADD inline when we did.

You're probably thinking, "this isn't quite complete." And you'd be right. There's that funky time we haven't accounted for – the first couple of hours in each DST-observing time zone on the forward/back day, before the change is actually supposed to take place. We opted to defer that since most events happen during business hours, and the issue never came up with any of our customers. Certainly a concern you will want to take into account if you're thinking about this type of solution.

And in the spirit of keeping with the topic, if I were to be presented with this same challenge today, I would probably use it as a second chance to get the job done a little better, rather than take the easy "I-solved-this-before" route.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.