在 MySQL 中使用 INTERVAL 和 CURDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2793619/
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
Working with INTERVAL and CURDATE in MySQL
提问by sf_tristanb
I'm building a chart and I want to receive data for each month.
我正在构建一个图表，我想接收每个月的数据。
Here's my first request which is working:
这是我的第一个工作请求：
SELECT s.GSP_nom AS nom, timestamp, AVG( v.vote + v.prix ) /2 AS avg
FROM votes_serveur AS v
INNER JOIN serveur AS s ON v.idServ = s.idServ
WHERE s.valide =1
AND v.date > CURDATE() -30
GROUP BY s.GSP_nom
ORDER BY avg DESC
But, in my case I've to write 12 request to receive data for the 12 previous months, is there any trick to avoid writing:
但是，就我而言，我必须编写 12 个请求来接收前 12 个月的数据，是否有任何技巧可以避免编写：
// example for the previous month
AND v.date > CURDATE() -60
AND v.date < CURDATE () -30
I heard about INTERVAL, I went to the MySQL doc but i didn't manage to implement it.
我听说过 INTERVAL，我去了 MySQL 文档，但我没有设法实现它。
Any example of using INTERVAL please?
请使用 INTERVAL 的任何示例？
回答by Pekka
You need DATE_ADD/DATE_SUB:
AND v.date > (DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
AND v.date < (DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
should work.
应该管用。
回答by seddy
As suggested by A Star, I always use something along the lines of:
正如 A Star 所建议的那样，我总是使用以下内容：
DATE(NOW()) - INTERVAL 1 MONTH
Similarly you can do:
同样你可以这样做：
NOW() + INTERVAL 5 MINUTE
"2013-01-01 00:00:00" + INTERVAL 10 DAY
and so on. Much easier than typing DATE_ADDor DATE_SUBall the time :)!
等等。比打字DATE_ADD或DATE_SUB一直打字容易得多:)！
回答by confiq
I usually use
我通常使用
DATE_ADD(CURDATE(), INTERVAL - 1 MONTH)
Which is almost same as Pekka's but this way you can control your INTERVAL to be negative or positive...
这与 Pekka 的几乎相同，但通过这种方式您可以控制您的 INTERVAL 为负数或正数...
