2

In our system we have a Stored Procedure that creates shifts for users. These shifts have a specified start/end time and a duration. When these shifts cross a DST Boundary, the duration comes out incorrect. Take the following Example:

declare @start DATETIME = '2017-03-11 22:00:00.000',
@end DATETIME = '2017-03-12 06:00:00.000'
select (DATEDIFF(hh, @start, @end)) as 'Elapsed Hours'

This returns an elapsed hours of 8 hours. However, on 3/12 of this year, DST begins and clocks move an hour forward. So the actual elapsed hours for this time period is only 7. I know that in SQL 2016 I can use the AT TIME ZONE function to get around this, but unfortunately I have to support this in SQL 2008 - 2016.

I've found another question that seems to give me an idea: How to create Daylight Savings time Start and End function in SQL Server I could use these functions to get the DST start/end dates then do some calculation to see if the shift crosses one of these boundaries, then apply the appropriate offset but this seems like an ugly option to me. First of all, not all locations observe DST, and not all countries use the same DST Schedules... So I'm thinking there must be a better way to do this. Has anyone else in the community run across this problem and found a better way to handle it?

Community
  • 1
  • 1
Nathan24
  • 1,372
  • 1
  • 11
  • 20
  • 1
    Are you guaranteed that you will never have to determine what 01:30 means on the first Sunday in November (assuming the context is the USA)? When you "fall back" you will have an hour of duplicate times that you can't sort out based on local time. – HABO Feb 10 '17 at 19:49
  • That is a good question. I don't think that we should need to worry about which 1:30 it is. But I will need to research that to see how the application behaves during that time period. – Nathan24 Feb 10 '17 at 20:00
  • OK, no we don't need to worry about what that means. Because this is all logic for Shifts/Schedules of work times. A person who works 22:00 - 01:30 on any given day of the year, would always work only 3.5 hours even for on the date of the DST change, because their shift ends before the 02:00 cutover for DST. It only comes into play when someone works across that boundary as they either have to work an extra hour, or work one hour less. – Nathan24 Feb 10 '17 at 20:05
  • Have a look here: https://github.com/mj1856/SqlServerTimeZoneSupport – Matt Johnson-Pint Feb 20 '17 at 03:20

1 Answers1

3

Because of the uncertainty you've identified in DST schedules at different locations, it is often recommended to do all datetime calculations in UTC and display them in local time for clients. DST rules can change at any time based on local laws, even from city to city that UTC is by far the best way to calculate time.

Edit for additional clarity: Even the SQL 2016 fix will rely on human intervention and rule updates to be keep current as different countries, states, and cities make changes to their DST laws. UTC is the best consistently reliable tool at your disposal.

Forklift
  • 949
  • 8
  • 20
  • That is something I can agree on. Unfortunately with our software we have a notion that all times are stored in our Database using Local Time.... So to change that over to UTC would not be an easy task. – Nathan24 Feb 10 '17 at 19:22
  • @Nathan, that is unfortunate. Is this a situation where you can walk the information up the responsibility tree and make it someone else's problem? Or at least let them know that even with a server upgrade and complex functions, the nonUTC method will only be "pretty good". – Forklift Feb 10 '17 at 19:26
  • Yeah, I can't really make it someone else's problem. It's my responsibility to identify something we can fix before DST starts in March. Luckily most of our clients are all US Based, and I can just add in an override flag for clients in Arizona, or other similar locales that don't observe DST. But it looks like I will just have to go with the 'Pretty Good' option and this will allow me to make an argument for refactoring to a UTC method in the future. – Nathan24 Feb 10 '17 at 19:32
  • 1
    Yeah, sounds like "pretty good" is your best bet. And being that it's mostly US based, it's likely closer to "good enough." All that aside, I give you guys an A+ anyway for identifying a March 12 problem before March 12. We should all endeavor to be so pro-active. – Forklift Feb 10 '17 at 19:35
  • 1
    I would love to take that credit, but we discovered the problem last November when DST ended... Now we are trying to get it fixed before DST starts again. – Nathan24 Feb 10 '17 at 19:37