By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with data as
(
select '2018-03-29 13:36:52' "start",'2018-03-29 13:37:52+02' "end",'Alarm_821' d
union all
select '2018-03-29 13:39:52','2018-03-29 13:41:52+02','Alarm_821'
union all
select '2018-03-29 15:44:15','2018-03-29 15:50:16+02','Alarm_819'
union all
select '2018-03-29 15:44:15','2018-03-29 15:51:16+02','Alarm_817'
union all
select '2018-03-29 16:08:18','2018-03-29 16:10:19+02','Alarm_418'
union all
select '2018-03-29 16:08:18','2018-03-29 16:10:19+02','Alarm_465'
union all
select '2018-03-29 16:11:19','2018-03-29 16:15:19+02','Alarm_418'
)
select t.st, t.en, count(*)
from
(
select lag(tm) over (order by tm) st, tm en
from
(
select "start" tm from data
union
select "end" tm from data
) r
) t
join data on t.st < data."end" and t.en > data."start"
group by t.st, t.en
order by t.st
| st | en | count |
|---|---|---|
| 2018-03-29 13:36:52 | 2018-03-29 13:37:52+02 | 1 |
| 2018-03-29 13:39:52 | 2018-03-29 13:41:52+02 | 1 |
| 2018-03-29 15:44:15 | 2018-03-29 15:50:16+02 | 2 |
| 2018-03-29 15:50:16+02 | 2018-03-29 15:51:16+02 | 1 |
| 2018-03-29 16:08:18 | 2018-03-29 16:10:19+02 | 2 |
| 2018-03-29 16:11:19 | 2018-03-29 16:15:19+02 | 1 |