仅在 MySQL 中返回过去 3 个月的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15397015/
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
Returning records from the last 3 months only in MySQL
提问by user1783933
I have a table with a timestamp field. How do I get data from the last 3 months?
我有一个带有时间戳字段的表。如何获取过去 3 个月的数据？
In particular, March is my current month let say, 03/2012. I need to return records from the months March, February, and January only.
特别是，三月是我当前的月份，比如说03/2012。我只需要返回三月、二月和一月的记录。
回答by AlwaysBTryin
3 months before today:
3 个月前的今天：
select * from table where timestamp >= now()-interval 3 month;
Start with first of month:
从第一个月开始：
select * from table where timestamp >= last_day(now()) + interval 1 day - interval 3 month;
回答by fthiella
To get the first day of the current month, you could use this:
要获取当月的第一天，您可以使用以下命令：
DATE_FORMAT(CURDATE(), '%Y-%m-01')
if current date is 2013-03-13, it will return 2013-03-01, and we can just substract 2 months from this date to obtain 2013-01-01. Your query could be like this:
如果当前日期是2013-03-13，它会返回2013-03-01，我们可以从这个日期减去2个月得到2013-01-01。您的查询可能是这样的：
SELECT *
FROM yourtable
WHERE data >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH
回答by MLeblanc
Assuming you're using SQL Server (Oracle, MySQL and others have similar date functions), you can use the dateadd function to add or subtract an interval to the current date.
假设您正在使用 SQL Server（Oracle、MySQL 和其他类似的日期函数），您可以使用 dateadd 函数为当前日期添加或减去一个间隔。
If you want a full three months, you can subtract 3 months from today : DATEADD(m,-3,getdate())
如果你想要整整三个月，你可以从今天减去三个月： DATEADD(m,-3,getdate())
But, as you state, you only want data from January, February and March. You have to make some calculation based on today's date: dateadd(m,-2, CONVERT(datetime, CONVERT(VARCHAR(2), MONTH(getdate())) + '/01/' + CONVERT(VARCHAR(4), YEAR(getdate()))))
但是，正如您所说，您只需要 1 月、2 月和 3 月的数据。您必须根据今天的日期进行一些计算：dateadd(m,-2, CONVERT(datetime, CONVERT(VARCHAR(2), MONTH(getdate())) + '/01/' + CONVERT(VARCHAR(4), YEAR(getdate()))))
And in the end, get a query like
最后，得到一个类似的查询
SELECT fields
FROM table
WHERE timestampfield > DATEADD(m,-2, CONVERT(datetime, CONVERT(VARCHAR(2), MONTH(getdate())) + '/01/' + CONVERT(VARCHAR(4), YEAR(getdate()))))
--- edit --- erf, I just noticed the "mysql" tag... you can get more information on MySQL date functions here : https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
--- 编辑 --- erf，我刚刚注意到“mysql”标签......你可以在这里获得关于 MySQL 日期函数的更多信息：https: //dev.mysql.com/doc/refman/5.5/en/date -and-time-functions.html
回答by Pete_1
I know this is an old question, but to possibly save others time and to sum the above answers up for the case of needing (1) dates from current month and (2) dates from the prior 2 months (common when displaying data statistics):
我知道这是一个老问题，但为了可能节省其他人的时间并将上述答案总结为需要 (1) 当前月份的日期和 (2) 前 2 个月的日期（显示数据统计时常见） ：
WHERE ((timestamp >= NOW() - DATE_FORMAT(CURDATE(), '%Y-%m-01'))
OR (timestamp >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH))
