This discussion is archived
11 Replies Latest reply: May 15, 2012 3:33 AM by 936796 RSS

Calculating time differences between multiple rows

936796 Newbie
Currently Being Moderated
Hi,

I have a table as below:

ID EVENT DATE
1 Start 03/05/2012 13:00:00
1 Stop 04/05/2012 15:00:00
1 Start 07/05/2012 09:00:00
1 Stop 09/05/2012 10:00:00
2 Start 06/08/2012 08:00:00
2 Stop 07/08/2012 10:00:00

I would like to calculate the total time span (in hours ideally) between 'Start' and 'Stop' for each ID, so have a rowset:

ID TimeSpan
---------------------
1 75
2 26

I would be grateful for any ideas how to solve this, ideally as a SQL solution or perhaps a function.

Many thanks!
Tom
  • 1. Re: Calculating time differences between multiple rows
    indra budiantho Expert
    Currently Being Moderated
    go this link..LAG analithycal function:
    http://www.adp-gmbh.ch/ora/sql/analytical/lag.html
  • 2. Re: Calculating time differences between multiple rows
    Stew Ashton Expert
    Currently Being Moderated
    The difference between two dates is a number where 1 = 1 day. Multiply by 24 to get hours.

    Use LAG, as indicated above, to compare the previous date to the current date.
    create table T(TID, EVENT, TDATE) as select
    1, 'start', TO_DATE('03/05/2012 13:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
    1, 'Stop',  TO_DATE('04/05/2012 15:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
    1, 'start', TO_DATE('07/05/2012 09:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
    1, 'Stop',  TO_DATE('09/05/2012 10:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
    2, 'start', TO_DATE('06/08/2012 08:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
    2, 'Stop',  TO_DATE('07/08/2012 10:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL;
    
    select tid, round(sum(hours)) hours from (
      select case
        when EVENT = 'Stop' then 24*(TDATE - LAG(TDATE) over(partition by TID order by TDATE))
        else 0 end hours,
      TID from T
    )
    group by tid;
    
    TID HOURS
    --- -----
      1    75 
      2    26
  • 3. Re: Calculating time differences between multiple rows
    Nicosa Expert
    Currently Being Moderated
    Hi,

    Just my 2 cents in addition : One could also add some checking to prevent weird data to make erroneous total :
    Scott@my11g SQL>l
      1  with T(TID, EVENT, TDATE) as (select
      2  1, 'start', TO_DATE('03/05/2012 13:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
      3  1, 'Stop',  TO_DATE('04/05/2012 15:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
      4  1, 'Stop',  TO_DATE('04/05/2012 16:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
      5  1, 'start', TO_DATE('07/05/2012 08:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
      6  1, 'start', TO_DATE('07/05/2012 09:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
      7  1, 'Stop',  TO_DATE('09/05/2012 10:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
      8  2, 'start', TO_DATE('06/08/2012 08:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL union all select
      9  2, 'Stop',  TO_DATE('07/08/2012 10:00:00', 'dd/mm/yyyy hh24:mi:ss') from DUAL
     10  )
     11  select TID, trunc(sum(ela)*24) totalhour
     12  from (
     13  select TID, EVENT, TDATE, case when event='Stop' and lag(event) over (partition by tid order by tdate)='start' then tdate-lag(tdate) over (partition by tid order by tdate) else 0 end ela
     14  from t
     15  )
     16  group by tid
     17* order by tid
    Scott@my11g SQL>/
    
           TID  TOTALHOUR
    ---------- ----------
             1         75
             2         25
    This should prevent some duplicate stop and start event to "pollute" the total.
    Not heavily tested thought...
    :-)
  • 4. Re: Calculating time differences between multiple rows
    Stew Ashton Expert
    Currently Being Moderated
    Nice idea. How about this? It will cause an exception if the start and stop rows don't alternate.
    select TID, ROUND(SUM(HOURS)) HOURS from (
      select case EVENT || LAG(EVENT,1,'Stop') over(partition by TID order by TDATE)
        when 'startStop' then 0
        when 'Stopstart' then 24*(TDATE - LAG(TDATE) over(partition by TID order by TDATE))
        else 1/0 end hours,
      TID from T
    )
    group by TID;
    By the way, you used TRUNC instead of ROUND so you lost an hour;)
  • 5. Re: Calculating time differences between multiple rows
    Solomon Yakobson Guru
    Currently Being Moderated
    Using analytic functions here is an overkill:
    select  tid,
            trunc(
                  sum(
                      case event
                        when 'Stop' then tdate - to_date(1,'j')
                        else to_date(1,'j') - tdate
                      end
                     ) * 24
                 ) totalhour
      from  t
      group by TID
      order by TID
    /
    
           TID  TOTALHOUR
    ---------- ----------
             1         75
             2         26
    
    2 rows selected.
    
    SQL> 
    SY.
  • 6. Re: Calculating time differences between multiple rows
    Stew Ashton Expert
    Currently Being Moderated
    Very nice once it's understood. How do you check the data for coherence?
  • 7. Re: Calculating time differences between multiple rows
    Solomon Yakobson Guru
    Currently Being Moderated
    Well, you can't check it as thorough as in your solution, but something like:
    SQL> select  tid,
      2          trunc(
      3                sum(
      4                    case event
      5                      when 'Stop' then tdate - to_date(1,'j')
      6                      else to_date(1,'j') - tdate
      7                    end
      8                   ) * 24
      9               ) totalhour,
     10               case count(case event when 'Stop' then 1 end)
     11                 when count(case event when 'start' then 1 end) then 1
     12                 else 1/0
     13               end coherence
     14    from  t
     15    group by TID
     16    order by TID
     17  /
    
           TID  TOTALHOUR  COHERENCE
    ---------- ---------- ----------
             1         75          1
             2         26          1
    
    2 rows selected.
    
    SQL> delete t where rownum = 1
      2  /
    
    1 row deleted.
    
    SQL> select  tid,
      2          trunc(
      3                sum(
      4                    case event
      5                      when 'Stop' then tdate - to_date(1,'j')
      6                      else to_date(1,'j') - tdate
      7                    end
      8                   ) * 24
      9               ) totalhour,
     10               case count(case event when 'Stop' then 1 end)
     11                 when count(case event when 'start' then 1 end) then 1
     12                 else 1/0
     13               end coherence
     14    from  t
     15    group by TID
     16    order by TID
     17  /
                   else 1/0
                         *
    ERROR at line 12:
    ORA-01476: divisor is equal to zero
    
    
    SQL> 
    SY.
  • 8. Re: Calculating time differences between multiple rows
    Nicosa Expert
    Currently Being Moderated
    Stew Ashton wrote:
    Nice idea. How about this? It will cause an exception if the start and stop rows don't alternate.
    That's neat ! I never used the default clause of LAG function.
    Stew Ashton wrote:
    By the way, you used TRUNC instead of ROUND so you lost an hour;)
    Allow me to disagree here. How many hours in 2 hours and 50 minutes ? Only 2, not 3.
    I guess the good answer here depends on OP's requirement.
    ;-)
    ------
    @ Solomon :
    Solomon Yakobson wrote:
    Using analytic functions here is an overkill:
    (...)
    You're totally right. Nice use of julian date here. I'm huge fan of the [url http://en.wikipedia.org/wiki/KISS_principle]KISS principle, but I failed in this case.
    Solomon Yakobson wrote:
    Well, you can't check it as thorough as in your solution, but something like:
    (...)
    This "enhancement" is very clever to my point of view. Nice way to raise error on "unwanted" data pattern.
  • 9. Re: Calculating time differences between multiple rows
    Stew Ashton Expert
    Currently Being Moderated
    Nicosa wrote:
    Stew Ashton wrote:
    By the way, you used TRUNC instead of ROUND so you lost an hour;)
    Allow me to disagree here. How many hours in 2 hours and 50 minutes ? Only 2, not 3.
    I guess the good answer here depends on OP's requirement.
    Disagreement welcome, but look at the OP's desired output for ID 2: 26 hours. Look at your output for ID 2: 25 hours. Remove the TRUNC and see what value you get, it's a bit surprising.
  • 10. Re: Calculating time differences between multiple rows
    Nicosa Expert
    Currently Being Moderated
    Stew Ashton wrote:
    Disagreement welcome, but look at the OP's desired output for ID 2: 26 hours.
    Oups.... Indeed, I 'missed that.
    <i>(I guess it because I'm not a fan of rounding. I either prefer to floor/trunc or ceil values)</i>
  • 11. Re: Calculating time differences between multiple rows
    936796 Newbie
    Currently Being Moderated
    Thanks for all your help and modifications, I appreciate them all!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points