Data and Time Issue between two reports (utc)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Data and Time Issue between two reports (utc)

Author

Topic

nonsec
Starting Member

26 Posts

Posted - 2009-04-29 : 09:45:12

Hello everyone,

I have -4 hours time difference between system time and sql utc time. I am having issue with two reports, one is daily one is monthly. In two reports here is my daily query;

Both Report time is between 4/27/2009 12:00:00 AM to 4/27/2009 11:59:59 PM

SELECT TimeFromRaisedSeconds / 60 AS Minutes, COUNT(*) AS Counts
FROM Alert.vAlertResolutionState
WHERE (ResolutionState = 255) AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
GROUP BY TimeFromRaisedSeconds / 60
ORDER BY Minutes

My daily query output is;

Total counts for day 591

my monthly query is;

SELECT AddDate=dateadd(day,datediff(Day,0,StateSetDateTime),0),
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 0 THEN 1 ELSE NULL END) AS [0 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 1 THEN 1 ELSE NULL END) AS [1 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 2 THEN 1 ELSE NULL END) AS [2 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 = 3 THEN 1 ELSE NULL END) AS [3 Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 > 3 THEN 1 ELSE NULL END) AS [4+ Min],
COUNT (CASE WHEN TimeFromRaisedSeconds / 60 <= 100000 THEN 1 ELSE NULL END) AS [Total]
FROM Alert.vAlertResolutionState
WHERE ResolutionState = 255 AND (StateSetDateTime BETWEEN DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @startdate) AND DATEADD(HH, DATEDIFF(HH, GETDATE(), getutcdate()), @enddate))
Group by dateadd(Day,datediff(Day,0,StateSetDateTime),0)

Monthly query output is;


Date and Time		0M 	1M	2M	3M	4M	4+M
4/27/2009 12:00:00 AM	90	86	47	26	263	512
4/28/2009 12:00:00 AM	37	18	8	8	8	79

Total count is equal to daily report but it splits up to days.

I don`t know why SRS return different value, I assume there is something to do with UTC time part but I could not figure out. How can I fix this issue? or what is better approach for this to get accurate result from both query?

any input greatly appreciated.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 10:46:49

didnt understand issue here. are you looking at cumulative value for month rather than splitting up as days? then why group by day at all? just remove dateadd(day,datediff(Day,0,StateSetDateTime),0) from group by

nonsec
Starting Member

26 Posts

Posted - 2009-04-29 : 11:08:10

Visakh16,

Both Report time is between 4/27/2009 12:00:00 AM to 4/27/2009 11:59:59 PM

yes I am looking at cumulative value for month per day;

issue is with the cumulative value, Daily one returns Total Value; 591 Monthly one returns; 512 for the same day. which both should be same value. First one is ( grouping by minutes ) Second one ( grouping per day ).

is UTC time conversion correct in both query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 13:41:42

what does TimeFromRaisedSeconds hold?

nonsec
Starting Member

26 Posts

Posted - 2009-04-29 : 13:58:39

it is holding seconds and I convert them to minutes;

here is what looks like


Resolution State  TimeFromraisedSeconds  StateSetDateTime
255			156		2009-02-03 21:04:12.640
255			511		2009-02-03 21:10:49.717
255			726		2009-02-03 21:07:07.193
255			580		2009-02-03 21:07:42.333
255			40		2009-02-03 21:46:17.190
255			91		2009-02-03 22:12:50.770
255			1811		2009-02-03 22:58:55.970
255			1802		2009-02-03 22:59:42.580
255			1802		2009-02-03 22:59:42.610
255			897		2009-02-04 00:28:57.237

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources