Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


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
);

insert into adata (id,user_id,station_id,shift_stop,shift_date,created_at) 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'),
(3, 1, 2, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 11:00:00'),
(4, 2, 2, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 12:00:00'),
(5, 2, 3, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 12:30:00');
5 rows affected

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


id
user_id
station_id
shift_stop
shift_date
created_at
time


1
1
1
2022-01-01 15:00:00
2022-01-01
2022-01-01 10:00:00
3600.000000

3
1
2
2022-01-01 15:00:00
2022-01-01
2022-01-01 11:00:00
14400.000000

2
2
1
2022-01-01 15:00:00
2022-01-01
2022-01-01 10:01:00
7140.000000

4
2
2
2022-01-01 15:00:00
2022-01-01
2022-01-01 12:00:00
1800.000000

5
2
3
2022-01-01 15:00:00
2022-01-01
2022-01-01 12:30:00
9000.000000


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


id
user_id
station_id
shift_stop
shift_date
created_at
time


1
1
1
2022-01-01 15:00:00
2022-01-01
2022-01-01 10:00:00
3600.000000

3
1
2
2022-01-01 15:00:00
2022-01-01
2022-01-01 11:00:00
14400.000000

2
2
1
2022-01-01 15:00:00
2022-01-01
2022-01-01 10:01:00
7140.000000

4
2
2
2022-01-01 15:00:00
2022-01-01
2022-01-01 12:00:00
1800.000000

5
2
3
2022-01-01 15:00:00
2022-01-01
2022-01-01 12:30:00
9000.000000

id	user_id	station_id	shift_stop	shift_date	created_at	time
1	1	1	2022-01-01 15:00:00	2022-01-01	2022-01-01 10:00:00	3600.000000
3	1	2	2022-01-01 15:00:00	2022-01-01	2022-01-01 11:00:00	14400.000000
2	2	1	2022-01-01 15:00:00	2022-01-01	2022-01-01 10:01:00	7140.000000
4	2	2	2022-01-01 15:00:00	2022-01-01	2022-01-01 12:00:00	1800.000000
5	2	3	2022-01-01 15:00:00	2022-01-01	2022-01-01 12:30:00	9000.000000