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

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.

CodePudding user response：

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') and (my_hour BETWEEN '00:00:00' AND '07:29:59');

Query built from dummy names.

CodePudding user response：

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