7

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

Abhi
  • 5,501
  • 17
  • 78
  • 133

4 Answers4

20

Try this,

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

This would be proper approach to delete.

JWC May
  • 605
  • 8
  • 14
Sanjay Mohnani
  • 990
  • 7
  • 18
  • 1
    Runs much faster than Harry Joy's answer below, not sure why. – iautomation Dec 11 '16 at 22:39
  • @iautomation I'd guess this is more efficient as datediff() only considers the date while DATE_SUB() takes the time into consideration as well. – Vincent Nikkelen Jun 17 '17 at 10:13
  • Sorry for replying to myself, but note that: DATEDIFF('2007-12-31 00:00:01','2007-12-30 23:59:59') = 1 day while DATE_SUB('2007-12-31 00:00:01','2007-12-30 23:59:59') = 2 seconds. – Vincent Nikkelen Jun 17 '17 at 10:20
12

Use date_sub function like:

delete from `myTable` where createdOn<DATE_SUB(curdate(), INTERVAL 5 DAY);
Amunak
  • 456
  • 5
  • 19
Harry Joy
  • 58,650
  • 30
  • 162
  • 207
  • 1
    `SELECT *` should be `DELETE` –  May 02 '11 at 04:56
  • 1
    @bdares: Yes I know I was just updating that. – Harry Joy May 02 '11 at 04:56
  • i m not getting desired results , i tried to see data by using select rather than delete , just to see whether desired rows get picked by query or not , but i m not getting right result – Abhi May 02 '11 at 05:04
  • 1
    +1: Will be able to use an index if one exists on the `createdon` column – OMG Ponies May 02 '11 at 05:16
  • hey it is not working appropriately, once i saw the query i think , yes i got the right sql code now , but when i run it ir always returns same resutls ,i have updated the resutls in my question the query given by sanjay found to be work perfectly , i m also amazed by this behaviour of three different queries – Abhi May 02 '11 at 05:21
2

You want to do this:

delete from myTable 
where createdOn < CurDate() - 5
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • 2
    +1 The addition is standards compliant and easier to read than `DATE_SUB` imho :) – Andomar May 02 '11 at 05:04
  • This will not work i have tried it earlier, currdate-5 returns some integer which in turns deletes every 2 days old rows, and this results in issues. – Abhi May 02 '11 at 05:05
  • That's funny, always worked with me. That's `createdOn`'s datatype? – Adriano Carneiro May 02 '11 at 05:08
  • ya Adrian i understand it's ur opinoin , wat i found by running query i told u , and i got the desired results by query given by Sanjay. It's not matter of few points but what is more imp is that if we found a new thing/concept/logic then it is better. – Abhi May 02 '11 at 05:15
  • 2
    +1: Will be able to use an index if one exists on the `createdon` column – OMG Ponies May 02 '11 at 05:17
  • I think the timing of comments betrayed us. By stating my opinion I was referring to @andomar's comment. You do have to choose as answer whatever solves your problem, which is what you did. – Adriano Carneiro May 02 '11 at 05:20
  • Did NOT work for v5.6.. I tried a SELECT before-hand and it returns all the rows. – iautomation Dec 11 '16 at 22:37
0

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

Subhod30
  • 390
  • 2
  • 3
  • 14