Max Simultaenous requests based on Date/Time

[ataulhaq]

Hi,

I have a table in which i have the information about actions along with the date/time when the action happened. I want to know maximum simultaneous requests for a given action at any given time.

Example: Users signing into web site. Here is some sample data (using integers at timestamps to keep the example simple) :

1. User A logs in at time 0.
2. User B logs in at time 0.
3. User C logs in at time 1.
4. User D logs in at time 3.
5. User A logs out at time 4.
6. User E, F and G logs in at time 5.
7. User C logs out at time 6.
8. All Users log out at time 7.

Now, I want to run a query on start time = 0, end time = 7 and I want to know what was the maximum simultaneous count for the number of users logged in?

Based on the data, the user/time distribution is:
time 0: 2 users
time 1: 3 users
time 2: 3 users
time 3: 4 users
time 4: 3 users
time 5: 6 users
time 6: 5 users
time 7: 0 users

and so, max. simultaneous users between 0 and 7 is 6 (at time = 5). How can I find this using SQL query?

Table structure:
1. Each row has log-in and log-out time.
2. Record is written to the table once user logs-out. Till that time, its kept in memory. (Changing this behavior is not an option right now).

You can assume that this table only stores log-in and log-out actions.

[pbmods]

Heya, ataulhaq. Welcome to TSDN!

The problem with trying to do this with a query is that you always have to start from time 0 or else your count might not be valid.

For example, if there are two Users logged in at time 0, but you start counting with time 2, your result set will always be off by two Users.

And what happens after your site has been around for awhile, and you want to get the max. simultaneous logins up to time 1,500,000?

The easiest way to accomplish this would be to create a counter that keeps track of the number of Users that are logged in and then saving it with each entry in the table.

E.g.,

SELECT
        MAX(`UserCount`)
    FROM
        `LoginActivity`
    WHERE
        `Time` BETWEEN 0 AND 7
    GROUP BY
        `Time`
 

Now, the *real* fun kicks in when your Users don't log themselves out when they're done using your site....

[ataulhaq]

Heya, ataulhaq. Welcome to TSDN!

The problem with trying to do this with a query is that you always have to start from time 0 or else your count might not be valid.

For example, if there are two Users logged in at time 0, but you start counting with time 2, your result set will always be off by two Users.

And what happens after your site has been around for awhile, and you want to get the max. simultaneous logins up to time 1,500,000?

The easiest way to accomplish this would be to create a counter that keeps track of the number of Users that are logged in and then saving it with each entry in the table.

E.g.,

SELECT
        MAX(`UserCount`)
    FROM
        `LoginActivity`
    WHERE
        `Time` BETWEEN 0 AND 7
    GROUP BY
        `Time`
 

Now, the *real* fun kicks in when your Users don't log themselves out when they're done using your site....

Hi,

Well, the counts may have created the situation more confusiong. The real data in database has date/time stamps. I just didn't want to write the whole timestamp and so used counts.

So, the comment regarding starting from count 0 may not be applicable since the query would be between two timestamps always. Like I want to know maximum number of users between 2007-06-01 10:00:00 and 2007-06-03 10:00:00.

I agree with the counter approach and that is what we are planning for but I was just wondering if there is a way we can do it using SQL. We do have a solution to it but it is not pure SQL. We get two results sets. One for log-in time and one for log out time and we merge both sets and sort it. So for every log in, we increment the count and for every log out we decrement it. The maximum count is the max simultaneous users within given time period.

[pbmods]

Heya, ataulhaq.

The issue with doing this using a query is that you have to take into account the number of Users who were logged in before you start calculating.

For example, assume 2 Users logged in at 2007-05-31 13:45:00 and 2007-05-31 13:45:05, respectively.

Here's where the problem occurs:

SELECT
    .
    .
    .
    WHERE
        `datetime` BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 11:59:59'
 

Notice that the number of active Users on June 1st will be at least two off because it won't take into account the two Users who logged in on May 31st without logging off.

And if the Users that were logged in on May 31st then log out on June 1st, your User count might even be negative!

I think your best bet is to either update a counter on login/logout, or you can do what phpBB does, but you'll have to combine it with a periodic script (such as cron, or by implementing a cron script and hope your traffic is frequent).

The way phpBB does it is the database stores the timestamp of the last action taken by the User (i.e., every time the User sends a request, the timestamp updates).

Then, to find out the number of *active* Users, simply count the number of timestamps greater than 15 minutes ago.

To get a history of this value, you'd need to run a script once per day and count (and index) the number of timestamps greater than 1 day ago.

The counter idea is probably easier, though the 'active' counter is probably a better benchmark.