Delete all rows older than 5 days

Question

I want to delete all rows older than 5 days in my table.

My table has a createdOn column of type date that can offer me a query.

I am using mySql database

now i wrote folloiwng query

SELECT * from `user_games` where created_on >= DATE_SUB(created_on, INTERVAL 5 DAY) 

to see user_games five days old.

but always get the same result even when I run a query for Invterval 1 Day or Interval 5 day

while today curdate is 2011-5-2

Answer 1

Try it,

 delete from mytable where datediff(now(), mytable.date) > 5 

This will be the correct removal approach.

Answer 2

Use date_sub , for example:

 delete from `myTable` where createdOn<DATE_SUB(curdate(), INTERVAL 5 DAY); 

Answer 3

You want to do this:

 delete from myTable where createdOn < CurDate() - 5 

Answer 4

DELETE FROM table_name WHERE time <(NOW () - INTERVAL 5 DAYS)