Postgres difference between time stamp in minutes

Question

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

Answer 1

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

http://rextester.com/HGY2600

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.

Answer 2

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)