Find maximum number of logged on users in SQL

Question

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

Answer 1

How about

SELECT COUNT(*)
FROM TABLE 
WHERE time_start <= enddate
AND (time_stop > enddate OR time_stop IS NULL)

Where the enddate would be '2010-02-23'

OR

SELECT COUNT(*)
FROM TABLE 
WHERE time_start BETWEEN startdate AND enddate
AND (time_stop > enddate OR time_stop IS NULL)

Where the startdate would be '2010-02-17' and enddate would be '2010-02-23'

Try using MAX on either one of the dates you wish for, something like

SELECT  MAX(time_start),MAX(time_stop), COUNT(*)
FROM    @Table
WHERE   time_start between '17 Feb 2010' AND  '23 Feb 2010'
AND     (time_stop > '23 Feb 2010' OR time_stop IS NULL)

Answer 2

Perfect max did the trick. Thanks for your help!

lleto