Count events ocurred between time periods in PostgreSQL

user_14249596 0
Viewed 122

I'm trying to create a query using PostgreSQL 11.10 in order to retrieve the total events ocurred into multiple time perdiods.

For now, I just want to count these events, then I will obtain "who" and "what" in a detailed report.

I have the following table and data

create table events(
    id int primary key generated always as identity,
    who varchar,
    event_date date,
    start_time time,
    end_time time,
    event_type int
);

INSERT INTO events (who, event_date, start_time, end_time, event_type) values
('A', '2021-04-01', '06:00:00', '13:00:00', 1001),
('B', '2021-04-01', '08:00:00', '15:00:00', 1001),
('C', '2021-04-01', '06:00:00', '11:00:00', 1002),
('A', '2021-04-01', '13:30:00', '18:00:00', 1002);

I need to count events represented in the following timeline:

Events timeline

Min and max values can vary depending of the events ocurred along the day.

This is what I did, but I can't to avoid counting events when lead interval is 06:00:00.

select
    start_time,
    end_time,
    intervals as current_interval,
    coalesce(lead(intervals, 1) over (order by start_time, end_time, intervals ), '21:00:00') as next_interval,
    case 
        when
            intervals between start_time and end_time - '1 min'::interval
            or
            coalesce(lead(intervals, 1) over (order by start_time, end_time, intervals ), '21:00:00')
            between start_time and end_time - '1 min'::interval 
        then
            intervals || '-' || coalesce(lead(intervals, 1) over (order by start_time, end_time, intervals ), '21:00:00')
        else
            null
    end as is_present_in
from events e,
(
    select start_time as intervals from events
    where event_date = '2021-04-01'
    and who in ('A','B','C') 
    union 
    select end_time as intervals from events
    where event_date = '2021-04-01'
    and who in ('A','B','C') 
) tt
order by start_time, end_time, intervals;

I think there must be a better way to do this...

postgresql sql
2 Answers

Maybe this query solves your task

select (start_time || ' - ' || end_time) as "interval",
       (select count(1)
        from events
        where (intervals.start_time::interval + intervals.end_time::interval) / 2
               between events.start_time and events.end_time)
from (
         select start_time, lead(start_time, 1) OVER (order by start_time) end_time
         from (select start_time
               from events
               union
               select end_time
               from events) t
     ) intervals
where end_time notnull

Result

+-------------------+-----+
|interval           |count|
+-------------------+-----+
|06:00:00 - 08:00:00|2    |
|08:00:00 - 11:00:00|3    |
|11:00:00 - 13:00:00|2    |
|13:00:00 - 13:30:00|1    |
|13:30:00 - 15:00:00|2    |
|15:00:00 - 18:00:00|1    |
+-------------------+-----+
user_81531470

Use range types:

with
    i(r) as (
        select tsrange(dt, lead(dt) over (order by dt), '[)')
        from (
            select distinct dt
            from events cross join lateral (values(event_date + start_time), (event_date + end_time)) as t(dt)) as t)
select r, count(*)
from i join events as e on (tsrange(e.event_date + e.start_time, e.event_date + e.end_time, '[)') && i.r)
group by i.r
order by i.r;
┌───────────────────────────────────────────────┬───────┐
│                       r                       │ count │
├───────────────────────────────────────────────┼───────┤
│ ["2021-04-01 06:00:00","2021-04-01 08:00:00") │     2 │
│ ["2021-04-01 08:00:00","2021-04-01 11:00:00") │     3 │
│ ["2021-04-01 11:00:00","2021-04-01 13:00:00") │     2 │
│ ["2021-04-01 13:00:00","2021-04-01 13:30:00") │     1 │
│ ["2021-04-01 13:30:00","2021-04-01 15:00:00") │     2 │
│ ["2021-04-01 15:00:00","2021-04-01 18:00:00") │     1 │
└───────────────────────────────────────────────┴───────┘
user_5931440
Related