How to delete a MySQL record after a certain time

Question

I want to delete some messages from my MySQL database after 7 days.

My message table rows have this format: id | message | date

The date is a timestamp in the normal format; 2012-12-29 17:14:53

I was thinking that an MySQL event would be the way to go instead of a cron job.

I have what I guess is a simple question to an experienced SQL person, how do I code the delete messages portion in brackets below?

An example would be appreciated, Thanks.

 DELIMITER $$
   CREATE EVENT delete_event
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
   ON COMPLETION PRESERVE
  DO
    BEGIN
      DELETE messages WHERE date >= (the current date - 7 days);
    END;
$$;

Answer 1

This should do the trick.

DELETE FROM messages WHERE date < (CURDATE() - INTERVAL 7 DAY);

Answer 2

You can try using this condition:

WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)

So that the whole SQL script looks like this:

CREATE EVENT delete_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE

DO BEGIN
      DELETE messages WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;

However, on your place I would solve the given problem with a simple cron script. The reasons to do this is simple: it's easier to maintain the code, no ugly SQL workarounds, integrates smoothly with your system.

Answer 3

For those out there who are on a shared hosting, like 1and1's, and can't create events, an alternative is to use webcron

You just need to tell webcron the url of the php script you'd like to be run, and they'll trigger it for you at the intervals you want