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…