Retrieve rows less than a day old

Question

I have a column date, which has a default value of CURRENT_TIMESTAMP, eg: 2011-11-16 15:34:02. Is there any way to put a condition in the mysql statement (not in php) to retrieve rows which are less than a day old? Something like:

SELECT * FROM orders where date > 24 hours ago

Answer 1

You can use timestampadd() to get the timestamp for 24 hours ago

SELECT * FROM orders WHERE `date` > timestampadd(hour, -24, now());

This is equivalent to

SELECT * FROM orders WHERE `date` > timestampadd(day, -1, now());

Answer 2

SELECT *
FROM orders
WHERE DATE(`date`) = DATE(NOW() - INTERVAL 1 DAY)

Note the backticks around date, as it's a reserved word.