Deleting records before a certain date

Question

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.

Answer 1

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)

Answer 2

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)

Answer 3

This is another example using defined column/table names.

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

Answer 4

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.