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

Ben has been associated with a broad spectrum of technologies starting from an early age, and he's contributed to the success of many businesses and enterprises since 2001. Most of his time is spent building cool stuff. When he's not working, he enjoys reading, playing acoustic guitar, and being with friends. He currently resides in Chiang Mai, Thailand.

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 Activity

Recent Entries

Topics

Archives

Got WordPress Security?

You will definitely wish you had downloaded my top 5 recommendations when you're cleaning out malicious Javascript from deep within you WordPress site. Enter your email and get the PDF right now, before it's too late.