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