MySQL 8.0

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

CREATE TABLE checkinout (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  userid INT,
  checktime DATETIME,
  checktype CHAR(20)
);

INSERT INTO checkinout(userid, checktime, checktype) VALUES 
(34,'2023-08-01 07:52:23','In'),(27,'2023-08-01 10:57:18','In'),
(34,'2023-08-01 19:57:27','Out'),(27,'2023-08-02 00:12:06','Out');

SELECT * FROM checkinout;

Records: 4  Duplicates: 0  Warnings: 0




id
userid
checktime
checktype


1
34
2023-08-01 07:52:23
In

2
27
2023-08-01 10:57:18
In

3
34
2023-08-01 19:57:27
Out

4
27
2023-08-02 00:12:06
Out


/*defining a first check in time*/

SET @firstCheckIn := '07:00:00';

/*no grouping*/
SELECT userid,
       CASE WHEN TIME(checktime) <= @firstCheckIn 
             AND checktype='Out' THEN DATE(checktime) - INTERVAL 1 DAY
        ELSE DATE(checktime) END AS checkindate,
       CASE WHEN checktype='In' THEN checktime END AS time_in,
       CASE WHEN checktype='Out' THEN checktime END AS time_out
  FROM checkinout;

SELECT userid,
       CASE WHEN TIME(checktime) <= @firstCheckIn 
             AND checktype='Out' THEN DATE(checktime) - INTERVAL 1 DAY
        ELSE DATE(checktime) END AS checkindate,
       MAX(CASE WHEN checktype='In' THEN checktime END) AS time_in,
       MAX(CASE WHEN checktype='Out' THEN checktime END) AS time_out
  FROM checkinout
GROUP BY userid, checkindate;


userid
checkindate
time_in
time_out


34
2023-08-01
2023-08-01 07:52:23
null

27
2023-08-01
2023-08-01 10:57:18
null

34
2023-08-01
null
2023-08-01 19:57:27

27
2023-08-01
null
2023-08-02 00:12:06




userid
checkindate
time_in
time_out


34
2023-08-01
2023-08-01 07:52:23
2023-08-01 19:57:27

27
2023-08-01
2023-08-01 10:57:18
2023-08-02 00:12:06


INSERT INTO checkinout(userid, checktime, checktype) VALUES 
(34,'2023-08-02 08:01:23','In'),(27,'2023-08-02 09:30:10','In'),
(34,'2023-08-02 20:02:06','Out'),(27,'2023-08-02 23:30:06','Out'),
(55,'2023-08-31 20:30:27','In'),(55,'2023-09-01 01:30:06','Out');

SET @firstCheckIn := '07:00:00';

SELECT userid,
       CASE WHEN TIME(checktime) <= @firstCheckIn 
             AND checktype='Out' THEN DATE(checktime) - INTERVAL 1 DAY
        ELSE DATE(checktime) END AS checkindate,
       MAX(CASE WHEN checktype='In' THEN checktime END) AS time_in,
       MAX(CASE WHEN checktype='Out' THEN checktime END) AS time_out
  FROM checkinout
GROUP BY userid, checkindate;

Records: 6  Duplicates: 0  Warnings: 0




userid
checkindate
time_in
time_out


34
2023-08-01
2023-08-01 07:52:23
2023-08-01 19:57:27

27
2023-08-01
2023-08-01 10:57:18
2023-08-02 00:12:06

34
2023-08-02
2023-08-02 08:01:23
2023-08-02 20:02:06

27
2023-08-02
2023-08-02 09:30:10
2023-08-02 23:30:06

55
2023-08-31
2023-08-31 20:30:27
2023-09-01 01:30:06


CREATE TABLE presence_record_table (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_employee INT,
  checkindate DATE,
  time_in DATETIME,
  time_out DATETIME
);

SET @firstCheckIn := '07:00:00';
INSERT INTO presence_record_table(id_employee, checkindate, time_in, time_out)
SELECT userid,
       CASE WHEN TIME(checktime) <= @firstCheckIn 
             AND checktype='Out' THEN DATE(checktime) - INTERVAL 1 DAY
        ELSE DATE(checktime) END AS checkindate,
       MAX(CASE WHEN checktype='In' THEN checktime END) AS time_in,
       MAX(CASE WHEN checktype='Out' THEN checktime END) AS time_out
  FROM checkinout
GROUP BY userid, checkindate;

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM presence_record_table;

SELECT id, id_employee, 
       MONTH(checkindate) AS period_month,
       YEAR(checkindate) AS period_year,
       DAY(checkindate) AS date,
       TIME(time_in) time_in, TIME(time_out) time_out,
  /*getting duration*/ TIMEDIFF(time_out, time_in) Durations
FROM presence_record_table;


id
id_employee
checkindate
time_in
time_out


1
34
2023-08-01
2023-08-01 07:52:23
2023-08-01 19:57:27

2
27
2023-08-01
2023-08-01 10:57:18
2023-08-02 00:12:06

3
34
2023-08-02
2023-08-02 08:01:23
2023-08-02 20:02:06

4
27
2023-08-02
2023-08-02 09:30:10
2023-08-02 23:30:06

5
55
2023-08-31
2023-08-31 20:30:27
2023-09-01 01:30:06




id
id_employee
period_month
period_year
date
time_in
time_out
Durations


1
34
8
2023
1
07:52:23
19:57:27
12:05:04

2
27
8
2023
1
10:57:18
00:12:06
13:14:48

3
34
8
2023
2
08:01:23
20:02:06
12:00:43

4
27
8
2023
2
09:30:10
23:30:06
13:59:56

5
55
8
2023
31
20:30:27
01:30:06
04:59:39

id	userid	checktime	checktype
1	34	2023-08-01 07:52:23	In
2	27	2023-08-01 10:57:18	In
3	34	2023-08-01 19:57:27	Out
4	27	2023-08-02 00:12:06	Out

userid	checkindate	time_in	time_out
34	2023-08-01	2023-08-01 07:52:23	null
27	2023-08-01	2023-08-01 10:57:18	null
34	2023-08-01	null	2023-08-01 19:57:27
27	2023-08-01	null	2023-08-02 00:12:06

id	id_employee	period_month	period_year	date	time_in	time_out	Durations
1	34	8	2023	1	07:52:23	19:57:27	12:05:04
2	27	8	2023	1	10:57:18	00:12:06	13:14:48
3	34	8	2023	2	08:01:23	20:02:06	12:00:43
4	27	8	2023	2	09:30:10	23:30:06	13:59:56
5	55	8	2023	31	20:30:27	01:30:06	04:59:39