Joining 2 tables to calculate the time difference

Question

We have a couple of seperate event tables. For a given id we would like to know the time difference between 2 events. Now, there can be several of the same events for a given id, we are only interested in the "next" event.

Example:

http://sqlfiddle.com/#!4/1f724/6/0

Schema

create table event_a (
  timestmp timestamp(3),
  id       varchar2(50)
);

create table event_b (
  timestmp timestamp(3),
  id       varchar2(50)
);

insert into event_a values (to_timestamp('2015-05-12 10:22:00', 'YYYY-MM-DD HH24:MI:SS'), 'x');
insert into event_b values (to_timestamp('2015-05-12 10:22:05', 'YYYY-MM-DD HH24:MI:SS'), 'x');
insert into event_a values (to_timestamp('2015-05-12 10:22:08', 'YYYY-MM-DD HH24:MI:SS'), 'x');
insert into event_b values (to_timestamp('2015-05-12 10:22:10', 'YYYY-MM-DD HH24:MI:SS'), 'x');

Query

This is the query I came up with, but it seems to me to be a little too complex. I was wondering if there is a better way to do this.

This query also takes a long time to run if there is a lot of data in these tables.

select a.id, nvl((extract(day from (b.timestmp - a.timestmp))   * 86400
                 + extract(hour from (b.timestmp - a.timestmp))   * 3600 
                 + extract(minute from (b.timestmp - a.timestmp)) * 60   
                 + extract(second from (b.timestmp - a.timestmp)) ), 0) as duration_in_sec
from event_a a
join event_b b on a.id = b.id and b.timestmp = (
  select min(timestmp)
  from event_b
  where id = a.id
  and timestmp >= a.timestmp
)

Possible duplicate of: http://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql — Christian Barron, May 12 '15 at 08:42
Not really Christian, I know how to calculate the difference between 2 timestamps, my question is more about the joining of these 2 tables to find the 2 timestamps to compare. — Pmarcoen, May 12 '15 at 08:49
Why are you extracting days, hours, minutes and seconds separately, if you're only interested in "duration in sec"? You can do datetime arithmetic on Oracle, so you can just do `(b.timestmp - a.timestmp) * 86400`. As for the performance, look at the execution plan. Guessing doesn't really get you anywhere. — Luaan, May 12 '15 at 08:52
Are you not better off using the DATEDIFF function as it would give you the difference in seconds without having to resort to picking apart the dates. DATEDIFF(s, b.timestmp, a.timestmp) — Tom, May 12 '15 at 08:53
Fair enough it did say possible, however the link does show a better way to do a datetime difference. — Christian Barron, May 12 '15 at 08:54
It looks like you're trying to get the minimum time for b for an id but what about a? Is that max or min? — Christian Barron, May 12 '15 at 08:56
@Christian: it takes the time from a and then finds the lowest timestamp with the same id for b. The query on itself works, you can try it in sqlfiddle. — Pmarcoen, May 12 '15 at 08:58
@Luaan: This returns "+05 00:00:00.000000" instead of a numeric value. Perhaps this works for dates but not for timestamps? — Pmarcoen, May 12 '15 at 08:59
@Alex, yes there are, and they can help. I'm just not sure if my query is optimal. — Pmarcoen, May 12 '15 at 09:06
Aaah, of course, my bad. So annoying. You could of course do the multiplication and then use `extract(day)`, but that's even uglier. — Luaan, May 12 '15 at 09:18

score 3 · Answer 1 · answered May 12 '15 at 09:11

3

This solution should be faster as it does not have self-join subquery:

select id, extract(day from (d)) * 86400 + extract(hour from (d)) * 3600 
         + extract(minute from (d)) * 60  + extract(second from (d)) duration
  from (
    select a.id, b.timestmp - a.timestmp d,
        row_number() over (partition by a.id, a.timestmp order by b.timestmp) rn
      from event_a a 
      join event_b b on a.id = b.id and a.timestmp <= b.timestmp
      order by a.timestmp)
  where rn = 1

SQLFiddle

answered May 12 '15 at 09:11

Ponder Stibbons

14,723
2
21
24

That does indeed seem better! One thing though, I also need to partition on id, otherwise if a different id has an event on the same time, this query would only return 1 result. Updated: http://sqlfiddle.com/#!4/875a5/1/0 – Pmarcoen May 12 '15 at 09:29
Yes, I corrected my answer, not corrected SQLFiddle, – Ponder Stibbons May 12 '15 at 09:34

Christian Barron · Answer 2 · 2015-05-12T09:25:08.363

2

You can try this and see if it works / performs better:

Select a.id, 86400 * (min(b.timestmp) - a.timestmp) diff
From event_a a
Join event_b b on a.id = b.id and b.timestmp >= a.timestmp
Group by a.id, a.timestmp

If this doesn't work for you then you can pretty much do your old query but using Group by:

Select a.id, nvl((extract(day from (min(b.timestmp) - a.timestmp))   * 86400
                 + extract(hour from (min(b.timestmp) - a.timestmp))   * 3600 
                 + extract(minute from (min(b.timestmp) - a.timestmp)) * 60   
                 + extract(second from (min(b.timestmp) - a.timestmp)) ), 0) as duration_in_sec
From event_a a
Join event_b b on a.id = b.id and b.timestmp >= a.timestmp
Group by a.id, a.timestmp

edited May 12 '15 at 09:25

answered May 12 '15 at 09:10

Christian Barron

2,695
1
14
22

That also needs `and b.timestmp >= a.timestmp` in the join (and the OP's extraction to seconds, not multiplying by 24, since they are timestamps not dates). Then it works for the sample data at least. – Alex Poole May 12 '15 at 09:13
Thanks @AlexPoole you're quite correct. Updated to reflect this – Christian Barron May 12 '15 at 09:16
Yes Christian, that does seem better, it works using the "old" time difference. Thanks! – Pmarcoen May 12 '15 at 09:31
Great, I wasn't sure as I don't have Oracle, just seen someone saying the top should work but SQL Fiddle didn't like it so thought I'd post the second just in case. The Group By method should be more efficient for you hopefully. – Christian Barron May 12 '15 at 09:41
Hmm, it definately should be more efficient, the explain plan is also a lot less complex with only 2x"table access" and a "hash join" vs 4x"table access" and 3x"hash joins". The only thing is, on the actual tables, this takes 143 seconds where my old solution only takes 105 seconds. – Pmarcoen May 12 '15 at 09:48
Do they use your indexes when it runs? It's a bit odd as I'd have expected it to run faster. Are your indexes covering indexes? – Christian Barron May 12 '15 at 10:00

score 1 · Answer 3 · answered May 12 '15 at 09:28

If the timestamps of event_a and event_b always alternate then you can use:

SQLFIDDLE

WITH times AS (
  SELECT id, timestmp, 1 AS event_type FROM event_a
  UNION ALL
  SELECT id, timestmp, 2 AS event_type FROM event_b
  ORDER BY 1,2,3
),
timediffs AS (
  SELECT id,
         timestmp - LAG( timestmp ) OVER ( PARTITION BY id ORDER BY timestmp ASC, event_type ASC ) AS d,
         event_type
  FROM   times
)
SELECT id,
       extract(day from (d)) * 86400
        + extract(hour from (d)) * 3600 
        + extract(minute from (d)) * 60
        + extract(second from (d)) AS duration
FROM   timediffs
WHERE  event_type = 2;

Unfortunalely, I cannot be sure that they alternate. – Pmarcoen May 12 '15 at 09:34 — Pmarcoen, May 12 '15 at 09:34

Joining 2 tables to calculate the time difference

3 Answers3