[SOLVED] How can i delete records older than 2 months?

[Poddy]

Hi, i want to run a cronjob at my application to delete records which are older than 2 months

i tried using

$sql = "SELECT * FROM `feeds` WHERE `timestamp`='DATE_SUB(CURDATE(), 60)'";
$result = mysql_query($sql) or die ('error' . mysql_error());
$row = mysql_fetch_assoc($result);
print_r($row);

note: this is a select because so far i cannot even select them..

 

this row returns me an empty result set

the timestamp is feeded by the CURRENT_TIMESTAMP and is a timestamp type

in the following format: 2008-05-29 14:53:38

 

thanks in advance to all helpers

 

[effigy]

Try:

$sql = "SELECT * FROM `feeds` WHERE `timestamp` <= DATE_SUB(CURDATE(), INTERVAL -2 MONTHS)";

[metrostars]

TRy:

 

$sql = "SELECT * FROM `feeds` WHERE `timestamp`='DATE_SUB(CURDATE(), INTERVAL 60 DAY)'";

$result = mysql_query($sql) or die ('error' . mysql_error());

$row = mysql_fetch_assoc($result);

print_r($row);

 

You missed out INTERVAL and DAY

[Poddy]

thanks for the replies

the working code is:

$sql = "SELECT * FROM `feeds` WHERE `timestamp` <= DATE_SUB(CURDATE(), INTERVAL 60 DAY)";

 

since month didn't want to work for some reason...

[effigy]

What about INTERVAL 2 MONTHS? I suppose the minus was pointless since DATE_SUB indicates what needs to be done.

[metrostars]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-add

 

I think the units are supposed to be singular. so it should be INTERVAL 2 MONTH . I might be wrong, but both might work.