You can use this in your MySQL WHERE clause to return records that were created within the last 7 days/week:
created >= DATE_SUB(CURDATE(),INTERVAL 7 day)
Also use NOW() in the subtraction to give hh:mm:ss resolution. So to return records created exactly (to the second) within the last 24hrs, you could do:
created >= DATE_SUB(NOW(),INTERVAL 1 day)
WHERE t.date >= DATE_ADD(CURDATE(), INTERVAL '-3' DAY);
use quotes on the -3 value
SELECT DATEDIFF(NOW(),pickup_date) AS noofday
FROM cir_order
WHERE DATEDIFF(NOW(),pickup_date)>2;
or
SELECT *
FROM cir_order
WHERE cir_order.`cir_date` >= DATE_ADD( CURDATE(), INTERVAL -10 DAY )
You could use a combination of the UNIX_TIMESTAMP() function to do that.
SELECT ... FROM ... WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(thefield) < 259200
Source: Stackoverflow.com