On Jul 27, 2010, at 9:21 PM, Steve Atkins wrote:
> 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;
Thanks! It looks like interval is what I need to play with.
Regards,
Patrick
smime.p7s
Description: S/MIME cryptographic signature
