Number of records grouped by start and end time
I have the following table in PostgreSQL where events with start and end times are stored:
CREATE TABLE foo
AS
SELECT id, name, startTime::timestamp, endTime::timestamp
FROM ( VALUES
( 1, 'A', '2017-05-19T12:21:18+00:00', '2017-05-19T15:31:18+00:00' ),
( 2, 'B', '2017-05-19T12:35:18+00:00', '2017-05-19T12:48:18+00:00' ),
( 3, 'C', '2017-05-19T13:00:18+00:00', '2017-05-19T13:31:18+00:00' ),
( 4, 'D', '2017-05-19T13:11:18+00:00', '2017-05-19T13:27:18+00:00' ),
( 5, 'E', '2017-05-19T13:45:18+00:00', '2017-05-19T14:55:18+00:00' )
) AS (id, name, startTime, endTime);
Let's say I want to group these records 15 minutes from start and end. So, for example, for the time from 2017-05-19 12:00 to 2017-05-19 14:00 I would like to get something like this:
date | count
---------------------------------
2017-05-19T12:00:00+00:00 | 0 (A expected)
2017-05-19T12:15:00+00:00 | 1 (A, B expected)
2017-05-19T12:30:00+00:00 | 2 (A, B expected)
2017-05-19T12:45:00+00:00 | 2 (A, C, D expected)
2017-05-19T13:00:00+00:00 | 3 (A, C, D expected)
2017-05-19T13:15:00+00:00 | 3 (A, C expected)
2017-05-19T13:30:00+00:00 | 2 (A, E expected)
2017-05-19T13:45:00+00:00 | 2 (A, E expected)
How can I achieve this in the simplest way possible in PostrgreSQL?
+3
source to share
3 answers
with my_table(id, name, startTime, endTime) as (
values
(1, 'A', '2017-05-19T12:21:18+00:00'::timestamp, '2017-05-19T15:31:18+00:00'::timestamp),
(2, 'B', '2017-05-19T12:35:18+00:00', '2017-05-19T12:48:18+00:00'),
(3, 'C', '2017-05-19T13:00:18+00:00', '2017-05-19T13:31:18+00:00'),
(4, 'D', '2017-05-19T13:11:18+00:00', '2017-05-19T13:27:18+00:00'),
(5, 'E', '2017-05-19T13:45:18+00:00', '2017-05-19T14:55:18+00:00')
)
select date, count(id), string_agg(name, ', ') as names
from generate_series('2017-05-19 12:00:00'::timestamp, '2017-05-19 14:00:00', '15m'::interval) as date
left join my_table t on tstzrange(date, date+ '15m') && tstzrange(t.starttime, t.endtime)
group by 1
order by 1;
date | count | names
---------------------+-------+---------
2017-05-19 12:00:00 | 0 |
2017-05-19 12:15:00 | 1 | A
2017-05-19 12:30:00 | 2 | A, B
2017-05-19 12:45:00 | 2 | A, B
2017-05-19 13:00:00 | 3 | A, C, D
2017-05-19 13:15:00 | 3 | A, C, D
2017-05-19 13:30:00 | 2 | A, C
2017-05-19 13:45:00 | 2 | A, E
2017-05-19 14:00:00 | 2 | A, E
(9 rows)
+1
source to share
with your_table(id, startTime, endTime) as (
select 1 ,'2017-05-19T12:21:18+00:00'::timestamp,'2017-05-19T15:31:18+00:00'::timestamp union all
select 2 ,'2017-05-19T12:35:18+00:00','2017-05-19T12:48:18+00:00' union all
select 3 ,'2017-05-19T13:00:18+00:00','2017-05-19T13:31:18+00:00' union all
select 4 ,'2017-05-19T13:11:18+00:00','2017-05-19T13:27:18+00:00' union all
select 5 ,'2017-05-19T13:45:18+00:00','2017-05-19T14:55:18+00:00'
)
select inter, sum(case when (inter, inter + interval '15 minute') OVERLAPS (startTime, endTime) then 1 else 0 end) from (
select
generate_series('2017-05-19 12:00:00'::timestamp, '2017-05-19 14:00:00'::timestamp, interval '15 minute') as inter
) t1
cross join your_table
group by inter
order by inter
+1
source to share
I'm not really sure what you want, but it looks like me.
SELECT
to_timestamp(timeseg*60*15) AT TIME ZONE 'localtime' AS tsround,
count(*),
array_agg(name)
FROM foo
CROSS JOIN LATERAL generate_series(
EXTRACT(EPOCH FROM starttime AT TIME ZONE 'localtime')::int / 60 / 15,
EXTRACT(EPOCH FROM endtime AT TIME ZONE 'localtime')::int / 60 / 15
) AS t(timeseg)
GROUP BY timeseg
ORDER BY tsround;
tsround | count | array_agg
---------------------+-------+-----------
2017-05-19 12:15:00 | 1 | {A}
2017-05-19 12:30:00 | 2 | {A,B}
2017-05-19 12:45:00 | 2 | {A,B}
2017-05-19 13:00:00 | 3 | {A,C,D}
2017-05-19 13:15:00 | 3 | {A,C,D}
2017-05-19 13:30:00 | 2 | {A,C}
2017-05-19 13:45:00 | 2 | {A,E}
2017-05-19 14:00:00 | 2 | {A,E}
2017-05-19 14:15:00 | 2 | {A,E}
2017-05-19 14:30:00 | 2 | {A,E}
2017-05-19 14:45:00 | 2 | {A,E}
2017-05-19 15:00:00 | 1 | {A}
2017-05-19 15:15:00 | 1 | {A}
2017-05-19 15:30:00 | 1 | {A}
(14 rows)
+1
source to share