Calculate time duration based on timestamp from previous row and new inserted row and put result into previous row

Question

I created table in postgres with real time machine logs. Every time when state of machine is changed new data are sending to database as below:

idx; timestamp; state
-----------------------------------------------
1;  2021-10-15 13:39:11.843; state0
2;  2021-10-15 13:40:12.857; state1
3;  2021-10-15 13:41:13.856; state2
4;  2021-10-15 13:43:17.657; state6
5;  2021-10-15 13:45:18.257; state0
6;  2021-10-14 12:32:12.857; state1
7;  2021-10-14 12:40:17.247; state6
8;  2021-10-15 09:18:19.156; state0

I would like to add new column with state duration. The duration will be calculated as a difference between timestamp from new row and timestamp from previous row.

idx; timestamp; state; duration
-----------------------------------------------
1;  2021-10-15 13:39:11.843; state0; (timestamp row 2 - timestamp row 1)
2;  2021-10-15 13:40:12.857; state1; (timestamp row 3 - timestamp row 2)
3;  2021-10-15 13:41:13.856; state2; (timestamp row 4 - timestamp row 3)
4;  2021-10-15 13:43:17.657; state6; (timestamp row 5 - timestamp row 4)

I have very little experience with databases but my idea is follow:

Create function in postgres that makes above calculation (using lag()) and use triggers to place duration data every time when new data arrive.

Could anyone advise me if above make sense and if so help me with create that function?

Answer 1

select 
    t1."timestamp", 
    t1.state, 
    (t2."timestamp" - t1."timestamp")::interval hour to minute as "duration"
from 
    (
        select "timestamp", state,  
        (ROW_NUMBER () OVER (ORDER BY idx)) as rnum 
        from test.tbl_status
    ) t1 
inner join 
    (
        select "timestamp",
        (ROW_NUMBER () OVER (ORDER BY idx)) as rnum 
        from test.tbl_status
    ) t2 on t1.rnum + 1 = t2.rnum

Result of this query:

timestamp	state	duration
2021-10-15 13:39:11.843	state0	00:01:00
2021-10-15 13:40:12.857	state1	00:01:00
2021-10-15 13:41:13.856	state2	00:02:00
2021-10-15 13:43:17.657	state6	00:02:00
2021-10-15 13:45:18.257	state0	-1 days -01:13:00
2021-10-14 12:32:12.857	state1	00:08:00
2021-10-14 12:40:17.247	state6	20:38:00