Getting the recent one month or year records from MySQL table

Some time we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table.

For example let us find out who are the new members joined in our forum in last week. One shop may be interested in knowing new products added in last one month. What are the books arrived in last one year. Here irrespective of the date values we want the records of last X days from today, or we can say that the records between today and last X days ( month , year or week) are required.

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 .  

Last 10 days records

select * from dt_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)

The above query will return last 10 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_table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 10 DAY ) AND CURDATE( )

Last one month records

Let us try to get records added in last one month

select * from dt_table where `date` >= 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_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

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

Records of two date ranges

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_table where `date` 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_table where `date` 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_table where `date` 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.

Generate Query by using dates from Calendar

Records of last working days of week

Now let us try a different requirement. How to get the records of the working days of the week so far ? If today is Thursday then records from Monday to Thursday should be returned.

Records of working days of this week or previous week.



Dynamic SQL dump query

To test above queries you need to have records of last 15 days or last 6 months ( or 12 Months ). Any dump created today will not work in future dates ( say after 8 months ) . So to use above query which involves last 15 days or last 12 months records, we have to generate dump file every day.

SQL dump file generated based on today's date to test queries

Number of User Comments : 21


Google+

pushpinder bagga

07-05-2009

hello I am trying to rum this query concatenated with my php tag cloud generation generation query... I want to fetch tags popular in the week ordered by views... here is the querry ... and its not wroking... please help SELECT tag, views, date FROM tags WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() GROUP BY tag ORDER BY date DESC, RAND() ASC LIMIT 0,20
smo

07-05-2009

SELECT count(views)as no,tag,date FROM tags WHERE date BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE() GROUP BY tag ORDER BY no DESC LIMIT 0,20
pushpinder bagga

09-05-2009

I am sorry this does'nt work are desired... actually views are for each tag and is an updating value... each time a tag page loads its value is updated by 1 which also determines the font size in tha php tag cloud... please help... I am forced to use this query SELECT tag, views, date FROM tags WHERE date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY tag ORDER BY date DESC, RAND() ASC LIMIT 0,20
pushpinder bagga

14-05-2009

works SELECT * FROM $table WHERE disabled='0' AND featured='0' AND recommended='0' AND date BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 DAY) AND CURDATE() ORDER BY comments DESC, views DESC
mohsin khan

23-08-2009

i got the answer for what i m searching. thankyou.
casinoonesite.com

03-02-2010

I can't understand date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) gives me the twoo years ago
pramod

11-11-2010

hello i have table which contain date from 1995-01-01 to current date and i want to select only one date in month is there any query in mysql for this problem thanks
marcus wilson

15-11-2010

This doesnt seem to work for dates stored as a Unix Timestamp
Dahmane

21-10-2011

How do I generate a list of records opened in the last 12 months but only up to the last day of the previous month (i.e. 01.10.201 - 31-09-2011)? Many thanks.
sachin

15-02-2013

NICE EXAMPLE I WAS NOT KNOWN ABOUT INTERVAL AND DATE_SUB Functions
sam

23-03-2013

i want to show all data only
when set specific date and next specific date
sam

23-03-2013

plz tell query
how to get specific date to specific date record
Abdul Qadir

31-07-2013

Nice buddy i was in real delima before reading your blog but finally my delima comes to an end after reading this blog and material which required i found it from here. It works!!!!!!!!! :)
Ramcharan

21-08-2013

Good one
joseph

13-10-2013

I need some help, Generate and run a query that will return the customer number and day of the week for all orders placed in March of 2005.Thank you
muthuraj

26-03-2014

i want to delete the table records from current date to before 7 days.

what is the query for that?
smo

27-03-2014

delete from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
muthuraj

02-04-2014

its working...thank you...smo
santosh

09-04-2014

how to insert data in sql every 30 days and 15days its automatically generate
satish

10-04-2014

its working...thank you very much
suresh

05-09-2014

viey useful your code thank so mauch

Post Comment This is for short comments only. Use the forum for more discussions.






OPINION POLL

How you handle Search Engine Optimization ( SEO ) for your website
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer