How to get time from next date in MySQL

Question

I have table Att_Log like this:

Emp_ID	Date_Att	Time_Att
E-0123	2024-01-02	08:05:56
E-0123	2024-01-02	17:02:23
E-0124	2024-01-02	21:55:56
E-0124	2024-01-03	06:02:23

the results I expect is like this:

Emp_ID	Date	In_Time	Out_Time
E-0123	2024-01-02	08:05:56	17:02:23
E-0124	2024-01-02	21:55:56	06:02:23

get the min(Time_Att) as Out_Time from following Date_Att if the In_Time is >= 21:00:00

How to query in MySQL server?

I can query the min and max Time_Att with this query:

SELECT Emp_ID, Date_Att, Min(Time_Att) AS InTime, Max(Time_Att) AS OutTime FROM Att_Log

but I don't know how to get the above result. If anyone can help, I would highly appreciate it. Thank you in advance.

Answer 1

Probably not what you want but gives your expected result for the sample data in your question.
(Assumes data type of column DATE_ATT is DATE and data type of TIME_ATT column is TIME. Refer to MySQL documentation.)

select EMP_ID
      ,date(min(timestamp(DATE_ATT, TIME_ATT))) as THE_DATE
      ,time(min(timestamp(DATE_ATT, TIME_ATT))) as IN_TIME
      ,time(max(timestamp(DATE_ATT, TIME_ATT))) as OUT_TIME
  from ATT_LOG
 group by EMP_ID

Refer to this db<>fiddle and these SO questions:

• Creating DATETIME from DATE and TIME
• How to separate DATE and TIME from DATETIME in MySQL?

Answer 2

Assuming that each employee only has 2 Att_Log entries a day, your query should just need a GROUP BY clause:

SELECT Emp_ID, Date_Att, Min(Time_Att) AS InTime, Max(Time_Att) AS OutTime
FROM Att_Log
GROUP BY Emp_ID, Date_Att

That should give you the highest and lowest dates for each day for each employee (rather than the highest and lowest for the whole table). If you want to manipulate the data-types of the dates, you can try the manipulations posted by Abra.

Answer 3

Hope this should work

WITH Att_Log as (
SELECT 'E-0123' as Emp_ID, '2024-01-02' as Date_Att, '08:05:56' as Time_Att UNION ALL
SELECT 'E-0123','2024-01-02','17:02:23' UNION ALL
SELECT 'E-0124','2024-01-02','21:55:56' UNION ALL
SELECT 'E-0124','2024-01-03','06:02:23')

SELECT Emp_ID,DATE(MIN(DATETIME(Date_Att || ' ' || Time_Att))) as Date,TIME(MIN(DATETIME(Date_Att || ' ' || Time_Att))) as In_Time,TIME(MAX(DATETIME(Date_Att || ' ' || Time_Att))) as Out_Time FROM Att_Log
GROUP BY Emp_ID

Answer 4

As the OP said in comments that the emp_id stands for employee_id, so this means there can be more attendance instances for an employee than the current sample table shows.

And since there is no attendance_id column which identifies an attendance(i.e a att_id records the In_Time and the same att_id that records the Out_Time), therefore, unless there is a clear principle that handles the case when an employee fails to register on the machine at an anticipated clock-out time, here I can only assume that there is no missing clock-out record. In this case, a given attentance should always be elicited in a pair, which consists of a row that represents the clock-in and the following row that represents the clock-out, then the row right after it represents another attendance record's clock-in,etc. And should the total number of rows for an emp_id is odd, it means there is an on-going attendance which has yet to clock out.

If the case above is true, then I suppose the min and max aggregate function for an emp_id is not helpful in deducing the clock-in and clock-out of an attendance if there are more than 2 rows for that emp_id. On the other hand, we need to rely on the sequence of (Date_Att,Time_Att) in ascending order to cover the ground.

The query involves 2 steps. First of all, get the sequence of Date_Att,Time_Att in ascending order for every emp_id. The result is stored in a CTE table. Then perform an outer join on two of the same CTE tables which matches a row of odd sequence to a row of even sequence.

create table Att_Log (Emp_ID varchar(10),   Date_Att date,  Time_Att time);

-- let's add more rows to your sample data to make it more realistic
insert Att_Log values
('E-0123','2024-01-02','08:05:56'),
('E-0123','2024-01-02','17:02:23'),
('E-0124','2024-01-02','21:55:56'),
('E-0124','2024-01-03','06:02:23'),
('E-0123','2024-01-03','17:02:23'),
('E-0124','2024-01-04','09:02:23'),
('E-0124','2024-01-04','11:02:23')
;

select * from att_log;

-- result
+--------+------------+----------+
| Emp_ID | Date_Att   | Time_Att |
+--------+------------+----------+
| E-0123 | 2024-01-02 | 08:05:56 |
| E-0123 | 2024-01-02 | 17:02:23 |
| E-0124 | 2024-01-02 | 21:55:56 |
| E-0124 | 2024-01-03 | 06:02:23 |
| E-0123 | 2024-01-03 | 17:02:23 |
| E-0124 | 2024-01-04 | 09:02:23 |
| E-0124 | 2024-01-04 | 11:02:23 |
+--------+------------+----------+

with cte as(
    select emp_id, date_att, time_att, 
    row_number() over(partition by emp_id order by date_att,time_att) as att_seq
    from att_log 
    )
select c1.emp_id, c1.date_att as `date`, c1.time_att as in_time,c2.time_att as out_time
from cte c1 left join cte c2
on c1.emp_id=c2.emp_id and c1.att_seq=c2.att_seq - 1
where c1.att_seq mod 2 = 1
;

-- result
+--------+------------+----------+----------+
| emp_id | date       | in_time  | out_time |
+--------+------------+----------+----------+
| E-0123 | 2024-01-02 | 08:05:56 | 17:02:23 |
| E-0123 | 2024-01-03 | 17:02:23 | NULL     |
| E-0124 | 2024-01-02 | 21:55:56 | 06:02:23 |
| E-0124 | 2024-01-04 | 09:02:23 | 11:02:23 |
+--------+------------+----------+----------+