MySQL query to select records newer than x days

Question

I'm sorry if this sounds like a very basic question but for some reason, today I'm really having trouble getting my head round this. I have a database table with a date_added column in the format of 2014-09-30 20:39:17 and I have a web page with filter options for users. Basically I want to use variables to select different date ranges like so:

SELECT * FROM table WHERE date_added = /* EVERYTHING POSTED TODAY */
SELECT * FROM table WHERE date_added = /* EVERYTHING POSTED WITHIN LAST 7 DAYS */
SELECT * FROM table WHERE date_added = /* EVERYTHING POSTED WITHIN LAST 30 DAYS */

What would I need to put in to get those variables to work?

Answer 1

You can use CURDATE() and very simple INTERVAL arithmetic.

In the following examples assume that query was executed at 2014-10-21 22:25:28:

SELECT * FROM table WHERE date_added >= CURDATE()
                                  -- >= 2014-10-21 00:00:00

SELECT * FROM table WHERE date_added >= NOW() - INTERVAL 24 HOUR
                                  -- >= 2014-10-20 22:25:28

SELECT * FROM table WHERE date_added >= CURDATE() - INTERVAL 7 DAY
                                  -- >= 2014-10-14

SELECT * FROM table WHERE date_added >= CURDATE() - INTERVAL 30 DAY
                                  -- >= 2014-09-21