Delete MySQL/MariaDB Rows Older Than Date

I ran into a problem recently where I needed to delete all rows in a MySQL table that were older than three months. To delete MySQL/MariaDB rows older than date xxxx-xx-xx, I was able to accomplish this with the following statement:

DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);

Substitute table and column, and a proper date option. Note that column does need to have a datetime or timestamp data type for the comparison to work.

The DATE_SUB function subtracts a time period from the date specified. In this statement, NOW grabs the current date from which INTERVAL 3 MONTHS is subtracted (along with the < operand).

Here’s another more complex example (which includes joins and nested select statements) which I wrote when building my Amzbot Slack app. The query was written in PHP (so as to explain the $vars), but it’s another example of how you can perform date/time operations using DATE_SUB. Hopefully you can adapt these examples to fit your needs.

SELECT products.asin, products.product_name, products_keywordrank.keyword,
	products_keywordrank.category, products_keywordrank.rank, products_keywordrank.timestamp
	FROM products_keywordrank
	INNER JOIN products
	ON products_keywordrank.product_id = products.id
	WHERE products.customer_id = $customer_id
	AND products_keywordrank.rank != 0
	AND products.customer_id IN
		(SELECT subscriptions.customer_id
		FROM subscriptions
		WHERE subscriptions.customer_id = $customer_id
		AND subscriptions.active = 1)
	AND DATE(`timestamp`) < DATE_SUB(CURDATE(), INTERVAL 12 HOUR)
	ORDER BY products_keywordrank.rank ASC;

The difference between NOW and CURDATE is this: NOW returns a full timestamp 2016-06-29 23:50:26 whereas CURDATE returns only the date and not time 2016-06-29.

Your available options for the INTERVAL statement can be any from the following table:

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

Here's a third example using BETWEEN to perform date comparison operations.

SELECT MIN(competitors_salesrank) AS salesrank
     FROM competitors_salesrank
     WHERE timestamp BETWEEN SUBDATE(NOW(), 2)
     AND SUBDATE(NOW(), 1);

So did you get it to work? Were you able to write the query that you needed? Let me know how it went for you below in the comments, or if you had any trouble getting it to work.