php MySQL 从 7 天前选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7756708/
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 select rows from exactly 7 days ago
提问by Tim Blackburn
I'm completely stumped on this one, being trying for hours but with no success, hoping someone can help. Trying to build a cron script to run daily that returns the rows that are exactly 7 days older than the current date.
我完全被这个难住了，尝试了几个小时但没有成功，希望有人能提供帮助。尝试构建一个每天运行的 cron 脚本，该脚本返回比当前日期早 7 天的行。
The thing is, my query is not returning anything. No error messges, nothing (I know there are entries in the DB from the last 7 days - we get about 7000 new entries a day, so they are there!) I've tried a SELECT * and echo out the edit date with success, so everything is working, apart from my SQL script.
问题是，我的查询没有返回任何内容。没有错误消息，什么都没有（我知道过去 7 天数据库中有条目 - 我们每天收到大约 7000 个新条目，所以它们在那里！）我试过 SELECT * 并成功回显编辑日期，所以一切正常，除了我的 SQL 脚本。
The column I'm referencing (edit_date) is type 'datetime' formated with Y-m-d h-m-s. This column always has a datetime value assigned on both create and edit.
我引用的列 (edit_date) 是用 Ymd hms 格式化的“日期时间”类型。此列始终具有在创建和编辑时分配的日期时间值。
function get_ad_sql($table){
$sql = "SELECT
*
FROM
".$table."
WHERE
edit_date = DATE_SUB(NOW(), INTERVAL 7 DAY)
";
return $sql;
}
And calling the function and 'trying' to echo the primary_key:
并调用该函数并“尝试”回显primary_key：
$sqlAng = get_ad_sql('angebote');
$result = mysql_query($sqlAng);
while($row = mysql_fetch_array($result)){
echo $row['primary_key'];
}
I've tried every variation of DATE_SUB(NOW(), INTERVAL 7 DAY), including CURDATE(), DATE_FORMAT(edit_date, '%m/%d/%Y') that I could find on here and online, but couldn't get anything to work. Hope someone can help me!
我已经尝试了 DATE_SUB(NOW(), INTERVAL 7 DAY) 的所有变体，包括 CURDATE()、DATE_FORMAT(edit_date, '%m/%d/%Y') 我可以在这里和网上找到，但不能'没有任何工作。希望可以有人帮帮我！
回答by Ghazanfar Mir
It is very rare to get same datetime entries which gives date and time upto seconds. Therefore, for getting appropriate results we need to ignore the time part, and deal with date part, thus, using CURDATE()function.
获得相同的日期时间条目非常罕见，这些条目将日期和时间提供到秒。因此，为了得到合适的结果，我们需要忽略时间部分，而处理日期部分，因此，使用CURDATE()函数。
You could do that ignoring the time part and compare with the date using following:
您可以忽略时间部分并使用以下方法与日期进行比较：
function get_ad_sql($table){
$sql = "SELECT
*
FROM
".$table."
WHERE
DATE(edit_date) = DATE_SUB(CURDATE(), INTERVAL 7 DAY)
";
return $sql;
}
回答by Devart
NOW() returns DATETIME value, you should use a DATE function to get date without time, e.g. -
NOW() 返回 DATETIME 值，您应该使用 DATE 函数来获取没有时间的日期，例如 -
SELECT * FROM table WHERE edit_date = DATE_SUB(DATE(NOW()), INTERVAL 7 DAY);
If type of edit_datefield is DATETIME, then this field should be wrapped by DATE() function too -
如果edit_date字段类型为 DATETIME，则该字段也应由 DATE() 函数包装 -
SELECT * FROM table WHERE DATE(edit_date) = DATE_SUB(DATE(NOW()), INTERVAL 7 DAY);
回答by Brad
Your script is working... I highly doubt you have something exactly7 days ago (to the second).
你的脚本工作......我很怀疑你有什么事情正是8天前（到秒）。
Perhaps you wanted something WHERE edit_date>DATE_SUB(NOW, INTERVAL 7 DAY) AND edit_date<DATE_SUB(NOW, INTERVAL 6 DAY)?
也许你想要什么WHERE edit_date>DATE_SUB(NOW, INTERVAL 7 DAY) AND edit_date<DATE_SUB(NOW, INTERVAL 6 DAY)？
Or, if you want to compare just the date (not the time) portions, compare the output of DATE()instead.
或者，如果您只想比较日期（而不是时间）部分，请比较 的输出DATE()。
回答by abi740
SELECT SUBDATE(CURDATE(), 7)
Try this.
尝试这个。
