CODEWITHSUNDEEP
phpmysqlsql
Abhi
Abhi

Reputation: 5561

Deleting all rows older than 5 days

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

My table have a createdOn column of type date can u suggest me the query.

I m using mySql database

now i wrote folloiwng query

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

to see the user_games which are five days old

but always i m getting same result even when i run query for Invterval 1 Day or Interval 5 day

enter image description here

while today curdate is 2011-5-2

Upvotes: 7

Views: 22090

Answers (4)

Sanjay Mohnani
Sanjay Mohnani

Reputation: 990

Try this,

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

This would be proper approach to delete.

Upvotes: 20

Harry Joy
Harry Joy

Reputation: 59650

Use date_sub function like:

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

Upvotes: 12

Subhod30
Subhod30

Reputation: 488

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

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

You want to do this:

delete from myTable 
where createdOn < CurDate() - 5

Upvotes: 2

Related Questions