Daylight savings time and UTC time

Question

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'

Your question isn't clear at all. You seem to be aware of `GETUTCDATE()`, you say you want to use UTC timestamps (good choice), why are you even dealing with local timestamps? — Amit, Nov 05 '15 at 20:08
Which time is the exact same time: `12:52:00` MST or MDT? There is a difference which is important along with noting how daylight savings starts at different times and not that long ago had that shift. — JB King, Nov 05 '15 at 20:11
These reports are going to be ran from other individuals in the company who will select times (local times) for the report to run. And I'm foreshadowing the amount of complaints/backlash from them having to learn/convert to UTC on thier own. So I need to take their local times and convert them to UTC to get the correct records from the database. @amit — James Wilson, Nov 05 '15 at 20:12
@JBKing The correct time right now is MST (-7) March 13th of next year at 2:00 am it will turn into MDT (-6) if my research is correct. — James Wilson, Nov 05 '15 at 20:13
The "user story" your telling is a problem of the presentation or business layer, not the db/storage layer. deal with it over there. — Amit, Nov 05 '15 at 20:16
If your program truly "saves the time-stamps in UTC time" then those UTC time stamps are not affected by daylight savings time. DST comes in when you *convert* between UTC and local time. That is in fact one very good reason to do as you say you're doing. — John Bollinger, Nov 05 '15 at 20:45
@JohnBollinger So I guess the issue I am having is I think I understand now UTC will always be the same. But right now since it isn't DST I need to convert it -7 hours but during DST I need to convert it to -6 hours. I guess that is where my hangup is currently, but I think I can figure it out had to be with DATEADD. — James Wilson, Nov 05 '15 at 20:52
@JamesWilson, it sounds like you're looking for something such as is discussed here: http://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement — John Bollinger, Nov 05 '15 at 21:35

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

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.

Thanks Matt. I am not using an application unfortunately and so needed to write the DST conversion stuff that way. I take their supplied date and check if it is in DST or not then change the date to either 6or7 and then use that to grab the values from the database. It is a custom report requested. I will check out the Time Zone Support project you mentioned. — James Wilson, Nov 09 '15 at 16:11
Keep in mind the time of day of the transition, and also that in the fall, values in transition period may be ambiguous. For example, `2015-11-01T01:30` might be `2015-11-01T01:30-06:00` *OR* `2015-11-01T01:30-07:00`. You'll need to decide which to choose. If in doubt, most scenarios call for the first (daylight) occurrence. Though sometimes one can disambiguate based on other correlated data. — Matt Johnson-Pint, Nov 09 '15 at 16:52

Daylight savings time and UTC time

1 Answers1