Delete Only MySQL/MariaDB Rows Older Than A Certain 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);

1	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;

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);

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.

Delete MySQL/MariaDB Rows Older Than Date

There are 6 comments

Cancel reply