CODEWITHSUNDEEP
mysqltimestampconditional-statements
user1038814
user1038814

Reputation: 9667

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

Upvotes: 10

Views: 16631

Answers (5)

PhoneixS
PhoneixS

Reputation: 11026

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.

Upvotes: 0

T30
T30

Reputation: 12222

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.

Upvotes: 0

Marc B
Marc B

Reputation: 360742

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

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

Upvotes: 9

maček
maček

Reputation: 77778

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

Upvotes: 23

max4ever
max4ever

Reputation: 12142

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

Upvotes: 0

Related Questions