79

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.

Chaminda Bandara
  • 2,067
  • 2
  • 28
  • 31
Hard worker
  • 3,916
  • 5
  • 44
  • 73

5 Answers5

159
DELETE FROM table WHERE date < '2011-09-21 08:21:22';
Michael Mior
  • 28,107
  • 9
  • 89
  • 113
27

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)
Maddy
  • 1,233
  • 2
  • 12
  • 20
  • That was just what I needed, thanks. Only confusion for me was that you have to write "6 MONTH" not "6 MONTHS" as MySQL doesn't recognize it. This is a great way to keep audit logs manageable. – Tony Payne Jan 16 '15 at 16:26
14

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)
Rohan Khude
  • 4,455
  • 5
  • 49
  • 47
Harano Prithibi
  • 149
  • 1
  • 3
0

This is another example using defined column/table names.

DELETE FROM jos_jomres_gdpr_optins WHERE `date_time` < '2020-10-21 08:21:22';
Joniale
  • 515
  • 4
  • 17
0

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.

Riya Runjhun
  • 1