mySQL 日期时间范围查询问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9935690/
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 dateTime range Query Issue
提问by Myy
I have a quick question. I'm have a db an audit Table with a datetime column in it. (i.e 2012-03-27 00:00:00) and I'm building a mySQL query to return a set of rows if the date is in between the two dates I'm giving it.
我有一个快速的问题。我有一个 db 一个审计表，其中有一个日期时间列。（即 2012-03-27 00:00:00）并且我正在构建一个 mySQL 查询以返回一组行，如果日期在我提供的两个日期之间。
so far my query looks like this:
到目前为止，我的查询如下所示：
SELECT * FROM util_audit WHERE DATED >= DATE(03/15/2012) AND DATED <= DATE(03/31/2012);
if I just use
如果我只是使用
SELECT * FROM util_audit WHERE DATED >= DATE(03/15/2012);
It'll return all my record because they were dated this week.
它会返回我所有的记录，因为它们是本周约会的。
I also tried this:
我也试过这个：
SELECT * FROM util_audit WHERE DATED >= '02/15/2012 00:00:00' AND DATED <= '03/31/2012 00:00:00';
and nothing! It'll return zero rows, when I know I have all of them dated from the 27 of this month to today. Am I missing something here? why does it work on its own, but not when I add the second date?I'm probably overlooking something.
没事了！它会返回零行，当我知道我所有的行都从本月 27 日到今天。我在这里错过了什么吗？为什么它自己工作，但当我添加第二个日期时却没有？我可能忽略了一些东西。
回答by Adi Gandra
Try:
尝试：
SELECT * FROM util_audit WHERE `DATED` BETWEEN "2012-03-15" AND "2012-03-31";
回答by James F
There's a few edge cases which need to be caught here by using the correct end date, if not items on the 31st are ignored:
有一些边缘情况需要通过使用正确的结束日期来捕获，如果不是 31 日的项目将被忽略：
SELECT * FROM util_audit
WHERE DATED >= '2012-02-15 00:00:00' AND DATED <= '2012-03-31 23:59:59';
SELECT * FROM util_audit
WHERE DATED BETWEEN '2012-02-15 00:00:00' AND '2012-03-31 23:59:59';
Or you could push the end date forward and use:
或者您可以将结束日期提前并使用：
SELECT * FROM util_audit
WHERE DATED >= '2012-02-15 00:00:00' AND DATED < '2012-04-01';
SELECT * FROM util_audit
WHERE DATED BETWEEN '2012-02-15 00:00:00' AND '2012-04-01';
Just came across this myself so hope it helps if other people find this page.
我自己刚刚遇到这个，所以希望如果其他人找到这个页面会有所帮助。
回答by Ezequiel Muns
As far as I know, dates in MySql are represented with the format yyyy-MM-dd hh:mm:sshence you need to do this:
据我所知，MySql 中的日期用格式表示，yyyy-MM-dd hh:mm:ss因此您需要这样做：
SELECT * FROM util_audit
WHERE DATED >= '2012-02-15 00:00:00' AND DATED <= '2012-03-31 00:00:00';
Or even better:
或者甚至更好：
SELECT * FROM util_audit
WHERE DATED BETWEEN '2012-02-15 00:00:00' AND '2012-03-31 00:00:00';
