MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tbl_excel_attendance(
id int(11) NOT NULL AUTO_INCREMENT,
  user_id varchar(255) not null,
  verify_date datetime null,
  verify_state int not null,
  PRIMARY KEY (id)
);

INSERT INTO tbl_excel_attendance (user_id,verify_date,verify_state) VALUES
('16','2019-11-08 07:46','0'),
('16','2019-11-07 18:05','1'),
('16','2019-11-07 12:52','0'),
('16','2019-11-07 07:35','0'),
('16','2019-11-06 19:45','1'),
('16','2019-11-06 07:39','0'),
('19','2019-11-08 07:00','0'),
('19','2019-11-07 18:04','1'),
('19','2019-11-07 12:51','0'),
('19','2019-11-07 06:42','0'),
('19','2019-11-05 18:28','1'),
('19','2019-11-05 06:59','0'),
('19','2019-11-05 06:57','0'),
('39','2019-11-07 19:09','1'),
('39','2019-11-07 12:37','0'),
('39','2019-11-07 11:59','0'),
('39','2019-11-07 09:29','0'),
('39','2019-11-07 07:39','0'),
('40','2019-11-07 06:39','1'),
('40','2019-11-07 06:38','0'),
('40','2019-11-06 20:39','0')

Records: 21  Duplicates: 0  Warnings: 0


SELECT *
FROM tbl_excel_attendance
ORDER BY user_id, verify_date desc


id
user_id
verify_date
verify_state


1
16
2019-11-08 07:46:00
0

2
16
2019-11-07 18:05:00
1

3
16
2019-11-07 12:52:00
0

4
16
2019-11-07 07:35:00
0

5
16
2019-11-06 19:45:00
1

6
16
2019-11-06 07:39:00
0

7
19
2019-11-08 07:00:00
0

8
19
2019-11-07 18:04:00
1

9
19
2019-11-07 12:51:00
0

10
19
2019-11-07 06:42:00
0

11
19
2019-11-05 18:28:00
1

12
19
2019-11-05 06:59:00
0

13
19
2019-11-05 06:57:00
0

14
39
2019-11-07 19:09:00
1

15
39
2019-11-07 12:37:00
0

16
39
2019-11-07 11:59:00
0

17
39
2019-11-07 09:29:00
0

18
39
2019-11-07 07:39:00
0

19
40
2019-11-07 06:39:00
1

20
40
2019-11-07 06:38:00
0

21
40
2019-11-06 20:39:00
0


select * from 
(select A.*, 

LEAD(A.Verify_State,1) OVER (PARTITION BY A.user_id 
ORDER BY A.user_id, A.verify_date) as Nxt_row_State, 

LEAD(A.verify_date,1) OVER (PARTITION BY A.user_id 
ORDER BY A.user_id, A.verify_date) as TO_Date 
from tbl_excel_attendance as A ) as tbl_DTR_Mapped 
where
tbl_DTR_Mapped.verify_state = 0 and tbl_DTR_Mapped.Nxt_row_State = 1


id
user_id
verify_date
verify_state
Nxt_row_State
TO_Date


6
16
2019-11-06 07:39:00
0
1
2019-11-06 19:45:00

3
16
2019-11-07 12:52:00
0
1
2019-11-07 18:05:00

12
19
2019-11-05 06:59:00
0
1
2019-11-05 18:28:00

9
19
2019-11-07 12:51:00
0
1
2019-11-07 18:04:00

15
39
2019-11-07 12:37:00
0
1
2019-11-07 19:09:00

20
40
2019-11-07 06:38:00
0
1
2019-11-07 06:39:00


Select * from (
SELECT DISTINCT
       user_id,
       verify_state,
       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       LEAD(Verify_State,1) OVER (PARTITION BY user_id 
ORDER BY user_id, verify_date) as Nxt_row_State, 
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_excel_attendance) as q
where q.verify_state = 0 and q.nxt_row_state = 1
ORDER BY q.user_id, q.the_date;


user_id
verify_state
the_date
min_datetime
Nxt_row_State
max_datetime


16
0
2019-11-06
2019-11-06 07:39:00
1
2019-11-06 19:45:00

16
0
2019-11-07
2019-11-07 07:35:00
1
2019-11-07 18:05:00

19
0
2019-11-05
2019-11-05 06:57:00
1
2019-11-05 18:28:00

19
0
2019-11-07
2019-11-07 06:42:00
1
2019-11-07 18:04:00

39
0
2019-11-07
2019-11-07 07:39:00
1
2019-11-07 19:09:00

40
0
2019-11-07
2019-11-07 06:38:00
1
2019-11-07 06:39:00

id	user_id	verify_date	verify_state
1	16	2019-11-08 07:46:00	0
2	16	2019-11-07 18:05:00	1
3	16	2019-11-07 12:52:00	0
4	16	2019-11-07 07:35:00	0
5	16	2019-11-06 19:45:00	1
6	16	2019-11-06 07:39:00	0
7	19	2019-11-08 07:00:00	0
8	19	2019-11-07 18:04:00	1
9	19	2019-11-07 12:51:00	0
10	19	2019-11-07 06:42:00	0
11	19	2019-11-05 18:28:00	1
12	19	2019-11-05 06:59:00	0
13	19	2019-11-05 06:57:00	0
14	39	2019-11-07 19:09:00	1
15	39	2019-11-07 12:37:00	0
16	39	2019-11-07 11:59:00	0
17	39	2019-11-07 09:29:00	0
18	39	2019-11-07 07:39:00	0
19	40	2019-11-07 06:39:00	1
20	40	2019-11-07 06:38:00	0
21	40	2019-11-06 20:39:00	0

user_id	the_date	min_datetime	Nxt_row_State	max_datetime
16	2019-11-06	2019-11-06 07:39:00	1	2019-11-06 19:45:00
16	2019-11-07	2019-11-07 07:35:00	1	2019-11-07 18:05:00
19	2019-11-05	2019-11-05 06:57:00	1	2019-11-05 18:28:00
19	2019-11-07	2019-11-07 06:42:00	1	2019-11-07 18:04:00
39	2019-11-07	2019-11-07 07:39:00	1	2019-11-07 19:09:00
40	2019-11-07	2019-11-07 06:38:00	1	2019-11-07 06:39:00