Weekly :

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

Monthly :

select * from dt_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Yearly :

select * from dt_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

Between Two Dates :

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.