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

Please show us some information. The data you are talking about (example) the query you have so far, etc. So we dont have to guess what you are talking about — RiggsFolly, May 28 '20 at 13:08
question is not clear enough. you could add something like `AND match_start_time <= usergiventime AND match_end_time >= usergiventime` — WBGUY001, May 28 '20 at 13:10
@WBGUY001, updated with query which i have checked, please check — paradox, May 28 '20 at 13:26
Please clarify, what EXACTLY is in a single `match_start_time` and a `match_end_time` — RiggsFolly, May 28 '20 at 13:54
@RiggsFolly, match_start_time represents time in format (00:00:00) in mysql and same for match_end_time(23:59:59) — paradox, May 28 '20 at 14:16
Could be that you are getting `14:00 - 09:00` because it is tomorrows times. Do you also have a Date limiter in the where — RiggsFolly, May 28 '20 at 14:35
@RiggsFolly, yes TIME datatypes in MySQL, and no, i don't have limiter, we keep only time here — paradox, May 28 '20 at 15:02

slaakso · Answer 1 · 2020-05-28T20:19:34.680

0

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.

edited May 28 '20 at 20:19

answered May 28 '20 at 14:49

slaakso

8,331
2
16
27

will this work where if user passes min time as 18:00 and max as 09:00, where 09:00 represent the time of next day – paradox May 28 '20 at 19:54
@paradox See the db-fiddle. – slaakso May 28 '20 at 20:19
@ slaakso, here the match_start_time and match_end_time are two separate columns stored in db in TIME format eg: match_start_time: i) 09:00, ii) 10:00, iii) 18:00 eg: match_end_time: i) 12:00 ii) 15:00 iii) 09:00 where i), ii) iii) are respective rows of table. When user provides a range such as 09:00 to 16:00 as i/p, min being 09:00 and max being 16:00 the result would be in this case match_start_time: i) 09:00, ii) 10:00 match_end_time: i) 12:00 ii) 15:00 would be the result – paradox May 28 '20 at 20:30
in the fiddle provide, can i what is the use of times table, as user provides both min and max range – paradox May 28 '20 at 20:35

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

1 Answers1

Linked