MySQL

MySQL Date Problem

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2009

Posts: 8

Reputation: dan_ord is an unknown quantity at this point

Solved Threads: 0

dan_ord

Offline

Newbie Poster

MySQL Date Problem

Sep 8th, 2009

Hi, I'm using Cron Jobs to set up an automated script which checks for jobs which were posted 7 days ago, which will then delete the record and send the customer out an email.

I've got a bit of knowledge using php and mysql however i'm not sure on how to do the 7 days ago thing.

Below is a very rough bit of code to try explain the process.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
"SELECT * FROM quotes WHERE job_status = 'active' AND date_posted is = to 7 days ago";
 
if num_rows > 0 THEN execute DELETE query etc..
"SELECT * FROM quotes WHERE job_status = 'active' AND date_posted is = to 7 days ago";

if num_rows > 0 then execute delete query etc..

Some thing along the lines of that. I know above code incorrect, it's just for demonstration purposes, but if anyone can help me with this it would be greatly appreciated!

Also the time format im using is: 2009-08-24 12:04:55

Cheers Dan.

•

Join Date: Dec 2007

Posts: 190

Reputation: mwasif is an unknown quantity at this point

Solved Threads: 25

mwasif

Offline

Junior Poster

Re: MySQL Date Problem

Sep 9th, 2009

The correct query will be

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM quotes WHERE job_status = 'active' AND date_posted<= DATE_SUB(NOW(), INTERVAL 7 DAY)
SELECT * FROM quotes WHERE job_status = 'active' AND date_posted<= DATE_SUB(NOW(), INTERVAL 7 DAY)