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/