Find the maximum number of registered users in SQL

Question

I want to monitor the number of concurrent users of my application. Therefore, I register time_start and time_stop. If now I want to query the database for the maximum number of registered users and return the start date, how would I do it.

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 | 

If time_stop is empty, the user is still logged in. In this case, I expect to see a return 2010-03-07, since all users (5) were registered at that moment. However, if I 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 directly in SQL (using postgres) or do I need to parse the results in PHP?

Thanks lleto

Answer 1

What about

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

If enddate will 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)

If the start date is '2010-02-17', and enddate will be'2010-02-23'

Try using MAX on any of the dates you want, 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)