Delete MySQL 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. This I was able to accomplish with the following statement:

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

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

  1. 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…

  2. 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.

  3. ashraf @ June 21st, 2011

    dear,

    I want to delete data from a table from specified date to specified date. what will be the command?

  4. George @ August 5th, 2011

    Use the BETWEEN operator in the WHERE clause

  5. 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.

  6. Leandro @ December 7th, 2011

    Hey Patrik,
    Try removing the quotes ” from the name of the table and field.. that might help

  7. Jaffir @ February 11th, 2012

    That was exactly what i was looking for

  8. [...] 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 [...]

Leave a reply

Add this site to your Firefox Search Bar

Twitter Updates


No public Twitter messages.

Recent Entries

Topics

Archives

This site is optimized for Firefox.