Best Practice for window functions in PostgreSQL

Question

I have a table with record_id and record_created_time columns (postgresql). I want to calculate duration by minute between timeline of each records. Can I do it with window functions (using partition by)?

 record_id  |     record_time        | value(minute)
------------|------------------------|--------
 1          | 2019-10-01 01:00:00+02 |  0
 1          | 2019-10-01 01:03:00+02 |  3
 2          | 2019-10-01 02:00:00+02 |  0
 2          | 2019-10-01 02:05:00+02 |  5
 2          | 2019-10-01 02:07:00+02 |  2

Answer 1

Yes, this can be done with the window function lag():

select record_id, record_time, 
       record_time - lag(record_time) over (partition by record_id order by record_time) as diff_to_previous
from the_table
order by record_id, record_time;

Note that this will return an interval, if you want the value in minutes, you need to convert that value:

extract(epoch from record_time - lag(record_time) over (partition by record_id order by record_time)) / 60 as minutes