From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Histogram generator |
Date: | 2010-07-28 14:04:57 |
Message-ID: | 20100728140457.GH7584@samason.me.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2010-07-28 14:53:08 | Re: Which CMS/Ecommerce/Shopping cart ? |
Previous Message | Gary Fu | 2010-07-28 13:26:05 | Re: psql problem |