CODEWITHSUNDEEP
c#mysql.net
YosiFZ
YosiFZ

Reputation: 7900

MySql - Remove data after 3 months

I have MySql Database with 2 table: Events History

They have the same columns.

the first thing i want to do is to move all event that was passed to history and i achive it with :

@"INSERT INTO history (clientid, startdate, enddate, first, city) " +
            "SELECT clientid, startdate, enddate, first, city " +
            "FROM events " +
            "WHERE startdate < now()";

This work perfect.

Now i want to delete from history the rows that the startdate passed 3 months ago. How i can implement it?

Upvotes: 1

Views: 676

Answers (2)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

You can use Mysql Event scheduler as below:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP - INTERVAL 3 MONTHS
    DO
      //your query

For more information you can see: http://dev.mysql.com/doc/refman/5.1/en/create-event.html

Upvotes: 0

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

just compare the start date with actual date minus 3 months (using DATE_SUB)

delete from history
where start_date < DATE_SUB(now(), interval 3 month)

Upvotes: 3

Related Questions