By: Aaron Bertrand | Last Updated: 2014-03-04 | Comments (20) | Dates
Time zones have always made the SQL Server professional's life difficult. Before SQL Server 2008, conversion between time zones was completely up to the developer. Simple if you were converting today's date and time from, say, UTC to local time - that is very easy to do on either the server or the client. But not so simple if you were converting points in time both in and out of Daylight Saving Time, never mind to multiple time zones or between two different non-UTC time zones. It gets even more fun when converting between two time zones that both observe Daylight Saving Time but change their clocks on different days of the year.
SQL Server 2008 introduced a new data type called DATETIMEOFFSET (along with a function called SWITCHOFFSET), which was immediately heralded as the solution to all of these time zone conversion issues. The problem here is that DATETIMEOFFSET is not DST-aware (Leonard Lobel talks a bit about this here): it assumes that you've already incorporated any DST adjustments into the offset you specify. In several Connect items you will see discussions about future enhancements that will incorporate DST, but unfortunately even those promises - which haven't been delivered in SQL Server 2014 - only seem to be talking about supporting conversion to and from a *local* time zone with DST. In other words, it would use the operating system to allow adjustments from the local system's time zone for today's date, but wouldn't be very helpful for other dates or when considering other, non-local time zones.
So people started inventing their own solutions (or continuing to use the ones they employed before SQL Server 2008). These usually consist of long, tedious and hard-to-maintain scalar user-defined functions that take a date/time, parse all of its components, determine if it is within the boundaries of the target time zone's DST range, and then adjust by the appropriate number of minutes. These functions often have minor errors in logic, neglect DST rule changes for different years, and are extremely complex even while dealing with only one or a limited number of time zones. I've seen several solutions that have had a different function for each target time zone, or many redundant chunks of logic to deal with each relevant time zone. Many make the mistake of converting a UTC date/time to the local time zone by applying the difference between GETDATE() and GETUTCDATE(), but like other scenarios, this only works when running the code today (or for a date that happens to be in the same DST range). I'd reference some examples, but I don't want to call out any of my peers. :-)
I have long been a proponent of calendar tables for solving problems like this, among many others - I first wrote about these in 2004. In this series of tips, I'm going to describe some ways to convert data between time zones, with a focus on both minimizing the complexity of the calling code and maximizing performance by using the smallest calendar table possible.
One scenario that is certainly easy to address is the simplest case: you store all of your date/time values in UTC, and need to convert those dates to a single time zone. In this case, we'll use the Eastern time zone of the USA. For this we can use a calendar table with one row per year, where each year has a DST start and a DST end expressed in both UTC and the local time (more on this below).
CREATE TABLE dbo.TZCalendar ( [Year] DATE PRIMARY KEY, UTC_DST_Start SMALLDATETIME NOT NULL, UTC_DST_End SMALLDATETIME NOT NULL, ET_DST_Start AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -4, UTC_DST_Start)), ET_DST_End AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -5, UTC_DST_End)) );
The most complicated part of this scenario is populating the calendar table. Most people would use a loop, but I much prefer building a set-based solution. As I mentioned above, I'm going to assume that you don't need to go back earlier than 2000, and I'm also going to assume that you only need to go 50 years into the future (you may need to adjust for your requirements if they are different). But first I should explain the factors that influence the details of the set-based solution I derive.
For the Eastern time zone, in the Spring, we move from 2 AM to 3 AM local time, at 7 AM UTC ("Spring forward"). And in the Fall, we move from 2 AM to 1 AM local time, at 6 AM UTC ("Fall back"). Thankfully, since 2000 (well, since 1987), there are only two different conditions for determining the dates of these events: until 2006, we Spring forward on the first Sunday in April, and Fall back on the last Sunday in October. In 2007, this changed such that we Spring forward on the second Sunday in March, and fall back on the first Sunday in November. So, for example, in 2014, we will switch to DST on March 9th, and fall back on November 2nd. We use the local time values in the table only to rule out invalid dates (e.g. 2:20 AM, in Eastern Time, on the morning we Spring forward from 2 AM to 3 AM, is not a possible local time, because it never happened).
With all that in mind, here is the set-based solution I came up with:
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'), 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 ) INSERT dbo.TZCalendar([Year],UTC_DST_Start,UTC_DST_End) SELECT d, -- First Sunday in April (< 2007) or second Sunday in March (>= 2007): DATEADD(HOUR, 7, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,2+p,d))+1)%7 +(7*ABS(p-1)),DATEADD(MONTH,2+p,d))), -- Last Sunday in October (< 2007) or first Sunday in November (>= 2007): DATEADD(HOUR, 6, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,d))+1)%7 -(7*p),DATEADD(MONTH,10,d))) FROM cte ORDER BY d;
This creates a table with 65 rows that is a whopping 16 KB in size. As long as there isn't something really strange going on in your system, it should be in memory all of the time (and when it isn't, pulling two pages from disk will be largely inconsequential anyway). The data looks like this:
Year DTStart DTEnd ET_DST_Start ET_DST_End ---------- ---------------- ---------------- ---------------- ---------------- 2000-01-01 2000-04-02 07:00 2000-10-29 06:00 2000-04-02 03:00 2000-10-29 01:00 2001-01-01 2001-04-01 07:00 2001-10-28 06:00 2001-04-01 03:00 2001-10-28 01:00 2002-01-01 2002-04-07 07:00 2002-10-27 06:00 2002-04-07 03:00 2002-10-27 01:00 ... 2013-01-01 2013-03-10 07:00 2013-11-03 06:00 2013-03-10 03:00 2013-11-03 01:00 2014-01-01 2014-03-09 07:00 2014-11-02 06:00 2014-03-09 03:00 2014-11-02 01:00 2015-01-01 2015-03-08 07:00 2015-11-01 06:00 2015-03-08 03:00 2012-11-01 01:00 ... 2062-01-01 2062-03-12 07:00 2062-11-05 06:00 2062-03-12 03:00 2062-11-05 01:00 2063-01-01 2063-03-11 07:00 2063-11-04 06:00 2063-03-11 03:00 2063-11-04 01:00 2064-01-01 2064-03-09 07:00 2064-11-02 06:00 2064-03-09 03:00 2064-11-02 01:00
Now, how do we use this data? We can create two simple functions; one that takes a date/time value in UTC, and converts it to Eastern time, and another that takes a date/time value in Eastern time, and converts it to UTC. There are two edge cases that you need to be aware of:
- If I pass in a UTC time like 2014-11-02 06:30, and another UTC time like 2014-11-02 07:30, both will yield the same time (2014-11-02 01:30) when converted to Eastern Time. This is because at 1:30 Eastern Daylight Time, it was 06:30 UTC, then at 06:00 UTC (2:00 AM Eastern Daylight Time) the clocks rolled back to 1:00 AM Eastern Standard Time, and half an hour later - at 06:30 UTC - the Eastern time zone happened upon 1:30 AM again. I'll leave it up to you how you want to disambiguate this when displaying these values (since you have the original UTC time, you can tell which ones are four hours apart, and which are five hours apart).
- If I pass in a local time like 2014-03-09 02:30, I will return NULL (you could, alternatively, raise an error). This is because that time couldn't have actually happened - at 02:00 Eastern Standard Time we moved the clocks forward to 03:00 Eastern Daylight Time. So any event claiming to have happened inside that hour is bogus. In the function below, I chose to return NULL, but you obviously have other options if you want to change it.
CREATE FUNCTION dbo.ConvertUTCToLocal ( @utc DATETIME ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT UTCToLocal = DATEADD(HOUR, CASE -- within Daylight Savings Time WHEN @utc >= UTC_DST_Start AND @utc < UTC_DST_End THEN -4 -- within Standard Time ELSE -5 END, @utc) FROM dbo.TZCalendar WHERE CONVERT(DATE,@utc) >= [Year] AND CONVERT(DATE,@utc) < DATEADD(YEAR, 1, [Year]) ); GO CREATE FUNCTION dbo.ConvertLocalToUTC ( @local DATETIME ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT LocalToUTC = DATEADD(HOUR, CASE -- account for the "skipped" hour that never happened WHEN @local >= DATEADD(HOUR,-1,ET_DST_Start) AND @local < ET_DST_Start THEN NULL -- within Daylight Saving Time WHEN @local >= ET_DST_Start AND @local < ET_DST_End THEN 4 -- within Standard Time ELSE 5 END, @local) FROM dbo.TZCalendar WHERE CONVERT(DATE,DATEADD(HOUR, -5, @local)) >= [Year] AND CONVERT(DATE,DATEADD(HOUR, -5, @local)) < DATEADD(YEAR, 1, [Year]) ); GO
And we can use these functions in the following way. I've purposely used date values that brush against our edge cases in one direction or the other.
DECLARE @x TABLE(ID INT PRIMARY KEY, SourceDateTime DATETIME); INSERT @x(ID, SourceDateTime) VALUES (1, '20131231 23:59'), (2, '20140101 00:01'),(3, '20140309 01:59'),(4, '20140309 02:00'), (5, '20140309 06:59'),(6, '20140309 07:00'),(7, '20140601 00:00'), (8, '20141102 01:59'),(9, '20141102 02:00'),(10,'20141102 02:30'), (11,'20141102 03:00'),(12,'20141102 05:59'),(13,'20141102 06:59'), (14,'20141102 07:00'),(15,'20141231 18:59'),(16,'20141231 19:01'); SELECT * FROM @x AS x CROSS APPLY dbo.ConvertUTCToLocal(x.SourceDateTime) AS l;
Results of the conversion from UTC to local time:
SELECT * FROM @x AS x CROSS APPLY dbo.ConvertLocalToUTC(x.SourceDateTime) AS l;
Results of the conversion from local time to UTC:
In my next installment, I'll talk about expanding this solution to allow converting between multiple time zones, rather than just to and from UTC.
- Consider using a calendar table to vastly simplify your time zone conversions in SQL Server.
- Read the following tips and other resources:
- Converting UTC to local time with SQL Server CLR
- Convert SQL Server DateTime Data Type to DateTimeOffset Data Type
- Daylight Savings Time Functions in SQL Server
- Daylight saving time and time zone best practices (Stack Overflow)
- DATETIMEOFFSET and SWITCHOFFSET (MSDN)
- Daylight saving time in the United States (WikiPedia)
Last Updated: 2014-03-04
About the author
View all my tips