On Mon, Jul 04, 2005 at 14:00:57 +0200, Josep SanmartĂ <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])