How to aggregate specific rows in PostgreSQL?

Question

I am new in PostgreSQL and I wanted to know how we can aggregate 15 min intervals in a table.

for example:

Table:
time: count
9:01    1
9:05    2
9:13    3 
9:20    4
9:21    3
9:35    3

Output:
time          count
9:00 - 9:15    6
9:15 - 9:30    7
9:30 - 9:45    3

See http://stackoverflow.com/questions/11391085/getting-date-list-in-a-range-in-postgresql. — Politank-Z, May 12 '15 at 18:59

score 0 · Answer 1 · answered May 12 '15 at 19:56

I'm assuming that your time column is an actual date-time field, not just a text field.

To calculate which quarter an event occurred in, simply truncate the time field to the nearest hour using date_trunc. Then, calculate which quarter it fell into by dividing the minute portion by 15 and flooring it. After that, you can calculate the start/end times by using an interval.

EX: May, 12 2015 19:46:41.
Truncating this to the hour, you get May, 12 2015 19:00:00.
Calculating the quarter is floor(46/15) = 3
Start time= May, 12 2015 19:00:00 + interval '15 minutes' * 3 (which is 19:45)
End time= May, 12 2015 19:00:00 + interval '15 minutes -1 second' * 4 (which is 19:59)

Now, you have start times and end times and action counts. All you have to do is group them and sum it all together.

Using the following table structure,

create table events (
    time timestamp,
    actions integer
);

Running the following query will get you the data you're looking for.

SELECT start_time, end_time, sum(actions), count(*)
FROM (
  SELECT 
    date_trunc('hour',time) + (interval '15 minutes'*floor(date_part('minute',time)/15)) as start_time, 
    date_trunc('hour',time) + (interval '15 minutes -1 second'*(floor(date_part('minute',time)/15)+1)) as end_time,
    actions
   from events
) as interval_actions
group by start_time, end_time;

I mocked this up in sql fiddle for you.
Datetime functions for postgres are listed in the documentation.

How to aggregate specific rows in PostgreSQL?

1 Answers1