Select records from today, this week, this month php mysql

I imagine this is pretty simple, but can't figure it out. I'm trying to make a few pages - one which will contain results selected from my mysql db's table for today, this week, and this month. The dates are entered when the record is created with date('Y-m-d H:i:s');. Here's what I have so far:

day where date>(date-(60*60*24))

 "SELECT * FROM jokes WHERE date>(date-(60*60*24)) ORDER BY score DESC"

week where date>(date-(60*60*24*7))

 "SELECT * FROM jokes WHERE date>(date-(60*60*24*7)) ORDER BY score DESC"

month (30 days) where date>(date-(60*60*24*30))

 "SELECT * FROM jokes WHERE date>(date-(60*60*24*30)) ORDER BY score DESC"

Any ideas would be much appreciated. Thanks!


Solution 1:

Assuming your date column is an actual MySQL date column:

SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC;        
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;

Solution 2:

Try using date and time functions (MONTH(), YEAR(), DAY(), MySQL Manual)

This week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW());

Last week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;