MySql database 5.7 version - Check login of each user

Hi there,

The logins of each user are stored in the _t_user table of the mysql database 5.7 version

+----+------------+-----------------------+-----------+-------+-----------+
| ID | IP_ADDRESS | Date_Time_from_System | User_name | Event | Effective |
+----+------------+-----------------------+-----------+-------+-----------+
|  1 | XX1030729  | 2022-08-11 15:16:52   | CIRICILLS | IN    | NULL      |
|  2 | XX1030729  | 2022-08-11 15:16:28   | CIRICILLS | IN    | NULL      |
|  3 | XX1030729  | 2022-08-11 13:24:50   | CIRICILLS | IN    | NULL      |
|  4 | XX1030729  | 2022-08-11 13:23:52   | CIRR      | OUT   | NULL      |
|  5 | XX1030729  | 2022-08-11 13:23:26   | CIRICILLS | IN    | NULL      |
|  6 | XX1030729  | 2022-08-11 13:22:56   | CIRR      | IN    | NULL      |
|  7 | XX1030729  | 2022-08-11 13:22:54   | CIRICILLS | OUT   | NULL      |
|  8 | XX1030729  | 2022-08-11 13:20:52   | CIRICILLS | IN    | NULL      |
|  9 | XX1030729  | 2022-08-11 13:15:40   | CIRI      | OUT   | NULL      |
| 10 | XX1030729  | 2022-08-11 13:15:06   | CIRICILLS | IN    | NULL      |
| 11 | XX1030729  | 2022-08-11 13:14:12   | CIRI      | IN    | NULL      |
| 12 | XX1030729  | 2022-08-11 13:14:00   | CIRICILLS | IN    | NULL      |
| 13 | XX1030729  | 2022-08-11 13:13:30   | CIRICILLS | IN    | NULL      |
| 14 | XX1030729  | 2022-08-11 12:22:06   | CIRICILLS | OUT   | NULL      |
| 15 | XX1030729  | 2022-08-11 12:21:04   | CIRICILLS | IN    | NULL      |
| 16 | XX1030724  | 2022-08-12 12:22:06   | CIR       | OUT   | NULL      |
| 17 | XX1030728  | 2022-08-12 12:21:04   | CIR       | IN    | NULL      |
+----+------------+-----------------------+-----------+-------+-----------+
17 rows in set (0.06 sec)

Each user has three minutes to logout manually.

Each single logout is stored in the same table _t_user.

I need to update on the table _t_user the column Effective with value ‘N’ when the user log out manually within the expected three minutes.

I need this output

+----+------------+-----------------------+-----------+-------+-----------+
| ID | IP_ADDRESS | Date_Time_from_System | User_name | Event | Effective |
+----+------------+-----------------------+-----------+-------+-----------+
|  1 | XX1030729  | 2022-08-11 15:16:52   | CIRICILLS | IN    | NULL      |
|  2 | XX1030729  | 2022-08-11 15:16:28   | CIRICILLS | IN    | NULL      |
|  3 | XX1030729  | 2022-08-11 13:24:50   | CIRICILLS | IN    | NULL      |
|  4 | XX1030729  | 2022-08-11 13:23:52   | CIRR      | OUT   | N         |
|  5 | XX1030729  | 2022-08-11 13:23:26   | CIRICILLS | IN    | NULL      |
|  6 | XX1030729  | 2022-08-11 13:22:56   | CIRR      | IN    | N         |
|  7 | XX1030729  | 2022-08-11 13:22:54   | CIRICILLS | OUT   | N         |
|  8 | XX1030729  | 2022-08-11 13:20:52   | CIRICILLS | IN    | N         |
|  9 | XX1030729  | 2022-08-11 13:15:40   | CIRI      | OUT   | N         |
| 10 | XX1030729  | 2022-08-11 13:15:06   | CIRICILLS | IN    | NULL      |
| 11 | XX1030729  | 2022-08-11 13:14:12   | CIRI      | IN    | N         |
| 12 | XX1030729  | 2022-08-11 13:14:00   | CIRICILLS | IN    | NULL      |
| 13 | XX1030729  | 2022-08-11 13:13:30   | CIRICILLS | IN    | NULL      |
| 14 | XX1030729  | 2022-08-11 12:22:06   | CIRICILLS | OUT   | N         |
| 15 | XX1030729  | 2022-08-11 12:21:04   | CIRICILLS | IN    | N         |
| 16 | XX1030724  | 2022-08-12 12:22:06   | CIR       | OUT   | N         |
| 17 | XX1030728  | 2022-08-12 12:21:04   | CIR       | IN    | N         |
+----+------------+-----------------------+-----------+-------+-----------+
17 rows in set (0.06 sec)

Because the time difference from first login and next manually logout for user_name CIR, is less than the three minutes.

SELECT TIMEDIFF('2022-08-11 12:22:06','2022-08-11 12:21:04');
+-------------------------------------------------------+
| TIMEDIFF('2022-08-11 12:22:06','2022-08-11 12:21:04') |
+-------------------------------------------------------+
| 00:01:02                                              |
+-------------------------------------------------------+
1 row in set

I can’t update on the table _t_user the column Effective with value ‘N’ in the rows where the first login is stored.

The table _t_user after the SQL update query

+----+------------+-----------------------+-----------+-------+-----------+
| ID | IP_ADDRESS | Date_Time_from_System | User_name | Event | Effective |
+----+------------+-----------------------+-----------+-------+-----------+
|  1 | XX1030729  | 2022-08-11 15:16:52   | CIRICILLS | IN    | NULL      |
|  2 | XX1030729  | 2022-08-11 15:16:28   | CIRICILLS | IN    | NULL      |
|  3 | XX1030729  | 2022-08-11 13:24:50   | CIRICILLS | IN    | NULL      |
|  4 | XX1030729  | 2022-08-11 13:23:52   | CIRR      | OUT   | N         |
|  5 | XX1030729  | 2022-08-11 13:23:26   | CIRICILLS | IN    | NULL      |
|  6 | XX1030729  | 2022-08-11 13:22:56   | CIRR      | IN    | NULL      |
|  7 | XX1030729  | 2022-08-11 13:22:54   | CIRICILLS | OUT   | N         |
|  8 | XX1030729  | 2022-08-11 13:20:52   | CIRICILLS | IN    | NULL      |
|  9 | XX1030729  | 2022-08-11 13:15:40   | CIRI      | OUT   | N         |
| 10 | XX1030729  | 2022-08-11 13:15:06   | CIRICILLS | IN    | NULL      |
| 11 | XX1030729  | 2022-08-11 13:14:12   | CIRI      | IN    | NULL      |
| 12 | XX1030729  | 2022-08-11 13:14:00   | CIRICILLS | IN    | NULL      |
| 13 | XX1030729  | 2022-08-11 13:13:30   | CIRICILLS | IN    | NULL      |
| 14 | XX1030729  | 2022-08-11 12:22:06   | CIRICILLS | OUT   | N         |
| 15 | XX1030729  | 2022-08-11 12:21:04   | CIRICILLS | IN    | NULL      |
| 16 | XX1030724  | 2022-08-12 12:22:06   | CIR       | OUT   | N         |
| 17 | XX1030728  | 2022-08-12 12:21:04   | CIR       | IN    | NULL      |
+----+------------+-----------------------+-----------+-------+-----------+
17 rows in set (0.06 sec)

Any suggestion?

Please don’t suggest me to change MySql database version because it’s not up to me, sorry… :frowning:

INSERT INTO t_user(ID) SELECT … ON DUPLICATE KEY UPDATE Effective = “N”;

And then SELECT only those logout ID’s where the distance between login and logout was less than 3 minutes.

I think I don’t understand… could you please explain better?

Even using the online example on db-fiddle?

I’m sure that someone will be along to do it better than me shortly, as i’m not a streamlining expert, but here’s what i ended up with… (lort the formatting on this is a pain.)

INSERT INTO _t_user(ID, Effective) (
  SELECT ID,"N" AS Effective FROM (
    SELECT MIN(_t_user.ID) AS ID,
           MIN(
             TIME_TO_SEC(
               TIMEDIFF(_t_user.Date_Time_from_System,user2.Date_Time_from_System)
             )  
           ) AS timediff 
    FROM   _t_user 
    LEFT   JOIN _t_user AS user2 
    ON     _t_user.User_name = user2.User_name 
    WHERE  _t_user.Event = "OUT" 
    AND    _t_user.Date_Time_from_System > user2.Date_Time_from_System 
    GROUP  BY _t_user.ID
  ) diffs 
  WHERE timediff < 180
) ON DUPLICATE KEY UPDATE Effective = VALUES(Effective);

thanks for help

I’ve tried but I don’t have change on the table _t_user

https://www.db-fiddle.com/f/5T3qr61DP2qtDDaWUnpBgA/8

mysql> INSERT INTO _t_user ( ID, Effective ) (
		SELECT
			ID,
			"N" AS Effective 
		FROM
			(
			SELECT
				MIN( _t_user.ID ) AS ID,
				MIN( TIME_TO_SEC( TIMEDIFF( _t_user.Date_Time_from_System, user2.Date_Time_from_System ) ) ) AS timediff 
			FROM
				_t_user
				LEFT JOIN _t_user AS user2 ON _t_user.User_name = user2.User_name 
			WHERE
				_t_user.EVENT = "OUT" 
				AND _t_user.Date_Time_from_System > user2.Date_Time_from_System 
			GROUP BY
				_t_user.ID 
			) diffs 
		WHERE
			timediff < 180 
		) 
		ON DUPLICATE KEY UPDATE Effective =
VALUES
	( Effective );
Query OK, 0 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Well the 5 rows the query identifies already have "N"s in their Effective column.

OH. Wait… are you trying to modify the IN record corresponding to those values? I think I misunderstood the ask.

change MIN(_t_user.ID) to MIN(user2.ID) [this query could be vastly simplified at this point, but that’s the simplest change immediately. I will think of the streamlined update just to change the IN records)

GREAT !!!

Thanks a lot!

The code below

https://www.db-fiddle.com/f/5T3qr61DP2qtDDaWUnpBgA/9

Just wondering, what is the purpose for doing this? By the way, you really need to be consistent with your naming convention.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.