CODEWITHSUNDEEP
mysqlsql
AlexGr
AlexGr

Reputation: 95

sql delete all rows older than 30 days

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

Upvotes: 7

Views: 22963

Answers (2)

Vignesh VS
Vignesh VS

Reputation: 939

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!

Upvotes: 17

Taylor
Taylor

Reputation: 1253

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'.

Upvotes: -3

Related Questions