• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 974
  • Last Modified:

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

Hi,

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.

SELECT
ps.production_status_id,
ps.reg_id,
ps.email_message_id,
ps.est_ship_date,
ps.revised_ship_date,
ps.date_modified,
ps.date_added
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());
0
Animaze
Asked:
Animaze
1 Solution
 
SharathData EngineerCommented:
What do you mean by not working? Are you seeing any unwanted data?
0
 
johanntagleCommented:
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.
0
 
AnimazeAuthor Commented:
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.
0
 
johanntagleCommented:
ok i think i understand you now.  Think you just need to switch to <=.  Try:

ps.date_modified <= date_add(curdate(), interval -7 day)
0
 
et_meCommented:
U could try this:

where DATEDIFF(CURDATE(),date_modified) > 6 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now