Question 234609 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get the maximum logged in application users and when

I have tried googling and checking questions on this site, but I think the terms "users" and "logged in" is throwing the answers.

I have an application that uses a MySQL database where User details are stored on the Users table. The MySQL server version is: 5.7.42-cll-lve – MySQL Community Server – (GPL).

I am trying to find the maximum concurrent users the application ever had, and when that was. Note that by users I mean users of my application, not database users. As a bonus, knowing the actual usernames (not just the number of users) would be helpful as well.

The table structure is as follows:

CREATE TABLE `Users` (
 `user_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `username` varchar(25) NOT NULL,
 `last_login` datetime NOT NULL,
 `last_logout` datetime NOT NULL,
 PRIMARY KEY (`user_ID`),
 UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1813 DEFAULT CHARSET=utf8

The query I have written based on help from this site is as follows:

SELECT user_ID, username, last_login, last_logout, last_logout - last_login AS seconds
FROM Users
WHERE last_login < last_logout
AND last_logout > last_login
ORDER BY last_login;

But that is not giving me the results I want. Note that the last_login and last_logout columns are stored as datetime.

I’ve also tried to use a JOIN similar to the below, but that’s also not working:

SELECT user_ID, username, last_login, last_logout, last_logout -last_login AS seconds
FROM Users A
INNER JOIN Users B ON (B.last_login <= last_logout)
AND (B.last_logout >= A.last_login_datetime)
AND A.user_ID <> B.user_ID;

What am I doing wrong?

Tags:

2

Answers


  1. 0

    tried this one

    SELECT COUNT(*) AS concurrent_users, GROUP_CONCAT(username) AS usernames, MAX(last_login) AS peak_time
FROM Users
WHERE last_logout IS NULL OR (last_login >= last_logout)
    Login or Signup to reply.
  2. 0

    Maybe like this:

    SELECT LoginDate,
       GROUP_CONCAT(username) AS 'Concurrent_users',
       COUNT(username) AS 'Total_users'
   FROM
(SELECT DATE(u1.last_login) AS LoginDate,
       u1.username,
       u1.last_login,
       u1.last_logout
FROM Users u1
JOIN Users u2
 ON u1.user_ID <> u2.user_ID
  AND u2.last_login BETWEEN u1.last_login AND u1.last_logout
UNION ALL
SELECT DATE(u2.last_login) AS LoginDate,
       u2.username,
       u2.last_login,
       u2.last_logout
FROM Users u1
JOIN Users u2
 ON u1.user_ID <> u2.user_ID
  AND u2.last_login BETWEEN u1.last_login AND u1.last_logout) a
GROUP BY LoginDate;

    It’s a UNION ALL query build from

    SELECT DATE(u1.last_login) AS LoginDate,
       u1.username,
       u1.last_login,
       u1.last_logout
FROM Users u1
JOIN Users u2
 ON u1.user_ID <> u2.user_ID
  AND u2.last_login BETWEEN u1.last_login AND u1.last_logout

    The only difference is the switch between u1 and u2. In the outer query, we group by login date, group_concat the username and count how many there is.

    Fiddle: https://dbfiddle.uk/Nl4Qp5OX

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search