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?
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,