CODEWITHSUNDEEP
sqlsql-servert-sqldst
James Wilson
James Wilson

Reputation: 5150

Daylight savings time and UTC time

We use a program that saves the time-stamps in UTC time. We are a local to Utah company so we are affected by Daylight Savings time.

For example if we receive a call right now it is 12:52:00 MST and it would be saved in the database as 19:52:00.

My first concern is next year when DST starts again on March 13th 2016 and I run this at the exact same time. Will the time stamp in UTC be then 18:52:00 or would it stay at 19:52:00?

My second concern is if I convert the date in the database to my local time so I have to first check if it DST and then if it is take the time -6 and if not it would be -7?

So using the above example: 

IsDST = 01:52:00 (-6)
IsNotDST = 12:52:00 (-7)

I assume this is something I need to worry about having to convert to/from UTC?

My main question aside from the two concerns above. Is there anything built into SQL Server/T-SQL that handles this conversion for me or do I need to write everything myself to take care of the need?

I have it started already, but now need to work in the DST if it is necessary

DECLARE @declared_start_datetime DATETIME, 
        @declared_end_datetime DATETIME, 
        @converted_start_datetime DATETIME, 
        @converted_end_datetime DATETIME

SET @declared_start_datetime = '11/04/2015 07:00:00' -- Hour we open phones
SET @declared_end_datetime = '11/04/2015 18:00:00' -- Hour we close phones
SET @converted_start_datetime = DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), @declared_start_datetime)
SET @converted_end_datetime = DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), @declared_end_datetime)

select @declared_start_datetime as 'Declared Start', 
       @declared_end_datetime as 'Declared End'
select @converted_start_datetime as 'Converted Start',
       @converted_end_datetime as 'Converted End'

Upvotes: 2

Views: 4046

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241563

For example if we receive a call right now it is 12:52:00 MST and it would be saved in the database as 19:52:00.

My first concern is next year when DST starts again on March 13th 2016 and I run this at the exact same time. Will the time stamp in UTC be then 18:52:00 or would it stay at 19:52:00?

Mountain Standard Time is UTC-7, and US Mountain Daylight time is UTC-6. It's a lot easier to reason about if you write out the full date, time, and offset(s) involved in the conversion. Here it is in standard ISO8601 extended format:

2015-11-06T12:52:00-07:00 = 2015-11-06T19:52:00Z
2016-03-13T12:52:00-06:00 = 2016-03-13T18:52:00Z

Each local time on the left side of the equation is marked with the correct local time and local offset for that time. Then to get to UTC (identified by Z), you simply subtract the offset from the local time. Or, think of it as inverting the sign and adding, if that's easier to rationalize.

So yes, it would store it at 18:52:00 UTC when you are in daylight time. This is the correct behavior.

My second concern is if I convert the date in the database to my local time so I have to first check if it DST and then if it is take the time -6 and if not it would be -7?

Yes, but keep in mind that it's the date and time reflected by the timestamp you're converting. It makes no difference whether you are currently in DST or not.

However, keep in mind that time zone conversion should usually be avoided in the database layer, if you can all help it. In the vast majority of use cases, it's an application layer concern.

For example, if you're writing to SQL Server from an application built in .NET, then you could use the TimeZoneInfo class with the "Mountain Standard Time" ID (which is for both MST and MDT). Or, you could use the Noda Time library with the TZDB identifier of "America/Denver".

By using a library, you don't have to concern yourself with all of the various details of when DST starts and stops, nor how it has changed throughout history in different parts of the world.

In the rarer case where you actually need time zone conversion done at the database level, you can certainly write a stored procedure or UDF of your own (such as some of the question comments linked to), but depending on your needs they may not be sufficient. Typically they tend to encode just one set of fixed rules for time zone conversions, so they won't take other time zones or historical changes into account.

There are a few generic time zone solutions for SQL Server, but unlike other database platforms, there's nothing built in. I'll recommend my SQL Server Time Zone Support OSS project, and there are others if you search. But really, you should hopefully not need this, and should do the conversion in the application layer whenever possible.

Update: With SQL Server 2016 CTP 3.1, there is now built-in support for time zones via the AT TIME ZONE statement. See the CTP announcement for examples.

Upvotes: 3

Related Questions