MySQL filtering SELECT results by date

It may be getting data from inbetween two dates or even a SELECT query for data from now to 1 week ago or older than 1 month, there is a few ways to do it with MySQL.

Keep in mind you need a date time column quite often these will store a date from a create/insert or update event.

SELECT between two dates

If you wanted to get data in between two date times in MySQL you can do this:

SELECT * FROM `table` WHERE `created` BETWEEN '2019-08-20 00:00:01' AND '2019-08-21 23:59:59';

Simply state the date value column (created) and then the two dates you want to filter your data down to.

SELECT within a date

Another way to filter down a SELECT is to get data from now to X time ago.

The time can be MINUTE, HOUR, DAY, WEEK, MONTH, YEAR etc. There are many.

SELECT * FROM `table` WHERE `created` >= DATE(NOW()) - INTERVAL 4 DAY;

Will return the data from up until 4 days ago

SELECT * FROM `table` WHERE `created` >= DATE(NOW()) - INTERVAL 1 WEEK;

Will return the data from up until 1 week ago

SELECT * FROM `table` WHERE `created` >= DATE(NOW()) - INTERVAL 6 MONTH;

Will return the data from up until 6 months back.

Just note the lack of the s. It is 5 WEEK not 5 WEEKS.

SELECT where older than

If you wanted to get all the results that are older than 2 weeks:

SELECT * FROM `table` WHERE `created` <= DATE(NOW()) - INTERVAL 2 WEEK;

Just use a less than sign instead of greater than!

This will return all data that is older than 1 day (24 hours):

SELECT * FROM `table` WHERE `created` <= DATE(NOW()) - INTERVAL 1 DAY;