Find maximum number of logged on users in SQL
- by lleto
Hi,
I want to keep tabs on the number of concurrent users of my application. I therefore log a time_start and a time_stop. If I now want to query the database for the maximum number of logged on users and return the start date, how would I do that.
The table looks like this:
id | time_start | time_stop
----+---------------------+---------------------
1 | 2010-03-07 05:40:59 | 2010-03-07 05:41:33
2 | 2010-03-07 06:50:51 | 2010-03-07 10:50:51
3 | 2010-02-21 05:20:00 | 2010-03-07 12:23:44
4 | 2010-02-19 08:21:12 | 2010-03-07 12:37:28
5 | 2010-02-13 05:52:13 |
Where time_stop is empty the user is still logged on. In this case I would expect to see 2010-03-07 returned, since all users (5) were logged on at that moment. However if I would run the query with 'where time_start BETWEEN '2010-02-17' AND '2010-02-23' I would expect to see 2010-02-21 with a maximum of 2.
Is this possible direct in SQL (using postgres) or do I need to parse the results in PHP?
Thanks,
lleto
