Postgres 12

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

CREATE TABLE activities(
    id int,
    name text,
    start timestamp,
    "end" timestamp
);

INSERT INTO activities VALUES
(1, 'A', '2018-01-09 17:00:00', '2018-01-09 20:00:00'),
(2, 'A', '2018-01-09 18:00:00', '2018-01-09 20:30:00'),
(3, 'B', '2018-01-09 19:00:00', '2018-01-09 21:30:00'),
(4, 'B', '2018-01-09 22:00:00', '2018-01-09 23:00:00');

SELECT * FROM activities;

CREATE TABLE



INSERT 0 4




id
name
start
end


1
A
2018-01-09 17:00:00
2018-01-09 20:00:00

2
A
2018-01-09 18:00:00
2018-01-09 20:30:00

3
B
2018-01-09 19:00:00
2018-01-09 21:30:00

4
B
2018-01-09 22:00:00
2018-01-09 23:00:00



SELECT 4


select a.name, min(start) as startt, max("end") as endt
from (select a.*,
             count(*) filter (where prev_end is null or prev_end < start) over (partition by name order by start, id) as grp
      from (select a.*,
                   max("end") over (partition by name
                                    order by start, id
                                    rows between unbounded preceding and 1 preceding
                                   ) as prev_end
            from activities a
           ) a
     ) a
group by name, grp


name
startt
endt


A
2018-01-09 17:00:00
2018-01-09 20:30:00

B
2018-01-09 19:00:00
2018-01-09 21:30:00

B
2018-01-09 22:00:00
2018-01-09 23:00:00



SELECT 3


with islands as (
      select a.name, min(start) as startt, max("end") as endt
      from (select a.*,
                   count(*) filter (where prev_end is null or prev_end < start) over (partition by name order by start, id) as grp
            from (select a.*,
                         max("end") over (partition by name
                                          order by start, id
                                          rows between unbounded preceding and 1 preceding
                                         ) as prev_end
                  from activities a
                 ) a
           ) a
      group by name, grp
     )
select name, sum(endt - startt)
from islands i
group by name


name
sum


B
03:30:00

A
03:30:00



SELECT 2

id	name	start	end
1	A	2018-01-09 17:00:00	2018-01-09 20:00:00
2	A	2018-01-09 18:00:00	2018-01-09 20:30:00
3	B	2018-01-09 19:00:00	2018-01-09 21:30:00
4	B	2018-01-09 22:00:00	2018-01-09 23:00:00