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

# Calculating time differences between multiple rows

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
Currently Being Moderated
• ###### 2. Re: Calculating time differences between multiple rows
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
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
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
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
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
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
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
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
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
Currently Being Moderated
Thanks for all your help and modifications, I appreciate them all!

#### Legend

• Correct Answers - 10 points