Postgres Pro
Downloads
Home   >   mailing lists

Thread: A query or maybe programmatic?

A query or maybe programmatic?

From
S Dawalt
Date:
  I have a large pile of records in a table having, in part, the following structure:
 
starttime timestamp
endtime  timestamp
portnum  integer
serverid   integer
<lots more garbage>
 
  What I want is to obtain the total number of records over a period of time at each second.  So, for example, if I have three records as shown here:
 
starttime                endtime
3/10/2002 1:0:5      3/10/2002 1:0:10
3/10/2002 1:0:7      3/10/2002 1:0:11
3/10/2002 1:0:13    3/10/2002 1:0:14
 
then I wish to enter a query that will count the number of records that intersects each second over the period 3/10/2002 1:0:0 thru 3/10/2002 1:1:0.  When my query gets done, what I want back is:
 
Time                    Count
3/10/2002 1:0:5    1
3/10/2002 1:0:6    1
3/10/2002 1:0:7    2
3/10/2002 1:0:8    2
3/10/2002 1:0:9    2
3/10/2002 1:0:10   2
3/10/2002 1:0:11   1
3/10/2002 1:0:13   1
3/10/2002 1:0:14   1
 
Whether it returns 0 or not for those times that no records fit the selection makes no difference to me. Ultimately, this is to be used to determine the used capacity of a modem bank for which I receive log data where the log data is stored in the database as records of complete modem sessions (start and end time per record). I thought I could just query the database on a second-by-second basis to find the number of modems in-use. Doing this programmatically is very slow (an SQL call for each second). But I'm having a devil of a time wrapping my brain around the proper query to do this in one or a few SQL statements. Maybe it cannot be done?  Maybe I'm asking for trouble?  Ultimately I need to do this over hours rather than just a minute. Anyone have ideas?
 
Shane A. Dawalt
********************************************
Network Engineer
Wright State University
Dayton,  OH   45435    USA
Phone:  937-775-4089
Email:  shane.dawalt@wright.edu

Re: A query or maybe programmatic?

From
Martijn van Oosterhout
Date:
On Mon, Mar 11, 2002 at 11:15:29AM -0500, S Dawalt wrote:
>   I have a large pile of records in a table having, in part, the following structure:
>
> starttime timestamp
> endtime  timestamp
> portnum  integer
> serverid   integer
> <lots more garbage>
>
>   What I want is to obtain the total number of records over a period of time at each second.  So, for example, if I
havethree records as shown here: 
>
> starttime                endtime
> 3/10/2002 1:0:5      3/10/2002 1:0:10
> 3/10/2002 1:0:7      3/10/2002 1:0:11
> 3/10/2002 1:0:13    3/10/2002 1:0:14

Ok, one thing to remember about SQL is that it cannot generate new values
out of thin air, they have to come from somewhere. What you need to do is
create a table with all the timestamp values you're interested in and then:

select time, count(*)
from data
where time between data.starttime and data.endtime;

Done. HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> If the company that invents a cure for AIDS is expected to make their
> money back in 17 years, why can't we ask the same of the company that
> markets big-titted lip-syncing chicks and goddamn cartoon mice?