Page 1 of 1

date less than

Posted: Mon May 14, 2007 10:36 pm
by bob_the _builder
Hi,

Can seem to get the correct solution for:

one field in the database is datetime .. 2007-05-14 13:00:00

I want to check for records that have 6 hours or less till the actual date and time match and perform a task, then mark that the task completed.

the marking part isnt a problem.

Whats the best query for this situation?


Thanks

Posted: Mon May 14, 2007 11:18 pm
by John Cartwright
Might want to read up on the datetime functions

Code: Select all

... WHERE date_column > DATE_SUB(NOW(), INTERVAL 6 HOUR)

Posted: Tue May 15, 2007 6:44 am
by bob_the _builder
Thanks,

Im still not having much luck, always ends up grabing all records after set date rather than records due in the next 6 hours.

So the database holds the following dates (datetime:

2007-05-15 11:00:00
2007-05-15 10:00:00
2007-05-16 05:00:00
... etc

perform a query on the 2007-05-14 for all records matching 2007-05-15 and run a task
next day 2007-05-15 query all rrecords matching 2007-05-16 ... etc

Thanks

Posted: Tue May 15, 2007 7:00 am
by volka
bob_the _builder wrote:Im still not having much luck, always ends up grabing all records after set date rather than records due in the next 6 hours.
May we see the code?

Posted: Tue May 15, 2007 7:15 am
by bob_the _builder
At the moment I have:

Code: Select all

$sql = mysql_query("SELECT * FROM calendar WHERE DATE_ADD(NOW(), INTERVAL 1 DAY) = starttime && sendemail = 1");
To try and acheive as explained above.

Thanks

Posted: Tue May 15, 2007 9:42 am
by blackbeard
bob_the _builder wrote:At the moment I have:

Code: Select all

$sql = mysql_query("SELECT * FROM calendar WHERE DATE_ADD(NOW(), INTERVAL 1 DAY) = starttime && sendemail = 1");
To try and acheive as explained above.

Thanks
Try this:

Code: Select all

$sql = mysql_query("SELECT * FROM calendar WHERE starttime BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 6 HOUR) AND sendemail = 1");