MySQL query that returns records that fall on or after date_modified + 7 Days

Posted on 2012-09-04
Last Modified: 2012-09-04

I'm trying to put together a MySQL query that returns records that fall on or after date_modified + 7 Days. After a bit of research this is what I came up with, but it isn't working correctly. All dates are DATETIME types.

FROM customer_production_status ps
WHERE (ps.email_message_id = '2' OR ps.email_message_id = '3')
AND (DATE_ADD(ps.date_modified,INTERVAL 7 DAY) >= NOW());
Question by:Animaze
    LVL 40

    Expert Comment

    What do you mean by not working? Are you seeing any unwanted data?
    LVL 24

    Expert Comment

    Basically you want records that have been modified the last seven days, right?  Try:

    ps.date_modified >= date_add(curdate(), interval -7 day)

    I used curdate() so that results will include start of day 7 days ago.  If you use now() and run it at noon, the coverage will only include starting noon 7 days ago.

    Also, when comparing with a date/datetime column, you keep the column "bare" and make calculations on the value to compare it to - doing it the way you did above will make mysql disregard any index on the date_modified column, if there's one.

    Author Comment

    Thanks johanntagle,

    I tried what you have, but I'm still getting records that have been added in past day, etc. This is actually the same issue as my original query (should answer your question too, Sharath_123)

    I'm looking to return records that are 7 days or older than date_modified. Put another way, I'm looking to see if at least 7 days have elapsed since the date_modified.

    I know this should be pretty straight-forward, just having trouble wrapping my head around the query needed.
    LVL 24

    Accepted Solution

    ok i think i understand you now.  Think you just need to switch to <=.  Try:

    ps.date_modified <= date_add(curdate(), interval -7 day)
    LVL 3

    Expert Comment

    U could try this:

    where DATEDIFF(CURDATE(),date_modified) > 6 

    Open in new window


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to count occurrences of each item in an array.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now