CODEWITHSUNDEEP
phpmysql
Ketan Savaliya
Ketan Savaliya

Reputation: 1210

Mysql Fetch Data Between Two Hours

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

Upvotes: 0

Views: 327

Answers (2)

Krish
Krish

Reputation: 5917

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

Upvotes: 1

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

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.

Upvotes: 0

Related Questions