Mysql – How to Get the First in and Last out in night shift

MySQLmysql-5.6

I already have the Query for Getting the Day shift but I'm Having a problem in the night shift, Which is this query.

SELECT user_id,
Min(verify_date) as First_IN,
Max(verify_date) as Last_Out
from tbl_attendance group by user_id,verify_date

But this query doesn't give the night shift so i tried another Query Which is:

SELECT user_id,verify_date,
Lead(verify_date,-1) OVER(PARTITION BY user_id order by verify_date desc)
from tbl_attendance

This Query Give's The past attendance but i don't know how do i get rid the extra attendance in my Biometrics (Or the Double tap in Biometrics) the Biometric is used as the door so i need to get the first in and last out.

And also I'm having a problem with my SQL Fiddle The Date Time is maybe broke it alway's give me this output 2019-10-17T07:55:00Z.
See this DB Fiddle

Desired Output :

User_id     in_time             out_time
71      17/10/2019 7:55     null
13      16/10/2019 18:44    17/10/2019 7:05
8       16/10/2019 19:47    17/10/2019 7:05
70      16/10/2019 18:25    17/10/2019 7:01
31      16/10/2019 19:02    17/10/2019 6:34
66      17/10/2019 6:19     null
64      16/10/2019 11:49    16/10/2019 22:05
36      16/10/2019 7:42     16/10/2019 19:03
34      16/10/2019 6:26     16/10/2019 19:03
79      16/10/2019 9:25     16/10/2019 19:02
76      16/10/2019 7:02     16/10/2019 19:02
13      16/10/2019 7:05     16/10/2019 18:44
71      16/10/2019 7:02     null

Best Answer

For dayshift use

SELECT DISTINCT
       user_id,
       DATE(verify_date) the_date,
       MIN(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) min_datetime,
       MAX(verify_date) OVER (PARTITION BY user_id, DATE(verify_date)) max_datetime
FROM tbl_attendance
ORDER BY user_id, the_date;

For nightshift use

WITH cte AS (SELECT user_id,
                    verify_date, 
                    LEAD(verify_date) OVER (PARTITION BY user_id ORDER BY verify_date) lead_date
             FROM tbl_attendance)
SELECT * 
FROM cte 
WHERE DATE(verify_date) != DATE(lead_date)
ORDER BY user_id, verify_date;

fiddle