MySQL从7天前选择行 [英] MySQL select rows from exactly 7 days ago

问题描述

我完全被这件事困扰了，尝试了几个小时却没有成功，希望有人能帮上忙.尝试构建每天运行的cron脚本，以返回比当前日期早7天的行.

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.

问题是，我的查询未返回任何内容.没有错误消息，什么也没有(我知道过去7天数据库中有条目-我们每天大约有7000个新条目，所以它们在那里！)我尝试了SELECT *并成功回显了编辑日期，所以除了我的SQL脚本之外，一切都可以正常工作.

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.

我要引用的列(edit_date)的类型为"datetime"，格式为Y-m-d h-m-s.此列始终具有在创建和编辑上分配的日期时间值.

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.

function get_ad_sql($table){
    $sql = "SELECT 
                * 
            FROM 
                ".$table." 
            WHERE 
                edit_date = DATE_SUB(NOW(), INTERVAL 7 DAY)
            ";  
    return $sql;
}

并调用该函数并尝试"以回显primary_key:

And calling the function and 'trying' to echo the primary_key:

$sqlAng = get_ad_sql('angebote');
$result = mysql_query($sqlAng);
while($row = mysql_fetch_array($result)){
    echo $row['primary_key'];
}

我尝试了DATE_SUB(NOW()，INTERVAL 7 DAY)的所有变体，包括CURDATE()，DATE_FORMAT(edit_date，'％m/％d/％Y')，我可以在这里和在线上找到它们，但什么都没做.希望有人能帮助我！

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!

推荐答案

获得相同的datetime条目(将日期和时间最多设置为秒)非常罕见.因此，为了获得适当的结果，我们需要使用CURDATE()函数忽略时间部分，而处理日期部分.

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.

您可以忽略时间部分，并使用以下方法与日期进行比较:

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;
}

这篇关于MySQL从7天前选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！