Date: Mon, 4 Jul 2005 12:03:38 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Josep =?iso-8859-1?Q?Sanmart=ED?= <josep.sanmarti@openwired.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: select values from interval
Message-ID: <20050704170338.GB6117@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Josep =?iso-8859-1?Q?Sanmart=ED?= <josep.sanmarti@openwired.net>,
	pgsql-sql@postgresql.org
References: <1120478457.3506.17.camel@josep>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1120478457.3506.17.camel@josep>
User-Agent: Mutt/1.5.6i

On Mon, Jul 04, 2005 at 14:00:57 +0200,
  Josep Sanmartí <josep.sanmarti@openwired.net> wrote:
> Hi,
> 
> I've this table: am_conn(mac, user_ip, start_time, end_time). Each time
> a user connects with a remote server a new row is added, when the user
> is disconnected the end_time field is set.
> 
> Now, I want to know if there is a way (sql statement) to know how many
> connections are up during an interval time, for example 60 segs, and to
> repeat this interval from a beginning date to end date. I think it
> should be something like:
> 	SELECT COUNT(*)
> 	FROM am_conn
> 	WHERE start_time between '2004-01-01 10:28:00.000'
> 		AND end time '2005-01-01 10:28:00.000'
> 	GROUP BY .....something.....
> 
> I made some tests but I can't obtain the correct statement. Maybe,
> probably, it should be done without group by.

I think you are taking the wrong approach. I think you want to look at
joining your table to a list of intervals generated by generate_series
(or your own function in versions before 8.0). You aren't going to be able to
just use group by because a record can be in more than one interval.
There are different ways to implement the specifics of this, but this should
give you some things to test out.