Dealing with a Heisenbug here...please help.

[DavesChillaxin]

So I'm dealing with a Heisenbug ~ A computer bug that disappears or alters its characteristics when an attempt is made to study it.

The scenario:
I have a Windows Service running on a server, hitting a stored procedure say every 10 minutes. This stored procedure pull records from a table containing agent check-ins for events they've been assigned to. This I'm sure couldn't be any easier...if it wasn't for dealing with multiple time zones, but we have to. This has to handle all time zones in north america (for now). That part was hard, but I was able to accomplish it. The way it works is by converting all check in times and times of the event into a 'local time'. The local time would always be the time the server resides in. This all works fine, times take into consideration of DST and everything is accurate. Now here is where the bug becomes noticeable...

I'll run the stored procedure manually, passing say 12:10 PM 1/17/2013 as the check time. This should return all agents that have no existing check ins where there should be (within this time period), and it does! But when I run the same stored procedure again, same parameters, it will return a single agent with no check ins for an event occurring 09:00 AM EST - 11:00AM EST on 1/13/2013, which as you can see is no where near the date passed. So if I run it again a few more times I'll receive the same results, correct ones and then random ones with just a single record with occurrences of days or even weeks before the checking time.

This bug has been haunting me for a months now, and there just seems to be no excuse for what it's doing (not enough consistent results to point a finger in the right direction).

Just ask if there is anything else you'll need to possible help me in my endeavors.

[DavesChillaxin]

I GOT IT! But I need help explaining why.

The issue was with the time conversions. Till now I had never noticed it. But I've created a view as follows...

SELECT        Today, TodayUTC, Offset, ActualOffset
FROM            (SELECT        GETDATE() AS Today, GETUTCDATE() AS TodayUTC, - DATEPART(hour, GETUTCDATE() - GETDATE()) + DATEPART(minute, GETUTCDATE() - GETDATE()) 
                                                    * 100 / 60 AS Offset, dbo.fn_ActualTimezoneOffset() AS ActualOffset) AS tblSystemDates

The purpose for this was to dynamically obtain the offset of the server where the database would reside (this value is used in my calculations obviously) So this works...sometime, but when I view the view over and over again I'll get the correct offset -6 and then a random completely inaccurate offset of 93.

Now my question is why do these result vary?? I'm lead to believe it's an internal issue I'm unaware of or could even be a phenomena I've stumbled across, but then again I could be wrong.

[techgnome]

I think it has to do with the way you're pulling the time apart, running the calc then trying to put it back together.... just use the DateDiff using hours between UTC and GetDate... that's what you really need... at the moment you're taking UTCDate removing GetDate, taking the hour of that result, negating it, then adding in the difference in minutes (which really should be the same) ... then I'm not sure what's with the *100/60 ... I think I do... but I don't think it's necessary... what you want is a final result in hours, right..... so just do that... start with the UTC time and get the date Diff in hours between UTC and local time.

-tg

[DavesChillaxin]

ahhh DateDiff(), I'm pretty sure that was not part of my vocabulary at the time I wrote this. I think all the mumbo-jumbo I've provided was to get a more accurate offset and not just in hours, I'd like the minutes to. I'll give your suggest a run at work in a few and let you know how the results go. I was also thinking of just replacing this with a function - and again this was a while ago so I'm not even 100% sure why I chose to use a view in the first place.

thanks tg!