MySQL: Working with Date Ranges

At times we have to collect the last 7 or 15 days of  data from MySQL. For example to create graphs or stats for the past x amount of days or weeks.  You can use these examples anytime you want records from the last X days from today. 

We will use the MySQL function CURDATE() to get the today’s date.

To get the difference in today date and previous day or month we have to use the MySQL function DATE_SUB(). DATE_SUB() is a MySQL function which takes date expression, the interval and the constant to return the date value for further calculation.

Here are some sample queries on how to get the records as per requirements.

SELECT * FROM dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 15 DAY);

The above query will return last 15 days records. Note that this query will return all future dates also. To exclude future dates we have to modify the above command a little by using between query to get records. Here is the modified one.

SELECT * FROM dt_tb WHERE `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 15 DAY ) AND CURDATE( );

Let us try to get records added in last one month

SELECT * FROM dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Here also future records will be returned so we can take care of that by using between commands if required.

select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

You can easily make out what the above query will return.

We can collect records between a particular date ranges by using between command and DATE_SUB. Here are some queries to generate records between two date ranges.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH )

This query will return records between last three months. This query again we will modify to get the records between three moths and six months.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH )

Now let us change this to get records between 6 month and 12 month.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH )

With this you can understand how the records between a month range or a year range can be collected from a table. Note that the months ranges are calculated starting from current day. So if we are collecting records of last three months and we are in 15th day of 9th month then records of 15th day of 6th month we will get but the records of 14th day of 6th month will be returning on next query that is between 3 months and 6 months.