Sum of events over an interval; how?
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
********************************************
Network Engineer
Wright State University
Dayton, OH 45435 USA
Phone: 937-775-4089
Email: shane.dawalt@wright.edu
--- 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
It might help to have the structure of the table you're querying, like the
CREATE TABLE statement. Also what version of Postgres are you using?
Wes
S Dawalt <shane.dawalt@wright.edu>@postgresql.org on 02/18/2002 02:32:08 PM
Sent by: pgsql-general-owner@postgresql.org
To: pgsql-general@postgresql.org
cc:
Subject: [GENERAL] Sum of events over an interval; how?
? 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
********************************************
Network Engineer
Wright State University
Dayton,? OH?? 45435??? USA
Phone:? 937-775-4089
Email:? shane.dawalt@wright.edu
Import Notes
Resolved by subject fallback
