Getting the recent one month or year records using MySQL
by Rekha[ Edit ] 2009-10-29 14:33:00
Sometimes we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table.
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 common examples:
select * from table where dt >= DATE_SUB(CURDATE(), INTERVAL 15 DAY)
The above query will return last 15 days record.
select * from table where dt >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
The above query will return records added in last one month.
select * from table where dt >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
The above query will return records added in last one year.
select * from table where dt BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH )
The above query will return records between 6 month and 12 month.
