7

There are a lot of questions on how to delete all rows older than 30 days but i can't find anything same with mine so i can fix it

i need to delete some records of messages that are older than 30 days, the column name with the date is named sentOn and the rows in that column looks like this 2018-01-12 12:25:00 How should i format my query to delete all records from the table containing those that are older than 30 days?

DELETE FROM messages WHERE sentOn < '2018-02-21 00:00:00';

would this work?

EDIT: above query works but very very slowly any way to make it faster? i tried now() but it gives error that the function is wrong

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
AlexGr
  • 95
  • 1
  • 2
  • 8
  • 2
    Hint: `where sentOn < now() - interval 30 day`. – Gordon Linoff Mar 15 '18 at 22:07
  • so now() for sql would be something like now('2018-03-16 12:24:00') (today date)? – AlexGr Mar 15 '18 at 22:24
  • Also, if you're ever in doubt as to what will be deleted, you can always run a `SELECT` with the same conditions first. – fubar Mar 15 '18 at 22:56
  • @AlexGr . . . `now()` in MySQL is `now()`. Is something unclear about the hint? – Gordon Linoff Mar 16 '18 at 01:15
  • 2
    If your query is slow, it's likely that 1) you don't have a relevant index (nothing starting with `sentOn`), 2) you have a lot of stuff writing to the table at the same time, and have lock contention or escalation, 3) The unit-of-work is becoming large and requesting more resources, 4+) something we need more info to dissect. For 1, you'd need to create an index. For 2/3, I'd recommend running this particular statement _outside_ a transaction (I'm assuming there's no reason to want these rows back) – Clockwork-Muse Mar 16 '18 at 23:24

2 Answers2

17

The following code will delete the records of messages that are older than 30 days

DELETE FROM messages WHERE sentOn < NOW() - INTERVAL 30 DAY;

The NOW() method in MySQL is used to pick the current date with time. INTERVAL 30 DAY used for subtracting 30 days from the current date. After the above query, you can check the current table using the SELECT statement. Thank you!

Vignesh VS
  • 921
  • 1
  • 14
  • 30
  • i just did that and the query returns 0 rows, `DELETE FROM messages WHERE sentOn < '2018-02-21 00:00:00';` returns like 200k rows – AlexGr Mar 20 '18 at 11:00
  • If your table doesn't contain messages that are older than 30 days then the query returns 0 rows. Sorry, I can't understand why did you need this date( '2018-02-21 00:00:00') in your query. If you take that date as your current date then just do the below query 'DELETE FROM messages WHERE sentOn < '2018-02-21 00:00:00' - INTERVAL 30 DAY;' – Vignesh VS Mar 20 '18 at 11:59
-3
DELETE FROM messages WHERE sentOn > '2018-02-21 00:00:00';

You want to delete messages that are greater than '2018-02-21 00:00:00'. You can check that the logic is correct first by Select * FROM messages WHERE sentOn > '2018-02-21 00:00:00'.

Taylor
  • 1,223
  • 1
  • 15
  • 30