Get previous row value

Question

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!

Answer 1

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;