MySql database 5.7 version - Check login of each user

UncleVince · August 12, 2022, 9:04am

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…

m_hutley · August 12, 2022, 10:32am

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.

UncleVince · August 12, 2022, 1:24pm

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

Even using the online example on db-fiddle?

m_hutley · August 12, 2022, 2:28pm

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);

UncleVince · August 12, 2022, 2:44pm

m_hutley:

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

m_hutley · August 12, 2022, 2:55pm

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)

UncleVince · August 12, 2022, 4:08pm

GREAT !!!

Thanks a lot!

The code below

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

benanamen · August 12, 2022, 5:03pm

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