Mysql在两小时之间获取数据

Question

Fetch records between to hours For example records between 5AM to 6PM Sql statement as below

SELECT idProperty 
from property_work_hours 
WHERE day = '1' 
  AND (CONCAT('5:00', ' ', 'AM') BETWEEN CONCAT(`working_hour_from`, ' ', `from_period`) 
  AND CONCAT(`working_hour_to`, ' ', `to_period`)     
  or   CONCAT('6:00', ' ', 'PM') BETWEEN CONCAT(`working_hour_from`, ' ', `from_period`) 
  AND CONCAT(`working_hour_to`, ' ', `to_period`)) GROUP BY idProperty

Table

Answer 1

Not sure why you are not using appropriate field types. You asked

"For example records between 5AM to 6PM"

but then have a where condition for 1AM! I guess you wanted, anyone who worked between 5AM to 6PM regardless when they started or finished working.

WHERE(
    (`day` = 1) -- Day is 1
    AND (
        -- starting time is between 5am to 6pm
        TIME(STR_TO_DATE(concat(time(`working_hour_from`),`Period_from`),'%h:%m:%s %p')) between '05:00:00' and '18:00:00'
        -- or finished between 5am to 6pm
        OR TIME(STR_TO_DATE(concat(time(`working_hour_to`),`period_to`),'%h:%m:%s %p')) between '05:00:00' and '18:00:00'
    )
);