Delete MySQL Rows Older Than Date

June 25th, 2008

I ran into a problem recently where I needed to delete all rows in a MySQL table that were older than three months. This I was able to accomplish with the following statement:

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

Substitute table and column, and a proper date option. 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).

About Benjamin Perove

Benjamin has been associated with computer technology starting from a very early age, and has contributed to the success of many businesses and enterprises since 2001.

He loves to crush pow at Keystone, play acoustic guitar, climb rocks, and ascend mountains on his road bike.

Benjamin is an Avalanche fan and currently resides in Boulder, Colorado.

8 Comments so far

MySQL Tutorials @ November 6th, 2008

MySQL Tutorials…

The updates were performed in such a way that new Web server connections were delayed during the 30 seconds or so that PHP and MySQL were unavailable on each server. That should mean that as far as scripts on your Web site were concerned, there was zer…
Steve @ June 21st, 2011

I tried to run this and I get an error. Here is mine
_______________
DELETE FROM ‘cl_user’ WHERE ‘user_date_last_login’ < DATE_SUB(NOW(), INTERVAL 3 MONTH);
_______________

The error I get is.
_______________

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''cl_user' WHERE 'user_date_last_login' < DATE_SUB(NOW(), INTERVAL 3 MONTHS)' at line 1

_______________

Any Ideas??? I want to use this to delete users who have not logged in for more than 90 days. Let me know thanks so much.
ashraf @ June 21st, 2011

dear,

I want to delete data from a table from specified date to specified date. what will be the command?
George @ August 5th, 2011

Use the BETWEEN operator in the WHERE clause
Patrik Idén @ November 19th, 2011

Did you resolv this?

I tried to run this and I get an error. Here is mine
_______________
DELETE FROM ‘cl_user’ WHERE ‘user_date_last_login’ < DATE_SUB(NOW(), INTERVAL 3 MONTH);
_______________

The error I get is.
_______________

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''cl_user' WHERE 'user_date_last_login' < DATE_SUB(NOW(), INTERVAL 3 MONTHS)' at line 1

_______________

Any Ideas??? I want to use this to delete users who have not logged in for more than 90 days. Let me know thanks so much.
Leandro @ December 7th, 2011

Hey Patrik,
Try removing the quotes ” from the name of the table and field.. that might help
Jaffir @ February 11th, 2012

That was exactly what i was looking for
Delete information if older than… | GeekFreak @ February 24th, 2012

[...] I manually send a test out to test the fields for age older than three months and then delete it. http://benperove.com/howto/delete-my…der-than-date/ Code: DELETE FROM cl_user WHERE user_date_last_login < DATE_SUB(NOW(), INTERVAL 3 [...]