I have the following table in PostgreSQL storing events with start and end time:
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 assume I want to group these entries by 15min period from given start and end time. So for example for time from 2017-05-19 12:00 to 2017-05-19 14:00 I would like to receive 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 it in the simplest way in PostrgreSQL?