Retrieve items from a MySQL database BUT with some mathematical rules first

I have this table on my MySQL server:

id      name    url     type    ut      lastcheck
1       Name1   url1    1       5       2011-06-28 12:21:46
2       Name2   url2    1       10      2011-06-28 12:21:46
3       Name3   url3    1       12      2011-06-28 12:21:46
4       Name4   url4    1       1       2011-06-28 12:21:46

ut means update time, in minutes. I want to develop a script using MySQL commands that retrieves me only the elements that complains this statement:

NOW() - lastcheck=>ut

I'm developing in php and I know how to do this retrieving all the elements and then using simple php operations to filter the elements I need, BUT I don't want any more load in my script. So I want my MySQL server to do all the开发者_如何学C work but I have no idea how to build the SQL query to do this.

I know that MYSQL can make difference between dates using SELECT TIMEDIFF(NOW(), '2010-11-26 12:00:00'); for example.

If I have understood correctly, you need to select the rows where the number of minutes since the last check is greater than ut?

You should be able to use the TIMESTAMPDIFF command for that:

SELECT * FROM table WHERE ut < TIMESTAMPDIFF(MINUTE,lastcheck,NOW());

The TIMESTAMPDIFF command allows you to specify the units that you wish to be output so it should give you exactly what you need.

If you have an index on ut, this may benefit from the index:

WHERE ut <= TIMESTAMPDIFF(MINUTE, lastcheck, NOW())

If you have an index on lastcheck, this may benefit from the index:

WHERE (NOW() - INTERVAL ut MINUTE) >= lastcheck

Thinking more clearly, I don't think any of the two versions can beneficially use any index at all.

The second perhaps (if the distinct values in ut are few compared to the rows of the table) would benefit from a JOIN to an auxilary table.

SELECT t.*
FROM table t
  JOIN 
    ( SELECT 
          ut 
        , (NOW() - INTERVAL ut MINUTE) AS nowMinusInterval 
      FROM table
      GROUP BY ut
    ) AS dt
  ON t.ut = dt.ut
WHERE dt.nowMinusInterval >= t.lastcheck