Question 42178 in PHP Question posted in
Official manual can be found here.

mysql fetch data between hours [hh:mm:ss] – PHP

Fetch records between hours For example records below, i have tried to fetch the data between morning hours(07:30 to 19:30) and evening hours(19:30 to 07:30), but getting blank results while querying for evening results

migi_date|service_number
---------|-------
11:15:00 | 23KPLKS
18:32:42 | KPLSKS3
10:02:04 | OSNSJS0
23:79:00 | QIW8SKD
11:08:00 | 28SOKSL
22:29:00 | 2UJSOPD

SELECT * FROM `report` WHERE `migi_date` BETWEEN '07:30:00' AND '19:30:00';

migi_date|service_number
---------|-------
11:15:00 | 23KPLKS
18:32:42 | KPLSKS3
10:02:04 | OSNSJS0
11:08:00 | 28SOKSL

i can able to fetch data between ’07:30:00′ AND ’19:30:00′, but for ’19:30:00′ to ’07:30:00′ getting blank.

using same query with hour change

SELECT * FROM `report` WHERE `migi_date` BETWEEN '19:30:00' AND '07:30:00';

Please suggest the query.

Tags:

2

Answers


  1. 0

    Maybe these two queries could fit…? With the principle that I mentioned in the comment

    -- Morning
SELECT * FROM my_table WHERE my_hour BETWEEN '07:30:00' AND '19:30:00';

-- Evening
SELECT * FROM my_table WHERE (my_hour BETWEEN '19:30:01' AND '23:59:59') or (my_hour BETWEEN '00:00:00' AND '07:29:59');

    Query built from dummy names.

    Login or Signup to reply.
  2. 0

    You should take in consideration the column type.

    If your column is time which has range value -838:59:59 to 838:59:59
    therefore, you can’t expect mysql to use it as 24h round.

    @juan is almost right, you need 2 condition

    `migi_date` BETWEEN '19:30:00' AND '23:59:59'
OR
`migi_date` BETWEEN '00:00:00' AND '07:30:00'

    The same apply if it is a varchar().

    You need to adjust seconds if you don’t want to have boundaries values in both result

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search