MySQL select rows from exactly 7 days ago

Question

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.

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.

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

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

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!

What does `SELECT DATE_FORMAT(edit_date),DATE_SUB(DATE_FORMAT(NOW()), INTERVAL 7 DAY)` give you? Do they look the same? — marnir, Oct 13 '11 at 15:44
Thanks to everyone for helping in a matter of minutes of post, very much appreciated. I've tried to upvote but it looks like I cannot until I have 15 rep points. Thanks again to all! — Tim Blackburn, Oct 13 '11 at 15:53

score 50 · Accepted Answer · edited May 29 '14 at 20:01

50

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

edited May 29 '14 at 20:01

Dallas

1,045
1
8
25

answered Oct 13 '11 at 15:48

Ghazanfar Mir

3,493
2
26
42

Please note: I updated the query, right hand side of the comparison operator to improve performance. – Ghazanfar Mir Oct 13 '11 at 15:52
Noted and implemented with no problems (but I added DAY to the end... is that the default for INTERVAL? – Tim Blackburn Oct 13 '11 at 15:55
Its good to add Day, you can have a look at these functions for further details: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add – Ghazanfar Mir Oct 13 '11 at 15:58
1

Doesn't run for me unless I change "INTERVAL 7" to "INTERVAL 7 DAY" – Josh Ribakoff Mar 25 '14 at 20:17
1

Suggested improvement: the left hand side DATE(edit_date) means that every checked row has to be type casted (you can see this after executing an EXPLAIN EXTENDED ... and after that SHOW WARNINGS). Better alternative is WHERE edit_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND edit_date < DATE_SUB(CURDATE(), INTERVAL 6 DAY); – Marcel Oct 05 '15 at 08:51

score 10 · Answer 2 · answered Oct 13 '11 at 15:49

NOW() returns DATETIME value, you should use a DATE function to get date without time, e.g. -

SELECT * FROM table WHERE edit_date = DATE_SUB(DATE(NOW()), INTERVAL 7 DAY);

If type of edit_date field is DATETIME, then this field should be wrapped by DATE() function too -

SELECT * FROM table WHERE DATE(edit_date) = DATE_SUB(DATE(NOW()), INTERVAL 7 DAY);

score 6 · Answer 3 · answered Oct 13 '11 at 15:42

6

Your script is working... I highly doubt you have something exactly 7 days ago (to the second).

Perhaps you wanted something 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.

answered Oct 13 '11 at 15:42

Brad

159,648
54
349
530

Yeah, I was trying to compare dates, not too the second. Thanks very much for your help guys! – Tim Blackburn Oct 13 '11 at 15:52

score 5 · Answer 4 · edited Aug 13 '15 at 11:41

5

 SELECT SUBDATE(CURDATE(), 7)

Try this.

edited Aug 13 '15 at 11:41

Meenesh Jain

2,532
2
19
29

answered Aug 13 '15 at 11:03

abi740

61
1
1

MySQL select rows from exactly 7 days ago

4 Answers4

Linked