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.