Night Shift intime and outtime

Question

Hi everyone,

I am stuck with an issue, I have to create an attendance system where i have an employee id and punching time. There can be multiple punches in a day . So firstly for night shift employees i can't determine his actual in and out time and thus not the duration too .

for example a person came office at 11 pm on 1st april and left at 4 am on 2nd april. in betwwen he also went out for dinner or tea . I dont have fixed no. of punches. and i need to handle 2 dates as well. So min and max logic wont work.

 

Column name in attendance table are:-sno,user_id,date,time.