October 31, 2012 at 12:57 pm
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?
November 6, 2012 at 1:56 pm
Hi there - hate to bump a thread, but does anyone have any answers for this?
November 6, 2012 at 2:19 pm
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
November 7, 2012 at 2:10 am
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.
November 12, 2012 at 4:34 am
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.
November 12, 2012 at 6:33 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply