MySQL - DATE_ADD 月份间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6845604/
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 - DATE_ADD month interval
提问by Nugget
I face a problem with the function DATE_ADDin MySQL.
我DATE_ADD在 MySQL 中遇到了该函数的问题。
My request looks like this :
我的请求是这样的：
SELECT *
FROM mydb
WHERE creationdate BETWEEN "2011-01-01" AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH)
GROUP BY MONTH(creationdate)
The problem is that, in the results, -I think- because Junehas only 30 days, the function doesn't work properly as I have the results of the first of July.
问题是，在结果中，-我认为-因为June只有 30 天，所以该函数无法正常工作，因为我有7 月1日的结果。
Is there a way to tell DATE_ADDto work well and take the right number of days within a month?
有没有办法告诉你DATE_ADD工作良好并在一个月内使用正确的天数？
回答by Adriano Carneiro
DATE_ADDworks just fine with different months. The problem is that you are adding six months to 2001-01-01and July 1st is supposed to be there.
DATE_ADD在不同的月份工作得很好。问题是您要增加六个月，2001-01-01而 7 月 1 日应该在那里。
This is what you want to do:
这是你想要做的：
SELECT *
FROM mydb
WHERE creationdate BETWEEN "2011-01-01"
AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH) - INTERVAL 1 DAY
GROUP BY MONTH(creationdate)
OR
或者
SELECT *
FROM mydb
WHERE creationdate >= "2011-01-01"
AND creationdate < DATE_ADD("2011-01-01", INTERVAL 6 MONTH)
GROUP BY MONTH(creationdate)
For further learning, take a look at DATE_ADD documentation.
如需进一步学习，请查看DATE_ADD 文档。
*edited to correct syntax
*编辑以纠正语法
回答by wonk0
Well, for me this is the expected result; adding six months to Jan. 1st July.
嗯，对我来说这是预期的结果；到 1 月 1 日增加六个月。
mysql> SELECT DATE_ADD( '2011-01-01', INTERVAL 6 month );
+--------------------------------------------+
| DATE_ADD( '2011-01-01', INTERVAL 6 month ) |
+--------------------------------------------+
| 2011-07-01 |
+--------------------------------------------+
回答by sw0x2A
Do I understand right that you assume that DATE_ADD("2011-01-01", INTERVAL 6 MONTH)should give you '2011-06-30' instead of '2011-07-01'? Of course, 2011-01-01 + 6 months is 2011-07-01. You want something like DATE_SUB(DATE_ADD("2011-01-01", INTERVAL 6 MONTH), INTERVAL 1 DAY).
我是否理解正确，您认为DATE_ADD("2011-01-01", INTERVAL 6 MONTH)应该给您“2011-06-30”而不是“2011-07-01”？当然，2011-01-01 + 6 个月就是 2011-07-01。你想要类似的东西DATE_SUB(DATE_ADD("2011-01-01", INTERVAL 6 MONTH), INTERVAL 1 DAY)。
回答by Jacob
If expr is greater than or equal to min and expr is less than or equal to max,
BETWEENreturns 1, otherwise it returns 0.
如果 expr 大于或等于 min 且 expr 小于或等于 max，则
BETWEEN返回 1，否则返回 0。
The important part here is EQUAL to max., which 1st of July is.
这里的重要部分是 EQUAL to max.，即 7 月 1 日。
回答by yu_sha
DATE_ADDworks correctly. 1 January plus 6 months is 1 July, just like 1 January plus 1 month is 1 of February.
DATE_ADD工作正常。1 月 1 日加 6 个月是 7 月 1 日，就像 1 月 1 日加 1 个月是 2 月 1 日一样。
Between operation is inclusive. So, you are getting everything up to, and including, 1 July. (see also MySQL "between" clause not inclusive?)
操作之间是包含的。因此，您将获得包括 7 月 1 日在内的所有内容。（另请参阅MySQL“之间”子句不包括在内？）
What you need to do is subtract 1 day or use < operator instead of between.
您需要做的是减去 1 天或使用 < 运算符而不是 between。
