MySQL data time query
I have a problem with my MySQL Query but despite the amount of searching I can't seem to find an answer.
I have a table that records data for something. It gets updated every 2 minutes by a cron job. When the data is intially input into the table the 'date' field is as follows:
0000-00-00 00:00:00
And then every time it gets updated it the NOW() function is used and so outputs as follows:
2011-07-07 15:44:02
Then when the object being recorded goes offline the data then gets filed away in a different table. The problem is that I cannot seem to write a successful query to select rows where the 'date' field has not been updated for 10 minutes.
The current query I have is below; (it seems to file the row but only after several hours. I have ch开发者_StackOverflow社区ecked the time zone as I suspected that is likely to be causing the problem however, the NOW() query is run from the same PHP file as the query below, so that can't be the problem.
SELECT * FROM data WHERE check_active = '1' AND check_recorded = '0' AND takeoff = '1' AND callsign != '' AND `date` < DATE_SUB( NOW() , INTERVAL 10 MINUTE )
Any help is greatly appreciated.
You have a bunch of other criteria in your WHERE condition... I would start with the date first to see how many records ARE found, then gradually add each "AND" criteria to see where it finally chokes...
SELECT *
FROM data
WHERE `date` < DATE_SUB( NOW() , INTERVAL 10 MINUTE )
THEN, add the others... one at a time...
AND check_active = '1'
AND check_recorded = '0'
AND takeoff = '1'
AND callsign != ''
It could completely be legit that you have no records available if all "objects" are still being updated within the 2 minute interval, and thus none of them are beyond the 10 minute cycle your are looking for "stale/offline" activity.
First, should you be addressing your field date
as following: date(date
) < .... to be able to calculate?
Second, you're are inserting every 2 minutes and if that doesn't happen you want to get output from this query? does that describe your needs?
精彩评论