# Thread: Calculate the difference between two dates

1. Registered User
Join Date
Sep 2011
Posts
1

## Unanswered: Calculate the difference between two dates

Hi,
i have a puzzle here and would appreciate if someone can help me here cuz i could not get the correct results. I have 2 columns: DateStamp and ResponseTime. If the ResponseTime shows 0 that means it is downtime but if it shows any other value other than zero then it is up time. So, i want to calculate the Total Down Time(sum of total minutes) for each hour where the ResponseTime was Zero. Here is sample data that i have:

DateStamp ResponseTime
2011-09-08 22:53:04.627 17.2442893981934
2011-09-08 22:49:26.721 15.0537271499634
2011-09-08 22:38:04.728 0
2011-09-08 22:42:09.720 18.25
2011-09-09 14:34:26.703 16.7527503967285
2011-09-09 14:39:04.467 0
2011-09-09 14:45:26.597 15.9180603027344
2011-09-10 16:08:04.793 19.2465705871582
2011-09-10 16:12:27.003 15.5914258956909
2011-09-10 16:33:04.230 0
2011-09-10 16:49:26.643 16.3721446990967

Here is what the result should look like:
if for instance we have this scenario:
2011-01-25 02:24:56.367 14.7515907287598
2011-01-25 02:17:23.627 15.7695226669312
2011-01-25 02:09:56.223 16.7297210693359
2011-01-25 02:02:25.327 0
2011-01-25 01:54:56.207 13.0773229598999

At 02:02:25, it was down but the next time at 02:09:56 it was up and if we don't see any other downtime for this hour then we can safely say the downtime for this hour was 7 minutes. So the final result should output like this:

DateTime DownTime
2011-01-25 02:02:25.327 7min

2. Registered User
Join Date
Feb 2004
Location
Bangalore, India
Posts
250
/*
Create Table Test1
(
DateStamp DateTime,
ResponseTime Numeric(20,13)
)

Insert Into Test1
(
DateStamp,
ResponseTime
)
Select '2011-01-25 02:24:56.367', 14.7515907287598
Union
Select '2011-01-25 02:17:23.627', 15.7695226669312
Union
Select '2011-01-25 02:09:56.223', 16.7297210693359
Union
Select '2011-01-25 02:02:25.327', 0
Union
Select '2011-01-25 01:54:56.207', 13.0773229598999
Union
Select '2011-09-08 22:53:04.627', 17.2442893981934
Union
Select '2011-09-08 22:49:26.721', 15.0537271499634
Union
Select '2011-09-08 22:38:04.728', 0
Union
Select '2011-09-08 22:42:09.720', 18.25
Union
Select '2011-09-09 14:34:26.703', 16.7527503967285
Union
Select '2011-09-09 14:39:04.467', 0
Union
Select '2011-09-09 14:45:26.597', 15.9180603027344
Union
Select '2011-09-10 16:08:04.793', 19.2465705871582
Union
Select '2011-09-10 16:12:27.003', 15.5914258956909
Union
Select '2011-09-10 16:33:04.230', 0
Union
Select '2011-09-10 16:49:26.643', 16.3721446990967;
*/

With Data (Record#, DateStamp, ResponseTime) As
(
Select Row_Number() Over (Order By DateStamp desc) As Record#,
DateStamp,
ResponseTime
From Test1
)
Select DownTime.DateStamp, DateDiff(mi, DownTime.DateStamp,UpTime.DateStamp)
From Data UpTime
Inner Join Data DownTime
On (UpTime.Record#+1 = DownTime.Record# )
And UpTime.DateStamp > DownTime.DateStamp
And DownTime.ResponseTime = 0
And UpTime.ResponseTime <> 0

3. Registered User
Join Date
Sep 2011
Posts
75
Hello,

Use datediff function to achieve this

4. SQL Consultant
Join Date
Apr 2002
Location