Mysql在两小时之间获取数据

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

enter image description here

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'
    )
);

This will return any rows where the search range overlaps the range in the row, including where the search range start before and ends after the range in the row.

SELECT
    idProperty
FROM property_work_hours
WHERE day = 1 AND
    (
        5 between (`working_hour_from` + if(`from_period` = 'PM',12,0)) AND (`working_hour_to` + if(`to_period` = 'PM',12,0)) OR 
        1 between (`working_hour_from` + if(`from_period` = 'PM',12,0)) AND (`working_hour_to` + if(`to_period` = 'PM',12,0)) OR
        (`working_hour_from` + if(`from_period` = 'PM',12,0)) BETWEEN 1 AND 5 OR
        (`working_hour_to` + if(`to_period` = 'PM',12,0)) BETWEEN 1 AND 5
    )

It will match rows where:

  • The search range starts during the row range
  • The search range ends during the row range
  • The search range starts and ends during the row range
  • The search range starts before the row range and ends after the row range.

IOW, any row where the search range overlaps the range in the row.

It works only for even hours. If you need minutes to be included, that can also be done easily. Instead of the using the AM/PM in the comparison, it converts to 24 hours time.