Re: Sum of events over an interval; how?

--- S Dawalt <shane.dawalt@wright.edu> wrote:
>   I have a number of records having a timestamp when
> something happened. I am trying to find the number
> of events (records) that happened per minute over an
> hour timespan. I just cannot figure out how to do
> this.  Maybe it's not possible?  Would a plpgsql
> function be a good idea so that the number of
> front-end/back-end requests is small?  I'm looking
> for some output (or maybe temp table) of:
>
> Time                Count
> 2/17/2002 22:00:00    4
> 2/17/2002 22:01:00    7
> 2/17/2002 22:02:00   14
> ....
>
>   Any help, even "Can't be done" is appreciated.
>
> Shane A. Dawalt

The following solution will give you a count for the
events where the count during the minute is greater
than zero:

The name of our timestamp field is event_dt.
The name of the event flag is flag.
The name of the table is events.

select date_trunc('minute', event_dt) as minutes,
count(flag) as flags from events
where date_trunc('hour', event_dt)='2002-01-08
23:00:00'
group by minutes;

The WHERE clause is certainly optional.  You may need
it if you want to limit your results to a specific
time period.  In this example, only events occuring
between 11:00 pm and midnight on January 8, 2002 would
be reported.

Best of luck,

Andrew Gould


__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com