A couple weeks ago I hacked up some sql for this and had planned to blog about it but never got around to it. Of course then I needed to use the technique again yesterday, so it’s time to make a more permanent record for next time.
The problem we’re solving is the need to do group by on time intervals of less than 1 hour. This isn’t exactly hard, but it’s a little tricky, since you can’t easily pull out random fractions of an hour. The way I solved it was working my way through a series of sql statements.
pagila=# select date_trunc('M',current_timestamp),
pagila-# extract('minute' from current_timestamp);
date_trunc | date_part
------------------------+-----------
2007-08-28 22:47:00-04 | 47
(1 row)
This first sql statement has two parts; first I grab the current time and use the date_trunc function to cut it to the current minute. This isn’t needed for the final query, it is more for reference while I worked things out. The second part of the query I used the extract function to get just the minute portion of the time, in this case 47 minutes.
pagila=# select date_trunc('M',current_timestamp),
pagila-# extract('minute' from current_timestamp)/15;
date_trunc | ?column?
------------------------+-----------------
2007-08-28 22:47:00-04 | 3.13333333333333
(1 row)
Next, I divided the minutes by 15, to give me the quarter hour. Of course the decimal is a bit unwieldy, so…
pagila=# select date_trunc('M',current_timestamp),
pagila-# round(extract('minute' from current_timestamp)/15);
date_trunc | round
------------------------+-------
2007-08-28 22:47:00-04 | 3
(1 row)
I used the round function to get something a little nicer to work with. Now, this tells us our timestamp is in the third quarter hour, but if we’re going to have something to group by on, we want something a little more real world.
pagila=# select date_trunc('M',current_timestamp),
pagila-# round(extract('minute' from current_timestamp)/15)*15;
date_trunc | ?column?
------------------------+----------
2007-08-28 22:47:00-04 | 45
(1 row)
So now we have our time, rounded to the quarter hour of 45 minutes, and the only thing left to do is turn it back into a normal timestamp.
pagila=# select date_trunc('H',current_timestamp) + (round(extract('minute' from current_timestamp)/15)*15) \* '1 minute'::interval;
?column?
------------------------
2007-08-28 22:45:00-04
(1 row)
Note here I used date_trunc to get my time to set to the base time of the hour, and then I add back in the ‘45 minutes’ that are generated through our series of rounding and extracting. Once you have this, using it in an aggregate is only a short step away.
pagila=# select date_trunc('H',rental_date) + (round(extract('minute' from rental_date)/15)*15) \* '1 minute'::interval as time,
pagila-# count(*) from rental group by time order by time desc limit 10;
time | count
---------------------+-------
2005-08-23 22:45:00 | 3
2005-08-23 22:30:00 | 3
2005-08-23 22:15:00 | 9
2005-08-23 22:00:00 | 6
2005-08-23 21:45:00 | 7
2005-08-23 21:30:00 | 7
2005-08-23 21:15:00 | 5
2005-08-23 21:00:00 | 6
2005-08-23 20:45:00 | 7
2005-08-23 20:30:00 | 9
(10 rows)
Note you can adjust the interval (eg. make it every 5 minutes) by changing the number you divde and multiply by, so this technique should work for any sub-hour intervals, and would not be hard to adapt to other non-standard amounts (like every 4 hours). Also remember this was something I whipped out rather quickly, I’ve done no analysis on how well it scales, if you’ve worked out a better/cleaner query by all means, please post to the comments. HTH.