0

I need help with a query. I am taking input from a user where they enter a time range between 00:00 to 23.59. So it could be like 10:00 to 12:00 or 12:00 to 18:00. Then I need a query to pull data from a table that has a match_time stored in time format. Here we can assume that 10:00 being min and 12:00 being max range.

So if a user did 10:00 to 12:00 and the table had entries for 1:00, 2:30, 10:00, 11:30, 12:00, 15:00, 19:00 and 22:00 it would find 10:00, 11:30, 12:00. using MySQL

match_time BETWEEN (CAST('10:00:00' AS time)) AND (CAST('12:00' AS time))

But if they pass 18:00 to 3:00 that should output 1:00, 2:30, 19:00 and 22:00, not sure how to achieve this. Please help.

paradox
  • 153
  • 12
  • 2
    Does this answer your question? [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) – Dougie May 28 '20 at 09:15
  • @Dougie, no, as this involves the date, in my case only time is used – paradox May 28 '20 at 09:21
  • Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry May 28 '20 at 09:32

1 Answers1

4

Datatype TIME does not include knowledge about date ranges (3:00 in your query represents next day's 3:00). You have to handle this yourself:

SELECT columns 
FROM table
WHERE 
(cast('18:00' as time) <= cast('3:00' as time) and match_time between '18:00' AND '3:00') 
OR
(cast('18:00' as time) > cast('3:00' as time) and (match_time >= '18:00' or match_time<='3:00'));

See db-fiddle.

Martin
  • 22,212
  • 11
  • 70
  • 132
slaakso
  • 8,331
  • 2
  • 16
  • 27
  • working as expected, but can i know how to implement the same if match_time was divided into match_start_time and match_end_time, then how to query the same – paradox May 28 '20 at 12:20
  • can you check this query also https://stackoverflow.com/questions/62065450/how-do-i-query-between-two-time-range-when-time-was-divided-as-start-and-end-tim – paradox May 28 '20 at 13:33
  • @paradox See the answer for the other question. – slaakso May 28 '20 at 15:37