On Jul 27, 2010, at 6:07 PM, Patrick May wrote:
> On Jul 27, 2010, at 7:12 PM, Dann Corbit wrote:
>>> I have a table containing events with timestamps. I would like
>>> to generate a histogram of the number of each type of event for each
>>> half-hour period from 8:00 am to 6:00 pm. Are there any tools that
>>> will help me do this?
>>
>> GROUP BY is essentially a histogram generator.
>> The age() function will give you an interval.
>>
>> I am not sure if you want to combine half hour periods from different days
>> with the same time or not (IOW are 8AM Tuesday and 8AM Wednesday supposed to
>> be grouped together or not?)
>>
>> I guess that if you are more specific about exactly what you want you can
>> get a better answer. Your requirement is a little bit vague or ambiguous.
>
> I don't mind using Excel to generate the actual graphic. Ideally I'd
> get output something like this:
>
> date start end event count
> ---------- -------- -------- -------- -----
> 2010-07-27 08:00:00 08:29:59 EVENT_1 20
> 2010-07-27 08:00:00 08:29:59 EVENT_2 15
> 2010-07-27 08:30:00 08:59:59 EVENT_1 10
> 2010-07-27 08:30:00 08:59:59 EVENT_3 5
>
> I'm quite familiar with SQL, but I'm not sure how to generate the half hour
> intervals without hard coding them.
There's probably a better way, but something like this might work:
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;
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
