Entry

How can I delete all rows in a database table that are 30+ days old?

Jun 16th, 1999 07:00
Nathan Wallace, Jason Brooke, Colin Viebrock

You need to create a column in the table which stores the time that the
row was created.  This can be used to calculate it's age.

In MySQL you can do this with a column of type DATE, TIMESTAMP or
DATETIME.  Assuming you are using MySQL:

    DELETE FROM table WHERE 
        (TO_DAYS(NOW()) - TO_DAYS(datefield))>30

If you don't like using the DATE types there is an alternative method.

Create a column of type int and insert the unixtimestamp:

$sql = "insert into columnname (column1,column2,yourdatecolumn) values
('fu',$bar,time())";

Then to delete rows older than 30 days:

$sql = "delete from tablename where yourdatecolumn < time()-(86400*30)";

It's nice and easy, and if you ever need to get the date out and display
it, it works great with the date() function

If you want to calculate your dates to go from midnight to midnight 
rather than from exactly 30 days to the second from when the delete 
query is run, just use mktime on the insert to set the time to midnight 
on the day of the insert rather than to the current time of the insert:

$yourtime = mktime(0,0,0,date("m"),date("d"),date("y"));
$sql = "insert into columnname (column1,column2m,yourdatecolumn) values
('fu',$bar,$yourtime)";