Re: Histogram generator

On Tue, Jul 27, 2010 at 09:25:05PM -0400, Patrick May wrote:
> On Jul 27, 2010, at 9:21 PM, Steve Atkins wrote:
> > select date_trunc('hour', foo) + interval '30 minutes' * floor(extract(minute from foo) / 30) as start, event, count(*) from bar group by 1, 2 order by 1 asc;
>
> Thanks! It looks like interval is what I need to play with.

Another useful tool to use is the classic unix "seconds since epoch".
You could turn the key expression from above into:

timestamp 'epoch' + interval '1 second' * floor(date_part('epoch',foo) / (30*60)) * (30*60)

I'd probably go with Steve's version here, it's a bit more obvious
what's going on. Also note, that if you don't really care about what
the specific groups are, just that you have a set of evenly divided
30minute periods you don't need to convert back to a date, so could just
use:

floor(date_part('epoch',foo) / (30*60))

One final note, if you're dealing with lots of data and the above
expression is slow, you could delay converting back to a date until
"after" the grouping, i.e:

SELECT timestamp 'epoch' + interval '1 second' * floor(date_part('epoch',foo) / (30*60)) * (30*60) AS t, COUNT(*)
FROM data
GROUP BY floor(date_part('epoch',foo) / (30*60));

This will save PG from converting back to a date for every row when it's
going to chuck most of them away anyway.

Hope that gives you some more ideas!

--
Sam http://samason.me.uk/