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

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.