CODEWITHSUNDEEP
mysql
chris
chris

Reputation: 21197

Basic mysql question - how to search by time?

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

Upvotes: 0

Views: 65

Answers (2)

Nettogrof
Nettogrof

Reputation: 2136

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.

Upvotes: 1

ChssPly76
ChssPly76

Reputation: 100706

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

Upvotes: 4

Related Questions