Time Attendance Report using MySql in Laravel-CodePudding

Good Day!

I'm would like to generate a report that would display like the one in the attached photo. Click this example photo

I would like to separate the datetime into Time In AM (7:00AM), Time Out AM (12:00AM), Time In PM(12:30AM), Time Out PM((5:00PM) in the query

So far this is what I get, a static date only, I don't know how to make it dynamic by using the timerange only Click the example photo 2. Hope you can help. Thank you.

select datetime,id,
(CASE WHEN datetime > '19/04/2021 6:30' AND datetime < '19/04/2021 8:30' THEN datetime ELSE NULL END) as TimeInAM,
(CASE WHEN datetime > '19/04/2021 11:30' AND datetime < '19/04/2021 12:30' THEN datetime ELSE NULL END) as TimeOutAM,
(CASE WHEN datetime > '19/04/2021 12:30' AND datetime < '19/04/2021 13:30'THEN datetime ELSE NULL END) as TimeInPM,
(CASE WHEN datetime > '19/04/2021 16:30' AND datetime < '19/04/2021 21:30'THEN datetime ELSE NULL END) as TimeOutPM
from time_attendances WHERE id = 1345

CodePudding user response：

I suggest to not using reserved MySQL words such as datetime: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Are you trying something like this:

SELECT d,
if( DATE_FORMAT(d,'%r') between '06:30:00 AM' and '08:30:00 AM' ,  d, 'Null' )  as TimeInAM ,
if( DATE_FORMAT(d,'%r') between '11:30:00 AM' and '12:29:59 AM' ,  d, 'Null' )  as TimeOutAM ,
if( DATE_FORMAT(d,'%r') between '12:30:00 AM' and '01:30:00 PM' ,  d, 'Null' )  as TimeInPM ,
if( DATE_FORMAT(d,'%r') between '04:30:00 PM' and '09:30:00 AM' ,  d, 'Null' )  as TimeOutPM 
from test ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/62

DATE_FORMAT(date_time,'%r') return date in 07:01:00 AM,09:30:00 PM format.

Check based on your values and let me now.