Postgres 12

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

CREATE TABLE SESSIONS(
            id serial NOT NULL PRIMARY KEY, 
            ctn INT NOT NULL, 
            day DATE NOT NULL,
            f_time TIME(0) NOT NULL,
            l_time TIME(0) NOT  NULL
        );     
    INSERT INTO SESSIONS(id, ctn, day, f_time, l_time)
    VALUES
    (1, 707, '2019-06-18', '10:48:25', '10:56:17'),
    (2, 707, '2019-06-18', '10:48:33', '10:56:17'),
    (3, 707, '2019-06-18', '10:53:17', '11:00:49'),
    (4, 707, '2019-06-18', '10:54:31', '10:57:37'),
    (5, 707, '2019-06-18', '11:03:59', '11:10:39'),
    (6, 707, '2019-06-18', '11:04:41', '11:08:02'),
    (7, 707, '2019-06-18', '11:11:04', '11:19:39');

CREATE TABLE



INSERT 0 7


select day + f_time from sessions


?column?


2019-06-18 10:48:25

2019-06-18 10:48:33

2019-06-18 10:53:17

2019-06-18 10:54:31

2019-06-18 11:03:59

2019-06-18 11:04:41

2019-06-18 11:11:04



SELECT 7


select
	ctn,
	min(f_ts) start_ts,
	max(l_ts) end_ts,
	max(l_ts) - min(f_ts) duration
from (
	select 
		s.*,
		count(*) filter(where f_ts > lag_l_ts) over(partition by ctn order by f_ts) grp
	from (
		select
			s.*,
			lag(l_ts) over(partition by ctn order by f_ts) lag_l_ts
		from (
			select
				s.*,
				(day + l_time)::timestamp l_ts,
				(day + f_time)::timestamp f_ts
			from sessions s
		) s
	) s
) s
group by ctn, grp
order by ctn, start_ts


ctn
start_ts
end_ts
duration


707
2019-06-18 10:48:25
2019-06-18 11:00:49
00:12:24

707
2019-06-18 11:03:59
2019-06-18 11:10:39
00:06:40

707
2019-06-18 11:11:04
2019-06-18 11:19:39
00:08:35



SELECT 3

ctn	start_ts	end_ts	duration
707	2019-06-18 10:48:25	2019-06-18 11:00:49	00:12:24
707	2019-06-18 11:03:59	2019-06-18 11:10:39	00:06:40
707	2019-06-18 11:11:04	2019-06-18 11:19:39	00:08:35