Basic mysql question - how to search by time?

Question

I have a mysql table where I have a column called 'timecreated' with a value of NOW() when it is inserted.

How can I write a MYSQL statement to get rows that were inserted from the past minute?

This is not working:

SELECT * FROM mytable WHERE timecreated > '(NOW() - 60)' ORDER BY id DESC LIMIT 0 , 30

Answer 1

Use DATE_SUB():

SELECT * FROM mytable
 WHERE timecreated > DATE_SUB(NOW(), INTERVAL 1 MINUTE)
 ORDER BY id DESC LIMIT 0 , 30

Or you can do the same as expression:

SELECT * FROM mytable
 WHERE timecreated > NOW() - INTERVAL 60 SECOND
 ORDER BY id DESC LIMIT 0 , 30

Answer 2

You can :

SELECT *  from mytable where timecreated > SUBTIME(NOW() , '01:00:00')

to get all row where timecreated > now - 1 hours

or you can

SELECT * from mytable where timecreated > '2009-10-03 08:39:00')

if you want from a specific time.