Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dealing with Daylight Savings Time


Dealing with Daylight Savings Time

Author
Message
kramaswamy
kramaswamy
SSC Eights!
SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)

Group: General Forum Members
Points: 859 Visits: 1788
Hi all,

I'm having an issue regarding dates that are stored in one format vs another, as far as Daylight Savings Time (DST) is concerned.

The values in the table are stored in a DST-insensitive fashion. It is being stored as the number of miliseconds since Jan 1st, 1970 GMT. What I'm trying to do is extract all values that fall between a given set of dates, time-inclusive.

Initially, I had used a solution involving simply using GETDATE() and comparing it against the the DST-insensitive values. The problem was that, during DST times, all results were off by an hour, which meant that I was getting the wrong values.

Someone proposed that I use the following formula to deal with this:


DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) - 24, DATEADD(ss, StartTime / 1000.0, '1970-01-01 00:00:00'))



This worked fine for a while, but I'd been noticing oddities recently, where the values being returned kept "randomly" changing.

What I discovered eventually is that there are times where GETDATE() and GETUTCDATE(), executed on the same row of the query, occasionally return different results:


GETDATE() : 2012-10-31 13:58:48.933
GETUTCDATE() : 2012-10-31 17:58:48.940



As you can see, there is a 0.07 milisecond difference in those two values. What this means is that when I take my function above and run it, instead of getting a 4-hour time offset, I get a 5-hour time offset, which completely messes up the results.

After looking at that, I've re-written my formula to be the following:


DATEADD(hh, -1 * (24 + ROUND(DATEDIFF(ss, GETDATE() - GETUTCDATE(), '1899-12-31 00:00:00') / 3600.0, 0)), DATEADD(ss, StartTime / 1000.0, '1970-01-01 00:00:00'))



This seems to work - I'm consistently getting the same time now. The questions that I have are the following:

1) Will this continue to work when DST ends? I assume it will, but I can't really effectively test it.
2) Is this reliable? Will it consistently work?
3) Is this the best way of going about it, or is there a better way?
kramaswamy
kramaswamy
SSC Eights!
SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)

Group: General Forum Members
Points: 859 Visits: 1788
Hi there - hate to bump a thread, but does anyone have any answers for this?
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14383 Visits: 9729
Does your data take "leap seconds" and things like that into account? There have been several over the last 42 years. They don't matter to very many people, but if you're measuring events with millisecond precision, it may matter.

Also, you'll need to take into account that the "Spring Ahead" day is an hour shorter, per the clock, than every other day of the year, while the "Fall Back" day is an hour longer. (Yes, we have 1 day per year that's 23 hours long and another that's 25 hours long. Only politicians could inflict that kind of insanity on the world.)

Plus, the schedule of the start and end of DST has changed a few times, so which day is shorter and which one is longer, depends on the year you're asking about.

To accommodate all that kind of confusion, I think a Calendar table is really your best bet. A table with all the dates you need, including something like a century of future dates, with a DayStart and DayEnd value in each, which contains the start and end millisecond values for each date. It's going to be a little bit of a pain to build, but, once built, you won't have to worry about the math on it. Just join to that table using Between on those two columns, and you've got your date range.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
crmitchell
crmitchell
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 Visits: 1715
kramaswamy (10/31/2012)
Hi all,

What I discovered eventually is that there are times where GETDATE() and GETUTCDATE(), executed on the same row of the query, occasionally return different results:


GETDATE() : 2012-10-31 13:58:48.933
GETUTCDATE() : 2012-10-31 17:58:48.940



As you can see, there is a 0.07 milisecond difference in those two values. What this means is that when I take my function above and run it, instead of getting a 4-hour time offset, I get a 5-hour time offset, which completely messes up the results.



You should expect them to return different values - it takes time to execute a function call and these are executed serially.
If you need them to represent the same time you need to extract the time into a variable first and then operate on that variable rather than sequentially calling functions to get dates.
tyson.price
tyson.price
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 642
GSquared (11/6/2012)
Does your data take "leap seconds" and things like that into account? There have been several over the last 42 years. They don't matter to very many people, but if you're measuring events with millisecond precision, it may matter.

Also, you'll need to take into account that the "Spring Ahead" day is an hour shorter, per the clock, than every other day of the year, while the "Fall Back" day is an hour longer. (Yes, we have 1 day per year that's 23 hours long and another that's 25 hours long. Only politicians could inflict that kind of insanity on the world.)

Plus, the schedule of the start and end of DST has changed a few times, so which day is shorter and which one is longer, depends on the year you're asking about.

To accommodate all that kind of confusion, I think a Calendar table is really your best bet. A table with all the dates you need, including something like a century of future dates, with a DayStart and DayEnd value in each, which contains the start and end millisecond values for each date. It's going to be a little bit of a pain to build, but, once built, you won't have to worry about the math on it. Just join to that table using Between on those two columns, and you've got your date range.


And you have to through into the caledar not everyone in the US follows DST. Hawaii, U.S. territories, and most of Arizona stay with standard time.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14383 Visits: 9729
tyson.price (11/12/2012)
GSquared (11/6/2012)
Does your data take "leap seconds" and things like that into account? There have been several over the last 42 years. They don't matter to very many people, but if you're measuring events with millisecond precision, it may matter.

Also, you'll need to take into account that the "Spring Ahead" day is an hour shorter, per the clock, than every other day of the year, while the "Fall Back" day is an hour longer. (Yes, we have 1 day per year that's 23 hours long and another that's 25 hours long. Only politicians could inflict that kind of insanity on the world.)

Plus, the schedule of the start and end of DST has changed a few times, so which day is shorter and which one is longer, depends on the year you're asking about.

To accommodate all that kind of confusion, I think a Calendar table is really your best bet. A table with all the dates you need, including something like a century of future dates, with a DayStart and DayEnd value in each, which contains the start and end millisecond values for each date. It's going to be a little bit of a pain to build, but, once built, you won't have to worry about the math on it. Just join to that table using Between on those two columns, and you've got your date range.


And you have to through into the caledar not everyone in the US follows DST. Hawaii, U.S. territories, and most of Arizona stay with standard time.


Yep.

It's a complex thing, turning milliseconds since the 70s into accurate date-time values. Unix servers use a clock chip for it, in many cases. It's not a well-defined dataspace, because it's completely arbitrary (anything is, as soon as politicians and bureaucrats take control of it).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search