MySQL 日期之间的 SQL，包括开始日期和结束日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8354638/
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
SQL between dates including start and end dates
提问by Karem
So I have this:
所以我有这个：
(CURDATE() BETWEEN start_date AND end_date)
Works fine.
工作正常。
But when the CURDATE()is 2011-12-02 and the end_dateis 2011-12-02 will it grab the row?
但是当CURDATE()是 2011-12-02 和end_date2011-12-02 时，它会抢占行吗？
E.g my start_date is 2011-12-01 00:00:00 and my end date is 2011-12-02 23:59:59
例如我的开始日期是 2011-12-01 00:00:00 而我的结束日期是 2011-12-02 23:59:59
So it only works when the date is between but not if it's ON the end_dateitself.
因此，它仅在日期介于两者之间时才有效，但如果它end_date本身处于开启状态则无效。
Or maybe it should check for the time too, because it still needs to be selected with this query when it's 2011-12-02 15:30:00 for example.
或者它也应该检查时间，因为例如在 2011-12-02 15:30:00 时仍然需要使用此查询选择它。
How can I do this?
我怎样才能做到这一点？
回答by Marco
Well, you could try
嗯，你可以试试
CURDATE() BETWEEN start_date AND DATE_ADD(end_date, INTERVAL 1 DAY)
回答by Mike Sherrill 'Cat Recall'
Since both columns are timestamps, you need to make sure times don't trip you up. To keep the times from tripping you up, cast the timestamps to date.
由于这两列都是时间戳，因此您需要确保时间不会让您失望。为了防止时间绊倒您，请将时间戳转换为日期。
where current_date between cast(start_date as date)
and cast(end_date as date);
回答by Eli Algranti
Maybe the answer to this question refers to a bug in an old version of MySql because betweenis inclusive, which means it will grab rows between the start and end dates inclusive, not just between the start and one day before the end.
也许这个问题的答案是指旧版本的 MySql 中的一个错误，因为betweenis inclusive，这意味着它将在开始和结束日期之间抓取行，而不仅仅是在开始和结束前一天之间。
Try this:
尝试这个：
SELECT CURDATE() BETWEEN CURDATE() AND CURDATE();
The result is 1(i.e. true).
I believe the original poster problem lies with mixing up proper dates (DATE) and dates with time (DATETIMEor TIMESTAMP).
结果是1（即true）。我相信最初的海报问题在于将正确的日期 ( DATE) 和日期与时间 (DATETIME或TIMESTAMP)混合在一起。
Try this:
尝试这个：
SELECT NOW() BETWEEN CURDATE() AND CURDATE();
SELECT NOW() BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 DAY);
The result is 0for the first select and 1for the second. What happened is a DATEis equivalent to a DATETIMEwith zero time so unless NOW()is called exactly at midnight it will be greater than CURDATE()and fall outside of the BETWEENstatement.
To prevent this test only the DATEpart of a DATETIMEusing the DATE()function:
结果是0第一次选择和1第二次选择。发生的事情是 aDATE等价于DATETIME零时间，因此除非NOW()恰好在午夜被调用，否则它将大于CURDATE()并落在BETWEEN语句之外。为了防止这个测试只使用函数的DATE一部分：DATETIMEDATE()
SELECT DATE(NOW()) BETWEEN CURDATE() AND CURDATE();
回答by xdazz
Use start_date <= CURDATE() AND end_date > CURDATE()
用 start_date <= CURDATE() AND end_date > CURDATE()
回答by Rahul
It will work ... BETWEENworks inclusive of the boundary values. That is,
它将起作用……BETWEEN包括边界值在内的工作。那是，
(CURDATE() BETWEEN start_date AND end_date)
including start_date,end_date and any day falling between
包括 start_date、end_date 和介于两者之间的任何一天
CURDATE() BETWEEN start_date AND ADDDATE(CURDATE(), INTERVAL 1 DAY);
回答by Leo Fazzi
cast (end_date - Start_date as double precision) * 86400
cast (end_date - Start_date 作为双精度) * 86400
