Time Zone Storage and Conversion
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: RobertFord from an article by stribbed
Date: 17 Nov 2008
Description
This was an article that I came across several years back. Normally with articles of this complexity, I would retain the original link and/or create an .mht page archive so the information was guaranteed to be available. However, in this case I didn't do that.
The technique is too good to not publish, especially as I too have worked for a company with global presence that faced similar challenges.
I will reproduce the original comment text below too:
Introduction
I work for a rather large company that spans countries and time zones, with multiple data centers in multiple locations.
While working on a project where data was coming from different locations into a common data repository,
we ran into a situation. Our application developers had not setup the application to convert date-time values
into a common time zone; each application used the local time of the servers that hosted the application.
In engaging the vendor, it was soon discovered that it was cost prohibitive to re-write the application to do so.
One of the design aspects of the application architecture included MS SQL Server 2005 as the back-end database software.
I did a lot of research on Microsoft's website, and the only time zone conversion functions that I could
find were relative to analysis services, and would not provide me with a solution to my problem.
So, my options were to pay someone to modify our application, or create a process in the database environment to perform
the conversions as data is inserted into the common database environment.
Using the Code
A few years ago, we were primarily an Oracle database shop.
Oracle natively comes with some time zone conversion functions.
My approach to resolve this issue was to setup an environment in MS SQL Server 2005 similar to the environment
that I was used to in Oracle (a scalar function called NEW_TIME that took three parameters; date to convert,
original time zone value, conversion time zone, and the function returns the converted value).
The only thing about the Oracle date time conversion functions that I did not like was that you have
to know whether you want to use the standard or the daylight code for the same time zone (e.g., CST vs. CDT).
I think that it would be better for the functions to be able to determine whether the date should be converted
using the daylight or standard offset.
First, I created a TIME_ZONES table to store the time zone conversion parameters (see the table structure below).
The main challenge that I had in putting together this table was to determine how to come up with a way to use a
generic code for the daylight start and end dates that would be applicable for any year, so that I did not have
to maintain a table of actual dates and times that show when day light savings starts or ends for each time zone.
So, I came up with a code that I could convert date time values to in order to determine if that date falls within
day light or standard time. The code is formatted as follows; MMWDHHmm. MM = two digit month (e.g., March = 03),
W = the week of the month (e.g., the second week of the month = 2), D = the day of the week, Sunday is the start
of the week which starts at 1 (e.g., Monday = 2), HH = two digit hour, 24 hour time (e.g., 2:00 am = 02, and 2:00 pm = 14),
mm = two digit minute (e.g., 35 minutes after the hour is 35). Example: On Sunday,
on the second week of the month, for the month of March, at 2:00 am, would be: 03210200.
An example of one of the records is as follows:
TIMEZONE_CD = 'CT',
TIMEZONE_NAME = 'CENTRAL TIME',
OFFSET_HR = -6,
OFFSET_MI = 0,
DST_OFFSET_HR = -5,
DST_OFFSET_MI = 0,
DST_EFF_DT = 03210200,
DST_END_DT = '11110200' ,
EFF_DT = '11/30/2008',
END_DT = '12/31/9999'.
After the table was created and populated, I started on the creation of the functions.
I started with the function to convert a provided date-time to Universal (UTC) or
Greenwich time (GMT). The function that I setup takes two parameters; the date-time to convert,
and the time zone code. The function declares some variables, and then populates them with the values
from the TIME_ZONES table using the provided time zone code. It then checks to see if the date-time
provided was within the daylight effective and end dates, so that it would know which offset to adjust
the provided date time with, and lastly, it returns the adjusted date-time in UTC time.
The next function that I needed to create was one that would convert time from UTC time to a
specified time zone, similar to the GET_UTCTIME function. This one takes two parameters;
the date-time to be converted and the time zone code to convert the provided date-time to.
The function declares some variables, then it converts the provided date-time to the DST parameter
format (MMWDHHmm). Next, it uses the provided time zone code to get the parameters from the TIME_ZONES
table, and populates the variables with the returned values. Last, it checks to see if the provided
date-time falls within the daylight range, and then applies the proper offset value, and then returns
the adjusted date-time value.
Now that I have the two functions to convert time to and from UTC, I can now create the main function that I will
use for most queries, NEW_TIME. This function will use the previous two functions to deliver the desired results.
This function takes three parameters: the date-time to be converted, the time zone code of the provided date-time,
and the time zone to convert the provided date-time to. The function starts by checking to see if the initial time
zone code is either UTC or GMT. The reason for this is that if the starting point is one of those, then the function
will not need to convert it to UTC time. If the initial time zone is not GMT or UTC, then the provided date time is
converted to UTC time using the provided initial time zone code, and that value is applied to the @NEWDT variable. If the
code is UTC or GMT, then the @NEWDT variable is set to the provided date-time. Next, the function converts the value in the
@NEWDT variable using the GET_TZTIME function and the second time zone code provided, and returns the converted value.
Final Solution Architecture
The final solution looks like this. We have our local applications with their data stores.
We setup database replication so that shortly after the record has been written to the local database,
it is replicated to the central data store. The central data store tables have triggers on them that convert
the local date-time to UTC time. The records have a field that stores the original local date-time, and another field
to store the converted UTC date-time. All of our reports use the UTC time, and the time zone functions to convert
the date-time to the desired time zone of the report user.
So far, I have not run into any issues with this setup to require any changes, though I am sure it is not
perfect and could use some adjustments here and there. For the application that this was specifically designed for,
it has worked out really well.
Points of Interest
The one annoying thing that I have not had a chance to research about is that in order to use the functions,
you are required to use the schema owner name prior to the function name (e.g., dbo.NEW_TIME()).
I am sure that there is a way around this, but I have not come across it yet, and it can be annoying at
times to remember to always use that, since I did not have to when working with Oracle previously, or
with other database objects within the MS SQL Server 2005 environment.
The only alteration that I could foresee to the code might be to extend the timezones table to have a year component to the DST validity code, or a Year column in the data. Probably the latter would be easier to implement. DST switchover dates are subject to fluctuation or rule changes as has been seen with North American figures in the time since this code was originally produced.
Code
Create TIME ZONES table:
DROP TABLE [dbo].[TIME_ZONES]
GO
CREATE TABLE [dbo].[TIME_ZONES]
(
[TIMEZONE_CD] VARCHAR (6) NOT NULL ,
[TIMEZONE_NAME] VARCHAR (60) NOT NULL ,
[OFFSET_HR] INT NOT NULL ,
[OFFSET_MI] INT NOT NULL ,
[DST_OFFSET_HR] INT NOT NULL ,
[DST_OFFSET_MI] INT NOT NULL ,
[DST_EFF_DT] VARCHAR (10) NOT NULL ,
[DST_END_DT] VARCHAR (10) NOT NULL ,
[EFF_DT] DATETIME NOT NULL,
[END_DT] DATETIME NOT NULL
)
Create the udf_GET_UTCTIME scalar function:
DROP FUNCTION dbo.udf_GET_UTCTIME
GO
CREATE FUNCTION dbo.udf_GET_UTCTIME
(
@DT AS DATETIME,
@TZ AS VARCHAR(12)
)
RETURNS DATETIME
AS
BEGIN
-- DECLARE VARIABLES
DECLARE
@NEWDT DATETIME,
@OFFSETHR INT,
@OFFSETMI INT,
@DSTOFFSETHR INT,
@DSTOFFSETMI INT,
@DSTDT VARCHAR(10),
@DSTEFFDT VARCHAR(10),
@DSTENDDT VARCHAR(10)
-- GET THE DST parameter from the provided datetime
-- This gets the month of the datetime provided (2 char value)
SELECT @DSTDT = CASE LEN(DATEPART(month, @DT))
WHEN 1 THEN '0' + CONVERT(VARCHAR(2),DATEPART(month, @DT))
ELSE CONVERT(VARCHAR(2),DATEPART(month, @DT))
END
-- This gets the occurrence of the day of the week within the month
-- (i.e. first Sunday, or second Sunday...) (1 char value)
SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),(DATEPART(day,@DT) + 6) / 7)
-- This gets the day of the week for the provided datetime (1 char value)
SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),DATEPART(dw, @DT))
-- This gets the hour for the provided datetime (2 char value)
SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(hh, @DT))
WHEN 1 THEN '0' + CONVERT(VARCHAR(2),DATEPART(hh, @DT))
ELSE CONVERT(VARCHAR(2),DATEPART(hh, @DT))
END
-- This gets the minutes for the provided datetime (2 char value)
SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(mi, @DT))
WHEN 1 THEN '0' + CONVERT(VARCHAR(2),DATEPART(mi, @DT))
ELSE CONVERT(VARCHAR(2),DATEPART(mi, @DT))
END
-- This query gets the timezone information
-- from the TIME_ZONES table for the provided timezone
SELECT
@OFFSETHR = offset_hr,
@OFFSETMI = offset_mi,
@DSTOFFSETHR = dst_offset_hr,
@DSTOFFSETMI = dst_offset_mi,
@DSTEFFDT = dst_eff_dt,
@DSTENDDT = dst_END_dt
FROM time_zones
WHERE timezone_cd = @TZ
AND @DT BETWEEN eff_dt AND end_dt
-- Checks to see if the DST parameter
-- for the datetime provided is within the DST
-- parameter for the timezone
IF @DSTDT BETWEEN @DSTEFFDT AND @DSTENDDT
BEGIN
-- Increase the datetime by the hours
-- and minutes assigned to the timezone
SET @NEWDT = DATEADD(hh,ABS(@DSTOFFSETHR),@DT)
SET @NEWDT = DATEADD(mi,ABS(@DSTOFFSETMI),@NEWDT)
END
-- If the DST parameter for the provided datetime is not within the defined
-- DST eff and end dates for the timezone then use the standard time offset
ELSE
BEGIN
-- Increase the datetime by the hours
-- and minutes assigned to the timezone
SET @NEWDT = DATEADD(hh,ABS(@OFFSETHR),@DT)
SET @NEWDT = DATEADD(mi,ABS(@OFFSETMI),@NEWDT)
END
-- Return the new date that has been converted to UTC time
RETURN @NEWDT
END
Create the udf_GET_TZTIME scalar function:
DROP FUNCTION dbo.udf_GET_TZTIME
GO
CREATE FUNCTION dbo.udf_GET_TZTIME
(
@DT AS DATETIME,
@TZ AS VARCHAR(12)
)
RETURNS DATETIME
AS
BEGIN
-- DECLARE VARIABLES
DECLARE
@NEWDT DATETIME,
@OFFSETHR INT,
@OFFSETMI INT,
@DSTOFFSETHR INT,
@DSTOFFSETMI INT,
@DSTDT VARCHAR(10),
@DSTEFFDT VARCHAR(10),
@DSTENDDT VARCHAR(10)
-- GET THE DST parameter from the provided datetime
-- This gets the month of the datetime provided (2 char value)
SELECT @DSTDT = CASE LEN(DATEPART(month, @DT))
WHEN 1 THEN '0' + CONVERT(VARCHAR(2),DATEPART(month, @DT))
ELSE CONVERT(VARCHAR(2),DATEPART(month, @DT)) END
-- This gets the occurrence of the day of the week within the month
--(i.e. first Sunday, or second Sunday...) (1 char value)
SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),(DATEPART(day,@DT) + 6) / 7)
-- This gets the day of the week for the provided datetime (1 char value)
SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),DATEPART(dw, @DT))
-- This gets the hour for the provided datetime (2 char value)
SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(hh, @DT))
WHEN 1 THEN '0' + CONVERT(VARCHAR(2),DATEPART(hh, @DT))
ELSE CONVERT(VARCHAR(2),DATEPART(hh, @DT))
END
-- This gets the minutes for the provided datetime (2 char value)
SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(mi, @DT))
WHEN 1 THEN '0' + CONVERT(VARCHAR(2),DATEPART(mi, @DT))
ELSE CONVERT(VARCHAR(2),DATEPART(mi, @DT))
END
-- This query gets the timezone information from the TIME_ZONES table
-- for the provided timezone
SELECT
@OFFSETHR=offset_hr,
@OFFSETMI=offset_mi,
@DSTOFFSETHR=dst_offset_hr,
@DSTOFFSETMI=dst_offset_mi,
@DSTEFFDT=dst_eff_dt,
@DSTENDDT=dst_END_dt
FROM time_zones
WHERE timezone_cd = @TZ
AND @DT BETWEEN eff_dt AND end_dt
-- Checks to see if the DST parameter for the datetime provided
-- is within the DST parameter for the timezone
IF @DSTDT BETWEEN @DSTEFFDT AND @DSTENDDT
BEGIN
-- Increase the datetime by the hours and minutes assigned to the timezone
SET @NEWDT = DATEADD(hh,@DSTOFFSETHR,@DT)
SET @NEWDT = DATEADD(mi,@DSTOFFSETMI,@NEWDT)
END
-- If the DST parameter for the provided datetime is not within the defined
-- DST eff and end dates for the timezone then use the standard time offset
ELSE
BEGIN
-- Increase the datetime by the hours and minutes assigned to the timezone
SET @NEWDT = DATEADD(hh,@OFFSETHR,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI,@NEWDT)
END
-- Return the new date that has been converted from UTC time
RETURN @NEWDT
END
Create the udf_NEW_TIME scalar function:
DROP FUNCTION dbo.udf_NEW_TIME
GO
CREATE FUNCTION dbo.udf_NEW_TIME
(
@DT AS DATETIME,
@TZ1 AS VARCHAR(12),
@TZ2 AS VARCHAR(12)
)
RETURNS DATETIME
AS
BEGIN
-- Declare variables
DECLARE @NEWDT AS DATETIME
-- Check to see if the provided timezone
-- for the source datetime is in GMT or UTC time
-- If it is not then convert the provided datetime to UTC time
IF NOT @TZ1 IN ('GMT','UTC')
BEGIN
SELECT @NEWDT = dbo.udf_GET_UTCTIME(@DT,@TZ1)
END
ELSE
-- If the provided datetime is in UTC or GMT time
-- then set the NEWTIME variable to this value
BEGIN
SET @NEWDT = @DT
END
-- Check to see if the provided conversion timezone is GMT or UTC
-- If it is then no conversion is needed.
-- If it is not then convert the provided datetime to the desired timezone
IF NOT @TZ2 IN ('GMT','UTC')
BEGIN
SELECT @NEWDT = dbo.udf_GET_TZTIME(@NEWDT,@TZ2)
END
-- Return the new converted datetime
RETURN @NEWDT
END