Re: Histogram generator

From: Patrick May <patrick(dot)may(at)mac(dot)com>
To: Steve Atkins <steve(at)blighty(dot)com>
Cc: PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Histogram generator
Date: 2010-07-28 01:25:05
Message-ID: 49FD1D28-EA80-417F-ACB1-8C76F01BCA81@mac.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Howard Rogers 2010-07-28 01:58:40 Comparison of Oracle and PostgreSQL full text search
Previous Message Tom Lane 2010-07-28 01:23:01 Re: postgres-8.4SS, pg_dump from macosx-10.6 has "ssl handshake error" 26% in