DATE_SUB() Getting the recent one month or year records from MySQL table

Syntax of DATE_SUB()
DATE_SUB(date, INTERVAL, expression, UNIT)
Example
SELECT DATE_SUB(  '2016-12-25', INTERVAL 3 DAY )
Output is 2016-12-22

We have subtracted three days ( Date time value ) form the given date by using DATE_SUB() function. In place of DAY we can use Month, year, hour, minute, second etc , here is the list.
unit ValueExpected expr FormatExample
DAYDAYSDATE_SUB( '2016-11-29', INTERVAL 10 DAY )
MONTHMONTHSDATE_SUB( '2015-11-20', INTERVAL 5 MONTH )
WEEKWEEKS DATE_SUB( '2016-08-20', INTERVAL 5 WEEK )
QUARTERQUARTERSDATE_SUB( '2016-08-20', INTERVAL 2 QUARTER )
YEAR YEARS DATE_SUB( '2016-02-23', INTERVAL 2 YEAR )
YEAR_MONTH 'YEARS-MONTHS'DATE_SUB( '2016-02-23', INTERVAL '2-5' YEAR_MONTH )
To use the above Examples add SELECT at left and run the query.
SELECT DATE_SUB( '2016-02-23', INTERVAL 2 YEAR ); // 2014-02-23 
SELECT DATE_SUB( CURDATE(), INTERVAL 2 YEAR ); // 2018-02-23
The second query depends on the todays date, so your result will be different.
Some time we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table.

Records of different date ranges by using DATE_SUB(), CURDATE() and BETWEEN() query.




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( )

Present Month Records

Starting from 1st day of the current month till now.
SELECT * FROM `dt_table` WHERE  date between  DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE()

Present Year Records

Starting from 1st Jan of the current Year till now.
SELECT * FROM `dt_table` WHERE  date between  DATE_FORMAT(CURDATE() ,'%Y-01-01') 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 BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH)  AND CURDATE();
Using Year
select * from dt_table WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
Using year with BETWEEN
SELECT * FROM dt_table WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR)  AND CURDATE();

Records of previous month of any year

SELECT * FROM  dt_table WHERE MONTH( DATE ) = MONTH( DATE_SUB(CURDATE(),INTERVAL 1 MONTH ))

Records of previous month of same year

SELECT * FROM  dt_table WHERE MONTH( DATE ) = MONTH( DATE_SUB(CURDATE(),INTERVAL 1 MONTH )) 
AND 
YEAR( DATE ) = YEAR( DATE_SUB(CURDATE( ),INTERVAL 1 MONTH ))
Note the difference between Last one month record and Previous month record

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.

Records of present week

SELECT * FROM `dt_table` WHERE WEEKOFYEAR(date)=WEEKOFYEAR(CURDATE())

Records of previous week

SELECT * FROM `dt_table` WHERE WEEKOFYEAR(date)=WEEKOFYEAR(CURDATE())-1

Records of next week

SELECT * FROM `dt_table` WHERE WEEKOFYEAR(date)=WEEKOFYEAR(CURDATE())+1

Records of present week all working days ( Mon - Fri )

SELECT * FROM `dt_table` WHERE WEEKOFYEAR(date)=WEEKOFYEAR(CURDATE()) 
AND 
WEEKDAY(date)  BETWEEN 1 AND 5

Records of present week all working days till today

SELECT * FROM `dt_table` 
WHERE WEEKOFYEAR( DATE ) = WEEKOFYEAR( CURDATE( ) )
AND WEEKDAY( DATE ) BETWEEN 1 AND WEEKDAY(CURDATE())

Using Date and time Queries

Now let us calculate with Time
unit ValueExpected expr Format
Example
MICROSECOND MICROSECONDS
DATE_SUB( '2016-02-23 20:55:58', INTERVAL 225 MICROSECOND )
SECOND SECONDS
DATE_SUB( '2016-02-23 20:55:58', INTERVAL 2 SECOND )
MINUTE MINUTES
DATE_SUB( '2016-02-23 20:55:58', INTERVAL 2 MINUTE )
HOUR HOURS
DATE_SUB( '2016-02-23 20:55:58', INTERVAL 5 HOUR )
SECOND_MICROSECOND'SECONDS.
MICROSECONDS'
DATE_SUB( '2016-02-23 20:55:58', INTERVAL '1.543' SECOND_MICROSECOND )
MINUTE_MICROSECOND 'MINUTES:
SECONDS.
MICROSECONDS'
DATE_SUB
( '2016-02-23 20:55:58',
INTERVAL '5:2.743' MINUTE_MICROSECOND )
MINUTE_SECOND 'MINUTES:
SECONDS'
DATE_SUB( '2016-02-23 20:55:58', INTERVAL '5:2' MINUTE_SECOND )
HOUR_MICROSECOND 'HOURS:MINUTES:
SECONDS.
MICROSECONDS'
DATE_SUB( '2016-02-23 20:55:58', INTERVAL '5:2:1.249' HOUR_MICROSECOND)
HOUR_SECOND 'HOURS:MINUTES:
SECONDS'
DATE_SUB( '2016-02-23 20:55:58', INTERVAL '5:2:1' HOUR_SECOND )
HOUR_MINUTE 'HOURS:MINUTES'
DATE_SUB( '2016-02-23 20:55:52', INTERVAL '5:2' HOUR_MINUTE )
DAY_MICROSECOND 'DAYS HOURS:
MINUTES:
SECONDS.
MICROSECONDS'
DATE_SUB( '2016-02-23 20:55:52', INTERVAL '2 5:2:24.879' DAY_MICROSECOND
DAY_SECOND 'DAYS HOURS:
MINUTES:SECONDS'
DATE_SUB( '2016-02-23 20:55:52', INTERVAL '2 5:2:24' DAY_SECOND)
DAY_MINUTE 'DAYS HOURS:
MINUTES'
DATE_SUB( '2016-02-23 20:55:52', INTERVAL '2 5:2' DAY_MINUTE)
DAY_HOUR 'DAYS HOURS'
DATE_SUB( '2016-02-23 20:55:52', INTERVAL '2 5' DAY_HOUR)
Our sample table dt_table_tm stores login date with time in a field along with one more column showing event_id.

All records of Last 5 Hours
SELECT * FROM `dt_table_tm` WHERE tm>=DATE_SUB(NOW(), INTERVAL 5 HOUR)
All records of Last 48 Hours
SELECT * FROM `dt_table_tm` WHERE tm>=DATE_SUB(NOW(), INTERVAL 48 HOUR)
All records of 15 hours 12 minutes
SELECT * FROM `dt_table_tm` WHERE tm>=DATE_SUB(NOW() , INTERVAL '15:12' HOUR_MINUTE)
In place of NOW() we can use specific date and time ( timestamp ) Note : While using date use the format YYYY-mm-dd ( YEAR - Month - date) and while using time use HH:MM:SS ( Hour : Minutes : Seconds )
SELECT * FROM  `dt_table_tm` WHERE tm >= DATE_SUB( '2018-08-10 11:50:00', INTERVAL '2:18:33' HOUR_SECOND )
All logins between 9 and 10 hours ( from 9 and 10 both inclusive till it is not 11 )
SELECT * FROM `dt_table_tm` WHERE HOUR(tm) between 9 and 10
All logins betwee 9 and 10 for a perticular month and year
SELECT * FROM `dt_table_tm` WHERE HOUR(tm) between 9 and 10 and MONTH(tm)=08 and year(tm)=2018
By Using date_format()
SELECT * FROM `dt_table_tm` WHERE HOUR(tm) between 9 and 10 and date_format(tm,'%Y-%m')='2018-08'
All logins between 11 and 18 hours on a prticular day
SELECT * FROM `dt_table_tm` WHERE HOUR(tm) between 11 and 18 and date(tm)='2018-08-09'
All logins between two input times in hour : minutes : seconds on a perticular day
SELECT * FROM `dt_table_tm` WHERE date(tm)='2018-08-09' AND  TIME(tm) BETWEEN TIME('9:00:00') AND TIME('10:15:00')
Counting logins at different hours in all days ( using GROUP BY )
SELECT hour(tm) , count(event_id)   FROM `dt_table_tm` group by HOUR(tm)
First Login ( tm ) of all days. We will use GROUP BY and MIN()
SELECT DATE(tm), MIN(tm),DATE_FORMAT(MIN(tm),'%H : %i :%s') time 
FROM `dt_table_tm` GROUP BY  DATE(tm) 
First Login of all days after a particular hour. ( HOUR(tm) >16 )
SELECT DATE(tm), MIN(tm),DATE_FORMAT(MIN(tm),'%H : %i :%s') time
 FROM `dt_table_tm`  WHERE HOUR(tm) > 16 GROUP BY  DATE(tm)  
First login of all days after a particular HOUR Minutes and seconds.
SELECT DATE(tm), MIN(tm),DATE_FORMAT(MIN(tm),'%H : %i :%s') time 
 FROM `dt_table_tm`  WHERE TIME(tm) > '16:01:00'  GROUP BY  DATE(tm) 

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.


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.

For the SQL with date and time , another table dt_table_tm is used and the dump file can be used .
SQL dump file generated based on date & time to test queries
SQL Date References Records between two dates

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    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
    Rajesh

    26-02-2016

    Hello Sir,
    i want to fetch sum of all fee which is deposite that date from database table.
    query not work:$rs=mysql_query("select sum(fee) form st where data='$date'";
    Pls. solve my query
    Subhas Ch Paramanick

    07-01-2017

    For Example 7 days leave (LAP) from 01/01/2017 upto 01/07/2017 for XYZ designation JE. In SQL Server record insert LeaveApp table as XYZ, JE, Leave, LAP, 01/01/2017, 01/07/2017 in one line. But I want the record in another table like Attendance table as in row 1 => XYZ, JE, Leave, LAP, 01/01/2017, 01/01/2017
    In row 2 => XYZ, JE, Leave, LAP, 01/02/2017, 01/02/2017 In row 3 => XYZ, JE, Leave, LAP, 01/03/2017, 01/03/2017 and so on upto Last row => XYZ, JE, Leave, LAP, 01/07/2017, 01/07/2017.
    How to create code in vb.net 2008 or vb.net c# 2008.
    Please Help me in this regards.
    Thanks.
    Arun

    03-06-2018

    Hi

    I have certain data stored in DB in milliseconds. But this was not the format I wanted so I converted it into actual date format. The format is getting updated as a new column during runtime. strftime(MillisField*1000,'%Y %m %d %H:%M:%s') as date.

    This returns the actual date and time format in a new column.

    But the problem here is if I want the last six months of some particular data, so I tried this:

    MillisField > 2017-12-01

    It didn't show me the last six months of data, but, if I try this:

    MillisField > 1512066600000

    it shows the exact result I wanted, but I want to give it in date rather than milliseconds
    smo1234

    10-08-2018

    Add one more timestamp field to the table, then on a single query update this filed by converting MillsField to required format. Once you are satisfied with the new field and gets the data in desired format , delete the MillsField
    umair

    10-09-2018

    if want to get the records between from the starting of this month date and the current date, then what will be the query/condition that time
    smo1234

    11-09-2018

    To get first day of the month use DATE_FORMAT(CURDATE() ,'%Y-%m-01'). The complete query is added to the tutorial above.

    10-03-2021

    if want to get the records past 10 days after particular 9 clock first record for each day
    please tell me query anyone

    23-03-2021

    Added this part
    First Login of all days after a particular hour.

    20-12-2021

    unbelievably useful to me as I always struggle with date format
    thank you

    26-03-2023

    Correct date time function month year week calendar world science

    Post your comments , suggestion , error , requirements etc here

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer