user3177012 user3177012 - 16 days ago 5
MySQL Question

MySQL query to select records newer than x days

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

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

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