How to delete a MySQL record after a certain time

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.


This should do the trick.

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