pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect values from interval
2+ messages / 2 participants
[nested] [flat]
* select values from interval
@ 2005-07-04 12:00 Josep Sanmartí <josep.sanmarti@openwired.net>
2005-07-04 17:03 ` Re: select values from interval Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 2+ messages in thread
From: Josep Sanmartí @ 2005-07-04 12:00 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: select values from interval
2005-07-04 12:00 select values from interval Josep Sanmartí <josep.sanmarti@openwired.net>
@ 2005-07-04 17:03 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2005-07-04 17:03 UTC (permalink / raw)
To: Josep Sanmartí <josep.sanmarti@openwired.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-07-04 17:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-04 12:00 select values from interval Josep Sanmartí <josep.sanmarti@openwired.net>
2005-07-04 17:03 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox