How do I query between two time range when time was divided as start and end time using MySQL?

Question

How do I query between two time range using MySQL?

it is similar to this question provided in the above link but the match_time was divided into two columns, i.e. match_start_time and match_end_time,

match_start_time <= CAST('10:00:00' AS time) AND match_end_time >= (CAST('12:00:00' AS time))

This was the query through which i tried but was not getting the correct result.

example

consider match start and end time being:-

01:30 - 03:30, 05:00 - 06:30, 03:00 - 21:30, 14:00 - 09:00

then if i pass 00:00 - 10:00 as min and max, then i get

01:30 - 03:30, 05:00 - 06:30, 14:00 - 09:00

but not sure whether 14:00 - 09:00 should be included.

Also if they pass 18:00 - 09:00

Then how to get the result if user provided min time is greater than max time Sorry for bad English, please help

Answer 1

The question is bit ambiguous. You can convert the times to datetime and determine into which date the times belong to:

select time_range, cast(time_to_match as time)
from (
  select 
    concat(m.match_start_time,'-',m.match_end_time) as 'time_range', 
    addtime(cast(concat('2020-01-', if(m.match_start_time<=m.match_end_time or m.match_start_time<t.match_time, '01', '02')) as datetime),t.match_time) as 'time_to_match',
    addtime(cast('2020-01-01' as datetime), m.match_start_time) as 'start_time',
    addtime(cast(concat('2020-01-', if(m.match_start_time<=m.match_end_time, '01', '02')) as datetime), m.match_end_time) as 'end_time'
  from times t
    join match_times m on 1=1
) as q
where time_to_match between start_time and end_time
order by 1,2;

See db-fiddle.