Results 1 to 4 of 4
  1. #1
    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
    appreciate your help

  2. #2
    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
    Cheers....

    baburajv

  3. #3
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Use datediff function to achieve this

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jassi.singh View Post
    Use datediff function to achieve this
    please give an example of how datediff will work in this situation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •