Mysql date query

Check if the last INSERT was done later than hour ago

[mcibor]

I need to check if one script was run later than one hour ago - I had a problem with cron and want to put in a safety code.

On successful running of the script I remember the date in db

CREATE TABLE log(id INT NOT NULL auto_increment, ttime DATETIME, other_info TEXT, PRIMARY KEY(id));

The sql query that I use now is:

SELECT HOUR(CURDATE() - ttime) as h FROM log ORDER BY h LIMIT 1;

However I have a problem - it returns sometimes value NULL (which is always first).

If I use the following syntax:

SELECT HOUR(CURDATE() - ttime) as h FROM log WHERE h IS NOT NULL ORDER BY h LIMIT 1;

I get the error: Unknown column `h` in `where clause`.

What can I do?

[arran]

Hi mcibor,

Single quotes around the 'h' in the where clause should eliminate the syntax error.

arran.

[mcibor]

The syntax error is gone, however the null values remain.

What to do?

The query is:
SELECT HOUR(CURDATE() - ttime) as h FROM log WHERE 'h' IS NOT NULL ORDER BY h

Michal Cibor

[coopster]

You are using the HOUR function on a datetime value, needs to be a time value. Why not subtract an hour from the current date/time and SELECT those rows WHERE that value is greater than or equal to the value in your DATETIME column?

SELECT * FROM log WHERE DATE_SUB(NOW(), INTERVAL 1 HOUR) >= ttime;