Retrieve rows less than a day old

问题:

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

回答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());

回答2:

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

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

回答3:

yup, combine *date_sub* with interval 1 day and curdate() and maybe something else see documentation here http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub

回答4:

This statement returns all today's rows:

select * from orders where date_field >= CURDATE();

CURDATE() returns today's date, so searches the records starting from midnight.

回答5:

You can use also the difference directly with timestampdiff function witch is similar to timestampadd.

SELECT * FROM orders WHERE TIMESTAMPDIFF(HOUR, date, NOW()) > 24;

I think this can't as optimized as using timestampadd (because it calculate the difference for each row) but it's, in my opinion, more readable and an alternative if you don't care about optimizing.