Mysql Question posted in
The official MySQL documentation can be found here.

How to get time from next date in MySQL

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.

Tags:

2

Answers


  1. 0

    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:

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search