MySql database 5.5.46 version - Check login of each user

Question

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

+------------+-----------------------+-----------+---------+-----------+
| IP_ADDRESS | Date_Time_from_System | User_name | Event   | Effective |
+------------+-----------------------+-----------+---------+-----------+
| XX1030729  | 2022-08-11 15:16:52   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 15:16:28   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:24:50   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:23:52   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:23:26   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:22:56   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:22:54   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:20:52   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:19:40   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:15:06   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:14:12   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:14:00   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:13:30   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 12:22:06   | CIRICILLS | OUT     | NULL      |
| XX1030729  | 2022-08-11 12:21:04   | CIRICILLS | IN      | NULL      |
+------------+-----------------------+-----------+---------+-----------+

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.

Example for user_name CIRICILLS

+------------+-----------------------+-----------+---------+-----------+
| IP_ADDRESS | Date_Time_from_System | User_name | Event   | Effective |
+------------+-----------------------+-----------+---------+-----------+
| XX1030729  | 2022-08-11 15:16:52   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 15:16:28   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:24:50   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:23:52   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:23:26   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:22:56   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:22:54   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:20:52   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:19:40   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:15:06   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:14:12   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:14:00   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 13:13:30   | CIRICILLS | IN      | NULL      |
| XX1030729  | 2022-08-11 12:22:06   | CIRICILLS | OUT     | N         |
| XX1030729  | 2022-08-11 12:21:04   | CIRICILLS | IN      | N         |
+------------+-----------------------+-----------+---------+-----------+

Because the time difference from first login and next manually logout for user_name CIRICILLS, 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

Any suggestion?

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

Answer 1

This is done using MySQL5.7 . Assuming every OUT has an IN before it (no consecutive OUT) , we can use an OUTTER SELF JOIN to form a derived table in which the table1 has a datetime that is right after table2's datetime (except the very first table1's datetime). To ensure the datetime discrepancy between the two tables' datetime is immediate, I used the @row_id trick to generate row_id in chronological order. The rest is just to calculate the effective value via CASE . Here is the one I wrote and tested in workbench. Note I took the liberty of adding 4 rows in addition to your original 15 rows for more thorough testing. As commented after each statement which contains an OUT, 3 out of the 4 OUT are qualified as being 'N'.

INSERT INTO `_t_user` VALUES (1, 'XX1030729', '2022-08-11 15:16:52', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (2, 'XX1030729', '2022-08-11 15:16:28', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (3, 'XX1030729', '2022-08-11 13:24:50', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (4, 'XX1030729', '2022-08-11 13:23:52', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (5, 'XX1030729', '2022-08-11 13:23:26', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (6, 'XX1030729', '2022-08-11 13:22:56', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (7, 'XX1030729', '2022-08-11 13:22:54', 'CIRICILLS', 'OUT', NULL); -- qualify
INSERT INTO `_t_user` VALUES (8, 'XX1030729', '2022-08-11 13:20:52', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (9, 'XX1030729', '2022-08-11 13:19:40', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (10, 'XX1030729', '2022-08-11 13:15:06', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (11, 'XX1030729', '2022-08-11 13:14:12', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (12, 'XX1030729', '2022-08-11 13:14:00', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (13, 'XX1030729', '2022-08-11 13:13:30', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (14, 'XX1030729', '2022-08-11 12:22:06', 'CIRICILLS', 'OUT', NULL); -- qualify
INSERT INTO `_t_user` VALUES (15, 'XX1030729', '2022-08-11 12:21:04', 'CIRICILLS', 'IN', NULL);
INSERT INTO `_t_user` VALUES (16, 'XX1030729', '2022-08-11 15:20:04', 'CIRICILLS', 'OUT', NULL); -- disqualify
INSERT INTO `_t_user` VALUES (17, 'XX1030729', '2022-08-12 12:23:04', 'CIRICILLS', 'IN', NULL); 
INSERT INTO `_t_user` VALUES (18, 'XX1030729', '2022-08-12 12:25:04', 'CIRICILLS', 'OUT', NULL); -- qualify
INSERT INTO `_t_user` VALUES (19, 'XX1030729', '2022-08-13 12:25:04', 'CIRICILLS', 'IN', NULL);

Here is the query:

select  t1.ip_address,t1.date_time_from_system,t1.user_name,t1.event,
        case when t1.event='OUT' and time_to_sec(timediff(t1.date_time_from_system,t2.date_time_from_system )) <=180 
            then 'N' else null end as effective
from
    (select ip_address,date_time_from_system,user_name,event,effective,@row_id:=@row_id+1 as row_id
    from _t_user,(select @row_id:=0) t 
    order by date_time_from_system) t1
left join
    (select ip_address,date_time_from_system,user_name,event,effective,@row_num:=@row_num+1 as row_num
    from _t_user,(select @row_num:=0) t 
    order by date_time_from_system) t2
on t1.row_id-t2.row_num=1
order by t1.date_time_from_system
;

--result set from workbench:
# ip_address, date_time_from_system, user_name, event, effective
XX1030729, 2022-08-11 12:21:04, CIRICILLS, IN, 
XX1030729, 2022-08-11 12:22:06, CIRICILLS, OUT, N
XX1030729, 2022-08-11 13:13:30, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:14:00, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:14:12, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:15:06, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:19:40, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:20:52, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:22:54, CIRICILLS, OUT, N
XX1030729, 2022-08-11 13:22:56, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:23:26, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:23:52, CIRICILLS, IN, 
XX1030729, 2022-08-11 13:24:50, CIRICILLS, IN, 
XX1030729, 2022-08-11 15:16:28, CIRICILLS, IN, 
XX1030729, 2022-08-11 15:16:52, CIRICILLS, IN, 
XX1030729, 2022-08-11 15:20:04, CIRICILLS, OUT, 
XX1030729, 2022-08-12 12:23:04, CIRICILLS, IN, 
XX1030729, 2022-08-12 12:25:04, CIRICILLS, OUT, N
XX1030729, 2022-08-13 12:25:04, CIRICILLS, IN,