I am trying to get time difference in minutes between the event time of below data having the same nodeid and code for nodeid having count > 1
nodeid code event_time
CAI0015 14961045 2017-04-22 21:22:00
CAI0024 14961045 2017-04-23 19:44:00
CAI0024 14961045 2017-04-23 09:07:00
CAI0040 14971047 2017-04-23 13:58:00
CAI0046 14961045 2017-04-23 11:19:00
CAI0050 14961045 2017-04-24 02:06:00
output should be like this:
nodeid code difference(min)
CAI0024 14961045 637
Use the formula extract(epoch from <later> - <earlier>) / 60 to get timestamp difference in minutes.
To select all permutations of differences (where nodeid & code is the same) within the table, use a self-join:
select nodeid, code, extract(epoch from e2.event_time - e1.event_time) / 60 difference
from events e1
join events e2 using (nodeid, code)
where e1.event_time < e2.event_time
However, this seems not to be actually useful, when you have more than 2 rows for a given nodeid & code pair. To calculate difference for the previous ones only, use the lag() window function:
select nodeid, code, extract(epoch from event_time - lag) / 60 difference
from (select *, lag(event_time) over (partition by nodeid, code order by event_time)
from events) e
where lag is not null
Note: both of these will give you only one row for every nodeid & code pair, if you only have max 2 rows for all of the pairs.
try to use dense_rank to find the next event.
SELECT t_a.*, EXTRACT(EPOCH FROM (t_a.event_time - t_b.event_time))
FROM
(SELECT nodeid, code, event_time, dense_rank() over (partition by node_id order by event_time) as rnk
FROM table) t_a
JOIN
(SELECT nodeid, code, event_time, dense_rank() over (partition by node_id order by event_time) as rnk
FROM table) t_b
ON (t_a.nodeid=t_b.nodeid and t_a.rnk + 1 = t_b.rnk)