From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Josep Sanmartí <josep(dot)sanmarti(at)openwired(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: select values from interval |
Date: | 2005-07-04 17:03:38 |
Message-ID: | 20050704170338.GB6117@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Mon, Jul 04, 2005 at 14:00:57 +0200,
Josep Sanmartí <josep(dot)sanmarti(at)openwired(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Din Adrian | 2005-07-04 21:14:29 | Help on Procedure running external function |
Previous Message | Bruno Wolff III | 2005-07-04 17:00:26 | Re: Help on Procedure running external function |