php 获取最近 30 天的 mysql 结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13997176/
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
Getting mysql result from the last 30 days
提问by Sam Williams
Possible Duplicate:
Get from database but only last 30 days
可能重复：
从数据库中获取但仅持续 30 天
Hi I have some php code which I use to count the rows in a database from the last 30 days. The problem is, that if I change the piece of code so that the number changes from -30to -20, the output number goes from 272to 360instead of going down.
嗨，我有一些 php 代码，我用它来计算过去 30 天数据库中的行数。问题是，如果我更改代码段使数字从-30变为-20，则输出数字会从272变为360而不是下降。
Here is the code:
这是代码：
$result = mysql_query("SELECT * FROM all_count WHERE DATEDIFF(date,NOW()) = -30 and member ='000002'");
$num_rows60 = mysql_num_rows($result);
回答by softsdev
Try this
尝试这个
select * from `table` where `yourfield` >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
For days, year see below for example.
例如，对于天、年，请参见下文。
DATE_SUB(CURDATE(), INTERVAL 15 DAY) /*For getting record specific days*/
DATE_SUB(CURDATE(), INTERVAL 1 YEAR) /*for getting records specific years*/
对于 Anand，查询
BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE() ,INTERVAL 3 MONTH )
/* For Getting records between last 6 month to last 3 month
回答by Andy Lester
It's better to compare
比较一下比较好
`date`< DATE(NOW() - INTERVAL 30 DAY)
rather than
而不是
DATEDIFF(date,NOW()) = -30
In the first case, the date calculation is done only once, at the beginning of the query, and the database can use any indexes on the date column.
在第一种情况下，日期计算只进行一次，在查询开始时，数据库可以使用日期列上的任何索引。
The second query must calculate the DATEDIFFon every row, and the database can't use any indexes. The second query forces a full table scan.
第二个查询必须计算DATEDIFF每一行，并且数据库不能使用任何索引。第二个查询强制进行全表扫描。
Also, I strongly suggest that you not call your column date. Yes, I know you can quote the name with backticks, but that's just messy and when you forget then your syntax errors will be hard to forget. Come up with a more descriptive name for the column. What kind of date is it? What does the date represent?
另外，我强烈建议您不要调用您的 column date。是的，我知道你可以用反引号引用这个名字，但这只是一团糟，当你忘记时，你的语法错误将很难忘记。为该列提出一个更具描述性的名称。什么样的日期？日期代表什么？
回答by aynber
You can use this instead:
您可以改用它：
$result = mysql_query("SELECT * FROM all_count WHERE `date`< DATE(NOW() - INTERVAL 30 DAY) and member ='000002'");
回答by s1lence
As you can see in the documentation here, the DATEDIFFfunction in MySQL tells you the difference in days of the first date to the second.
正如您在此处的文档中所见，DATEDIFFMySQL 中的函数会告诉您第一个日期与第二个日期之间的天数差异。
Your query only selects all rows where the difference is exactly 30 days and not those that are up to 30 days ago. So it's completely possible, that the number of rows for the date 20 days ago is higher than 30 days ago. What you most likely wanted was:
您的查询仅选择差值恰好为 30 天的所有行，而不是最多 30 天前的行。所以完全有可能，20 天前日期的行数高于 30 天前。你最有可能想要的是：
SELECT * FROM all_count WHERE DATEDIFF(date,NOW()) >= -30 and member ='000002'
