3

Background

I have a scenario where I am calculating the difference between two dates. While the increment differences are spot on, the final calculation has introduced a 60 minute (1 hour) disparity.

After investigation and "hair pulling" episodes, I have identified that the DST transition in November is the cause for the 60 minute (1 hr) disparity.

Scenario

declare @sdate datetime = '2016-10-29 06:03:00.000PM'
declare @edate datetime = '2016-11-29 11:59:00.000PM'

select 
    DATEDIFF(HOUR, @sdate, @edate),
    DATEDIFF(Minute, @sdate, @edate),
    DATEDIFF(Second, @sdate, @edate)

Question

Ultimately, I need to simply return the number of seconds, or minutes, between the @sdate and @edate variables. I know there will be two times during the year, where the difference value will be off by 60 minutes (1 hour), plus or minus, and want to account for that known disparity within my sql statement.

How can i account for the DST adjustment within a set-based operation, if possible?

Currently, I am getting 44996 as the difference, but that is the un-adjusted time-change difference. I am looking for 45056, which is the adjusted time-change difference.

GoldBishop
  • 2,820
  • 4
  • 47
  • 82

1 Answers1

3

The datetime type in SQL Server has no awareness of time zone or offset from UTC. In order to take DST into account, you need to use the datetimeoffset type.

declare @sdate datetimeoffset = '2016-10-29 06:03:00.000PM -05:00'
declare @edate datetimeoffset = '2016-11-29 11:59:00.000PM -06:00'

select 
    DATEDIFF(Hour, @sdate, @edate),
    DATEDIFF(Minute, @sdate, @edate),
    DATEDIFF(Second, @sdate, @edate)

This will give the results you asked for, taking into account that the offsets for the start and end differed by an hour.

The tricky part is how to determine the offset to begin with. If you are running SQL 2016, or Azure SQL DB, then you could use the AT TIME ZONE function to determine it.

declare @dt datetime = '2016-10-29 06:03:00.000PM'
declare @dto = @dt AT TIME ZONE 'Central Standard Time'

But since you said you are running SQL 2012, you'll have to either write your own functions that understand when DST starts and ends in your time zone, or you can use my SQL Server Time Zone Support package to do that:

declare @dt datetime = '2016-10-29 06:03:00.000PM'
declare @tz varchar = 'America/Chicago'
declare @dto = Tzdb.SwitchZone(Tzdb.LocalToUtc(@dt, @tz, 1, 1), @tz)

Note there is an open item to simplify this to a single function, but the above should work for now.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Ok....so for TZ sensitive evaluations, i need to place the datetime value into the `datetimeoffset` type and perform operations off that? I have not done much development, in the ways of `i18n` or spanning `l10n`. Guess it is time to start learning ;) Either way, after asking the question, found out all time has been standardized to CST. – GoldBishop Dec 06 '16 at 16:55
  • Localization doesn't really help you learn about time zones. They are separate concepts really. Also, keep in mind that it's likely your times are standardized to Central time, but that doesn't mean CST necessarily, as you have to consider CDT as well. Really, the best practice would be to normalize all dates and times to UTC in the database. `datetimeoffset` offers an alternative that works, but you have to ensure you have the correct offsets. :) – Matt Johnson-Pint Dec 06 '16 at 17:08
  • Some resources for you to learn more: [timezone tag wiki](http://stackoverflow.com/tags/timezone/info); [dst tag wiki](http://stackoverflow.com/tags/dst/info); [best practices](http://stackoverflow.com/q/2532729); [datetime vs datetimeoffset](http://stackoverflow.com/q/4331189); [Pluralsight](https://www.pluralsight.com/courses/date-time-fundamentals) – Matt Johnson-Pint Dec 06 '16 at 17:10
  • Yeah, i asked about CDT and it was confirmed all time is CST. I have other products that span the US, not yet Int'l, so `datetimeoffset` will be very useful for time-syncing data points. – GoldBishop Dec 06 '16 at 19:07