Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-04
5
Medium Priority
?
971 Views
Last Modified: 2012-09-04
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
Comment
Question by:Animaze
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 38366117
What do you mean by not working? Are you seeing any unwanted data?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38366119
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
 

Author Comment

by:Animaze
ID: 38366369
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38366491
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
 
LVL 3

Expert Comment

by:et_me
ID: 38366496
U could try this:

where DATEDIFF(CURDATE(),date_modified) > 6 

Open in new window

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

810 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