Postgres: Detect active and inactive periods

Question

I have a table containing events. Each event has a defined starting and ending timestamp:

start_ts	end_ts
2023-07-27 01:02:00	2023-07-27 01:05:00
2023-07-27 01:05:00	2023-07-27 01:07:00
2023-07-27 01:07:00	2023-07-27 01:11:00
2023-07-27 01:11:00	2023-07-27 01:15:00
2023-07-27 01:30:00	2023-07-27 01:35:00
2023-07-27 01:35:00	2023-07-27 01:42:00
2023-07-27 01:45:00	2023-07-27 01:50:00

From this structure, I want to find out, active and inactive periods. Active periods are defined by the time range in which an event takes place. Such an active period can be composed of multiple consecutive events. In inactive periods no event is taking place. So the expected output would be like:

period_start	period_end	status
2023-07-27 01:02:00	2023-07-27 01:15:00	active
2023-07-27 01:15:00	2023-07-27 01:30:00	inactive
2023-07-27 01:30:00	2023-07-27 01:42:00	active
2023-07-27 01:42:00	2023-07-27 01:45:00	inactive
2023-07-27 01:45:00	2023-07-27 01:50:00	active

Any idea how I can achieve this by using PostgreSQL?

CREATE TABLE IF NOT EXISTS events(
   id INT PRIMARY KEY,   
   start_ts timestamp NOT NULL,
   end_ts timestamp NOT NULL
);

INSERT INTO events(id,start_ts,end_ts)
VALUES
(1,'2023-07-27 01:02:00','2023-07-27 01:05:00'),
(2,'2023-07-27 01:05:00','2023-07-27 01:07:00'),
(3,'2023-07-27 01:07:00','2023-07-27 01:11:00'),
(4,'2023-07-27 01:11:00','2023-07-27 01:15:00'),
(5,'2023-07-27 01:30:00','2023-07-27 01:35:00'),
(6,'2023-07-27 01:35:00','2023-07-27 01:42:00'),
(7,'2023-07-27 01:45:00','2023-07-27 01:50:00');

Can those events overlap? Your example data does not show overlaps but are they guaranteed not to happen (i.e. using an exclusion constraint)? — SebDieBln, Jul 27 '23 at 13:39
Oh, and you might want to provide actual `CREATE TABLE ...` and `INSERT INTO ...` statements so people can recreate your example data. — SebDieBln, Jul 27 '23 at 13:40
@SebDieBln I have added both Create table and insert table statement — user10679526, Jul 27 '23 at 13:50
Does this answer your question? [How to find end point of same value with interval?](https://stackoverflow.com/questions/53854230/how-to-find-end-point-of-same-value-with-interval) — Bergi, Jul 27 '23 at 14:01
Search for "gaps and islands". But if your events are non-overlapping and non-consecutive, this is really trivial: `SELECT 'active' AS status, start_ts AS period_start, end_ts AS period_end FROM events UNION ALL 'inactive' AS status, end_ts AS period_start, lead(start_ts) OVER (ORDER BY start_ts) AS period_end FROM events`. — Bergi, Jul 27 '23 at 14:05
There many rows, where start_ts equal to end_ts for previous row. They can be collapsed to 1 row. — ValNik, Jul 27 '23 at 15:07

score 1 · Accepted Answer · answered Jul 27 '23 at 15:16

Another task about gaps and islands. Additionally, with adding gaps to output. Only for case The events cannot overlap each other See this example.

with t1 as(-- gaps
  select *
  ,case when lag(end_ts,1,start_ts)over(order by start_ts)=start_ts then 0 else 1 end gap
from events
)
,t2 as(--count islands
  select *
    ,sum(gap)over(order by start_ts)gr
  from t1
)
,t3 as(-- compact islands and take time for gap
  select min(start_ts)start_ts,max(end_ts)end_ts,gr
    ,lead(min(start_ts))over(order by gr) next_active
  from t2
  group by gr
)
select start_ts,end_ts,gr,'active' as status
from t3
union all
select end_ts start_ts,next_active end_ts,gr,'inactive' as status
from t3
where next_active is not null
order by gr,start_ts

Result

start_ts	end_ts	gr	status
2023-07-27 01:02:00	2023-07-27 01:15:00	0	active
2023-07-27 01:15:00	2023-07-27 01:30:00	0	inactive
2023-07-27 01:30:00	2023-07-27 01:42:00	1	active
2023-07-27 01:42:00	2023-07-27 01:45:00	1	inactive
2023-07-27 01:45:00	2023-07-27 01:50:00	2	active

Postgres: Detect active and inactive periods

1 Answers1

Linked