MySQL：比较时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4714899/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Compare BETWEEN time
提问by jmyz
This may be impossible in the way that I'd like to have it work, but here goes.
这可能以我希望它工作的方式是不可能的，但是这里是。
SELECT * FROM `table` WHERE CAST('05:00:00' AS time) BETWEEN `start` AND `end`
The entries in the database are:
数据库中的条目是：
`start` = '22:59:59'
`end` = '06:00:00'
However, the query returns no results. Of course, this would work if there were dates involved, however there are not. It would also work if the start was = '00:00:00'.
但是，查询不返回任何结果。当然，如果涉及日期，这将起作用，但是没有。如果开始是 = '00:00:00'，它也可以工作。
回答by changokun
i think what you are looking for is:
我认为你正在寻找的是：
SELECT * FROM table WHERE start < CAST('05:00:00' AS time) AND end > CAST('05:00:00' AS time)
I'm not sure if you need to use the CASTs, though this should also work.
我不确定您是否需要使用CAST，尽管这也应该有效。
SELECT * FROM table WHERE start < '05:00:00' AND end > '05:00:00'
回答by Quassnoi
SELECT *
FROM `table`
WHERE CAST('05:00:00' AS time) BETWEEN `start` AND `end`
OR (NOT CAST('05:00:00' AS time) BETWEEN `end` AND `start` AND `start` > `end`)
回答by GregD
I just came across this question and I had the same problem. My solution is as follow:
我刚刚遇到这个问题，我遇到了同样的问题。我的解决方案如下：
SET @time = '05:00:00';
SELECT * FROM `table`
WHERE IF(
`start` < `end`,
CAST(@time AS TIME) BETWEEN `start` AND `end`,
(CAST(@time AS TIME) BETWEEN CAST('00:00:00' AS TIME) AND `end`) OR
(CAST(@time AS TIME) BETWEEN `start` AND CAST('24:00:00' AS TIME))
) = 1;
Hope it helps someone in the future.
希望它可以帮助将来的某个人。
Thank you Martin for the hint!
谢谢马丁的提示！
回答by Faheem
We can compare time using simple time to secondsfunction. Use the following:
我们可以使用简单的time 和 seconds函数来比较时间。使用以下内容：
SELECT id
FROM table1
WHERE TIME_TO_SEC('2014-03-05 04:17:47') >= TIME_TO_SEC('2014-03-05 04:17:47');
