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

Solution 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());

Solution 2

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

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

Comments

[Marc B]

Not necessary. Try select now() + interval 1 day. You'll get a 'tomorrow' answer.

[maček]

I think you want > instead of =.

[Marc B]

@macek: at the time I wrote my answer, OP's question was "which are a day old".

[Adrian Cornish]

Interesting - learn something new every day - found that syntax in the middle of the DATE_ADD docs dev.mysql.com/doc/refman/5.5/en/…