Deleting records before a certain date

MysqlDatetimeDelete Row

Mysql Problem Overview

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.

Mysql Solutions

Solution 1 - Mysql

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

Solution 2 - Mysql

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)

Solution 3 - Mysql

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)

Solution 4 - Mysql

This is another example using defined column/table names.

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

Solution 5 - Mysql

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.

Categories
Recommended Mysql Solutions
Recommended Datetime Solutions
Recommended Delete Row Solutions

Previous Solution:

Jackson: What happens if a property is missing?

JavaJackson

Next Solution:

creating over 20 unique legend colors using matplotlib

PythonMatplotlibLegend

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionHard workerView Question on Stackoverflow
Solution 1 - MysqlMichael MiorView Answer on Stackoverflow
Solution 2 - MysqlMaddyView Answer on Stackoverflow
Solution 3 - MysqlHarano PrithibiView Answer on Stackoverflow
Solution 4 - MysqlJonialeView Answer on Stackoverflow
Solution 5 - MysqlRiya RunjhunView Answer on Stackoverflow