CODEWITHSUNDEEP
sqlpostgresqlcountgroup-by
xyz124
xyz124

Reputation: 69

Count entries grouped by start and end time

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?

Upvotes: 3

Views: 491

Answers (3)

Oto Shavadze
Oto Shavadze

Reputation: 42753

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

Upvotes: 1

Evan Carroll
Evan Carroll

Reputation: 1

I'm not exactly sure what you're after but this looks like it to 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)

Upvotes: 1

klin
klin

Reputation: 121594

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)

Upvotes: 1

Related Questions