By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE bio_info (id INT, bioname VARCHAR(255), attendance_datetime DATETIME);
INSERT INTO bio_info VALUES
(1 , 'test' , '2021-01-18 15:22:13'),
(2 , 'test' , '2021-01-19 00:05:49'),
(3 , 'test' , '2021-01-19 15:00:32'),
(4 , 'test' , '2021-01-20 00:06:22'),
(5 , 'test' , '2021-01-20 14:57:20'),
(6 , 'test' , '2021-01-20 23:58:23'),
(7 , 'test1' , '2021-01-18 15:00:13'),
(8 , 'test1' , '2021-01-19 00:00:49'),
(9 , 'test1' , '2021-01-19 15:00:32'),
(10 , 'test1' , '2021-01-20 00:00:22'),
(11 , 'test1' , '2021-01-20 14:00:20'),
(12 , 'test1' , '2021-01-20 23:00:23');
SELECT * FROM bio_info;
Records: 12 Duplicates: 0 Warnings: 0
| id | bioname | attendance_datetime |
|---|---|---|
| 1 | test | 2021-01-18 15:22:13 |
| 2 | test | 2021-01-19 00:05:49 |
| 3 | test | 2021-01-19 15:00:32 |
| 4 | test | 2021-01-20 00:06:22 |
| 5 | test | 2021-01-20 14:57:20 |
| 6 | test | 2021-01-20 23:58:23 |
| 7 | test1 | 2021-01-18 15:00:13 |
| 8 | test1 | 2021-01-19 00:00:49 |
| 9 | test1 | 2021-01-19 15:00:32 |
| 10 | test1 | 2021-01-20 00:00:22 |
| 11 | test1 | 2021-01-20 14:00:20 |
| 12 | test1 | 2021-01-20 23:00:23 |
WITH
cte AS ( SELECT bioname,
attendance_datetime,
1 + ROW_NUMBER() OVER (PARTITION BY bioname ORDER BY attendance_datetime) rn
FROM bio_info )
SELECT rn DIV 2 id,
bioname,
MIN(attendance_datetime) attendance_in,
MAX(attendance_datetime) attendance_out,
TIMEDIFF(MAX(attendance_datetime), MIN(attendance_datetime)) total_hours
FROM cte
GROUP BY id, bioname
ORDER BY bioname, id;
| id | bioname | attendance_in | attendance_out | total_hours |
|---|---|---|---|---|
| 1 | test | 2021-01-18 15:22:13 | 2021-01-19 00:05:49 | 08:43:36 |
| 2 | test | 2021-01-19 15:00:32 | 2021-01-20 00:06:22 | 09:05:50 |
| 3 | test | 2021-01-20 14:57:20 | 2021-01-20 23:58:23 | 09:01:03 |
| 1 | test1 | 2021-01-18 15:00:13 | 2021-01-19 00:00:49 | 09:00:36 |
| 2 | test1 | 2021-01-19 15:00:32 | 2021-01-20 00:00:22 | 08:59:50 |
| 3 | test1 | 2021-01-20 14:00:20 | 2021-01-20 23:00:23 | 09:00:03 |