I'm having trouble calculating time between lines. I would like to calculate how much time an user spent at the station on a specific day. The first problem is the one line offset, result is shown in the line below. The second problem is how can I use the end of shift time for the last user row.

 CREATE TABLE adata (
    id serial PRIMARY KEY,
    user_id INT NOT NULL,
    station_id INT NOT NULL,
    shift_stop TIMESTAMP NOT NULL,
    shift_date DATE NOT NULL,
    created_at TIMESTAMP NOT NULL,
    shift_start TIMESTAMP NOT NULL
);

insert into adata (id,user_id,station_id,shift_stop,shift_date,created_at, shift_start) values 
(1, 1, 1, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 10:00:00'),
(2, 2, 1, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 10:01:00','2022-01-01 10:00:00'),
(3, 1, 2, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 11:00:00','2022-01-01 10:00:00'),
(4, 2, 2, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 12:00:00','2022-01-01 10:00:00'),
(5, 2, 3, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 12:30:00','2022-01-01 10:00:00');

select 
        t.user_id, 
        t.shift_stop,
        t.created_at, 
EXTRACT(EPOCH FROM (lag(t.created_at) over (partition by t.user_id order by t.created_at ) - t.created_at )) as time,        t.station_id, 
        t.id
    FROM adata t 
    where DATE(t.shift_date) = '2022-01-01' 

Exmaple: http://sqlfiddle.com/#!17/a8979/1

CodePudding user response：

You can use COALESCE to use shift_stop value and LEAD to get the time a user spent at a station.

SELECT
    *,
    EXTRACT(EPOCH FROM (COALESCE(LEAD(created_at) OVER (PARTITION BY user_id, shift_date ORDER BY created_at), shift_stop) - created_at)) AS time
FROM adata    
WHERE DATE(shift_date) = '2022-01-01'
ORDER BY user_id, id

Or you can provide a default value for LEAD function

SELECT
    *,
    EXTRACT(EPOCH FROM (LEAD(created_at, 1, shift_stop) OVER (PARTITION BY user_id, shift_date ORDER BY created_at) - created_at)) AS time
FROM adata    
WHERE DATE(shift_date) = '2022-01-01'
ORDER BY user_id, id

Both queries return the same results:

You can check a working demo here