Get previous row value

user_16390428 0
Viewed 37

I'm trying to get a duration from my postgresql table and I have one idea on how to do it, is just to do extract from the time difference. But I can't get it right. Here is an example of table that I have.

ID Name Status_from Status_To Change_Time
1 Andrew Ready Break 2021-04-28 04:46:34
2 Andrew Break Meeting 2021-07-30 10:50:04
3 Andrew Meeting Checkout 2021-07-30 10:50:06
4 Nazar Checkout Ready 2021-07-30 10:54:09
5 Nazar Ready Meeting 2021-07-30 11:03:09
6 Andrew Checkout Ready 2021-07-30 11:10:09

And here is an example of the output that I want to get

ID Name Status_from Status_To Change_Time Duration
1 Andrew NULL Ready 2021-07-30 10:29:04 NULL (Or 0)
2 Andrew Ready Meeting 2021-07-30 10:50:04 1260
3 Andrew Meeting Checkout 2021-07-30 10:50:06 2
4 Nazar NULL Ready 2021-07-30 10:54:09 NULL (Or 0)
5 Nazar Ready Meeting 2021-07-30 11:03:09 540
6 Andrew Checkout Ready 2021-07-30 11:10:09 1203

The duration is the time that Status_to become a status_from for the same Name Any suggestions on how can I do it? Because I'm out of ideas, thank you in advance!

postgresql sql
1 Answers

You can use lag() and then "epoch" arithmetic to get the difference in seconds:

select t.*,
       (extract(epoch from changetime) -
        extract(epoch from lag(changetime) over (partition by name order by changetime)
       ) as duration_seconds
from t;
user_11440350
Related