Nick Davies Nick Davies - 8 days ago 6
PHP Question

Pdo remove rows that are x days old

I would like to set up a cron job which will remove rows which are 5 days old i am in the uk but my server has a different time / date set on it so when i pull it it comes back with the wrong date so in my scripts i set the religion to mine this is what ive got so far

date_default_timezone_set('Europe/London');
$sTime = date("d-m-Y");
$sql = "DELETE FROM application_running WHERE date < NOW() - INTERVAL 5 DAY";
$stmt = $db->prepare($sql);
$stmt->execute();


This removes all result no matter how old they are i also tried to put edit the date like so

date_default_timezone_set('Europe/London');
$sTime = date("d-m-Y");
$sql = "DELETE FROM application_running WHERE date < date(d-m-Y) - INTERVAL 5 DAY";
$stmt = $db->prepare($sql);
$stmt->execute();


which again didn't work.

the result looks like 01-11-2016 in the database so just need to remove all rows that are 5 days older than $sTime

Answer

You can use the DateTime class from php

// create date with your timezone
$date = new \DateTime('now', new \DateTimeZone('Europe/London'));
// remove 5 days
$date->sub(new DateInterval('P5D'));  

$sql = "DELETE FROM application_running WHERE date < '{$date->format('d-m-Y')}' ";
$stmt = $db->prepare($sql);
$stmt->execute();

See:

http://php.net/manual/en/class.datetime.php

http://php.net/manual/en/datetime.sub.php

http://php.net/manual/en/class.datetimezone.php