11 Replies Latest reply on May 15, 2012 10:33 AM by 936796

# Calculating time differences between multiple rows

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
• ###### 2. Re: Calculating time differences between multiple rows
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
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
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
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
Very nice once it's understood. How do you check the data for coherence?
• ###### 7. Re: Calculating time differences between multiple rows
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
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
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
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
Thanks for all your help and modifications, I appreciate them all!