A query or maybe programmatic? - Mailing list pgsql-general

From S Dawalt
Subject A query or maybe programmatic?
Date
Msg-id 002201c1c917$f5f85d10$23b16c82@HP0E2E6GKYFJS4
Whole thread Raw
Responses Re: A query or maybe programmatic?
List pgsql-general
  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

pgsql-general by date:

Previous
From: Frank_Lupo Frank_Lupo
Date:
Subject: createlang vbs
Next
From: Ricardo Vaz Mannrich
Date:
Subject: Re: Program lose the connection to backend