Search code examples
sqlpostgresqltimestampdatediff

SQL time between communication

hi Guys hope you are all Safe,

I have a table in SQL with time-stamps and I would like to calculate the time between 2 communications after i order them DEC

I have looked everywhere but unfortunately without any success :( The desirable outcome should be same as the yellow columns

Thanks in advance!

enter image description here

Solution

  • You can get the difference using lag():

    select t.*,
       (timestamp - lag(timestamp, 1, timestamp) over (order by timestamp)) diff_interval
from t;

    I don't know if converting the interval to minutes is an important part of your question.

    If second-precision is sufficient, you can use:

    select t.*,
       (timestamp - lag(timestamp, 1, timestamp) over (order by timestamp)),
       extract(epoch from (timestamp - lag(timestamp, 1, timestamp) over (order by timestamp))) / 60.0 as minutes
from t;