CODEWITHSUNDEEP
mysqldatetimedelete-row
Hard worker
Hard worker

Reputation: 4046

Deleting records before a certain date

How would I go about deleting all records from a MySQL table from before a certain date, where the date column is in DATETIME format?

An example datetime is 2011-09-21 08:21:22.

Upvotes: 82

Views: 199492

Answers (5)

Riya Runjhun
Riya Runjhun

Reputation: 1

If you are looking for Oracle SQL,then it might help:

Delete from table_name WHERE column_name < sysdate - INTERVAL '10' DAY

And do check on the format that sysdate is returing.

Upvotes: 0

Joniale
Joniale

Reputation: 565

This is another example using defined column/table names.

DELETE FROM jos_jomres_gdpr_optins WHERE `date_time` < '2020-10-21 08:21:22';

Upvotes: 0

Harano Prithibi
Harano Prithibi

Reputation: 149

To show result till yesterday

WHERE DATE(date_time) < CURDATE()

To show results of 10 days

WHERE date_time < NOW() - INTERVAL 10 DAY

To show results before 10 days

WHERE DATE(date_time) < DATE(NOW() - INTERVAL 10 DAY)

These will work for you

You can find dates like this 

SELECT DATE(NOW() - INTERVAL 11 DAY)

Upvotes: 14

Maddy
Maddy

Reputation: 1233

This helped me delete data based on different attributes. This is dangerous so make sure you back up database or the table before doing it:

mysqldump -h hotsname -u username -p password database_name > backup_folder/backup_filename.txt

Now you can perform the delete operation:

delete from table_name where column_name < DATE_SUB(NOW() , INTERVAL 1 DAY)

This will remove all the data from before one day. For deleting data from before 6 months:

delete from table_name where column_name < DATE_SUB(NOW() , INTERVAL 6 MONTH)

Upvotes: 27

Michael Mior
Michael Mior

Reputation: 28753

DELETE FROM table WHERE date < '2011-09-21 08:21:22';

Upvotes: 164

Related Questions