Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dealing with Daylight Savings Time Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 12:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:04 PM
Points: 819, Visits: 1,657
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?
Post #1379492
Posted Tuesday, November 6, 2012 1:56 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:04 PM
Points: 819, Visits: 1,657
Hi there - hate to bump a thread, but does anyone have any answers for this?
Post #1381708
Posted Tuesday, November 6, 2012 2:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1381711
Posted Wednesday, November 7, 2012 2:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 217, Visits: 869
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.
Post #1381836
Posted Monday, November 12, 2012 4:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:26 AM
Points: 74, Visits: 620
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.
Post #1383606
Posted Monday, November 12, 2012 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1383665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse