MySQL data time query

Question

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 checked 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.

If you're doing all the date updating/generating in MySQL, then timezones won't enter it. It'd only be if you were producing a date in PHP and using it in MySQL (or vice versa) that timezones would be a concern. — Marc B
– Marc B, Commented Jul 7, 2011 at 16:23

DRapp · Accepted Answer · 2011-07-07 17:43:07Z

1

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.

answered Jul 7, 2011 at 17:43

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PvdL · Accepted Answer · 2011-07-07 17:26:03Z

0

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?

answered Jul 7, 2011 at 17:26

PvdL

1,5931 gold badge20 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL data time query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related