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

Question

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.

Answer 1

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.