如何使用MySQL在两个时间范围之间进行查询? [英] How do I query between two time range using MySQL?

问题描述

我需要查询方面的帮助.我正在从用户那里输入他们在00:00到23.59之间输入的时间范围.因此可能是10:00至12:00或12:00至18:00. 然后，我需要一个查询以从具有以时间格式存储的match_time的表中提取数据. 在这里，我们可以假设10:00为最小范围，而12:00为最大范围.

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.

因此，如果用户执行10:00到12:00，并且该表具有1:00、2:30、10:00、11:30、12:00、15:00、19:00和22的条目:00，它将找到10:00、11:30、12:00. 使用MySQL

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))

但是，如果他们经过18:00至3:00，则应输出1:00、2:30、19:00和22:00，则不确定如何实现此目标. 请帮忙.

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.

推荐答案

数据类型TIME不包括有关日期范围的知识(查询中的3:00代表第二天的3:00).您必须自己处理:

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'));

请参见 db-fiddle .

这篇关于如何使用MySQL在两个时间范围之间进行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！