Re: A query or maybe programmatic?

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 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

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(at)svana(dot)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?