CODEWITHSUNDEEP
phpmysqlsqldatetime
user3177012
user3177012

Reputation: 681

MySQL query to select records for the past N days

I have a 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?

Upvotes: 5

Views: 15130

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

You can use CURRENT_DATE or CURRENT_TIMESTAMP 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 >= CURRENT_DATE
                                  -- >= 2014-10-21 00:00:00

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

SELECT * FROM table WHERE date_added >= CURRENT_DATE - INTERVAL 7 DAY
                                  -- >= 2014-10-14 00:00:00

SELECT * FROM table WHERE date_added >= CURRENT_DATE - INTERVAL 30 DAY
                                  -- >= 2014-09-21 00:00:00

Upvotes: 15

Related Questions