MySQL: query to get all rows from the previous month

Question

I need to select all rows in my database created last month.

For example, if the current month is January, then I want to return all the lines that were created in December, if the month is February, then I want to return all the lines created in January. I have a date_created column in my database that contains the date created in this format: 2007-06-05 14:50:17 .

Answer 1

 SELECT * FROM table WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) 

Answer 2

Here is another alternative. Assuming you have an indexed field of type DATE or DATETIME , this should use an index, since formatted dates will be converted to a type before using the index. Then you should see a range query, not an index query when looking at EXPLAIN .

 SELECT * FROM table WHERE date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' ) AND date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' ) 

Answer 3

If there are no future dates ...

 SELECT * FROM table_name WHERE date_created > (NOW() - INTERVAL 1 MONTH); 

Tested.

Answer 4

Alternative hobodave answer

 SELECT * FROM table WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) 

You can achieve the same result with EXTRACT by using YEAR_MONTH as a unit, so you don't need AND, for example:

 SELECT * FROM table WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL 1 MONTH) 

Answer 5

Here is a request to get records for the last month:

 SELECT * FROM `tablename` WHERE `datefiled` BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1 MONTH ) AND LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1 MONTH ) ) 

Relationships - saqib

Answer 6

select fields FROM table WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');

this one will be able to take advantage of the index if index_created is indexed since it does not apply any conversion function to the field value.

Answer 7

Although the answer to this question has already been selected, however, I believe that the simplest request will be

 SELECT * FROM table WHERE date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE(); 

Answer 8

 WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY) 

This worked for me (selects all records created in the last month, regardless of the day on which you launched the query this month)

Answer 9

select * From the table where DATE_FORMAT (date_created, '% Y-% m') = date_format (DATE_SUB (curdate (), INTERVAL 1 month), '% Y-% m')