Solution query to get all rows from previous month
For example, if the current month is March and if you want results from February, use this query:
SELECT * FROM table_name WHERE YEAR(date_column_name) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date_column_name) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
We are using few MySQL functions.
MySQL YEAR() Function
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).
MySQL MONTH() Function
The MONTH() function returns the month part for a given date (a number from 1 to 12).
MySQL CURRENT_DATE() Function
The CURRENT_DATE() function returns the current date. The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric).
The syntax is this for INTERVAL:
INTERVAL expr unit
Where INTERVAL = the start of interval and it expects expr and unit as well.
expr = value of the interval you want for example 1, 2, 3..
and lastly, unit = interval unit for example, minutes, hours, days… but you have to write those units in MySQL format. For example,
Now it's quite clear why we added INTERVAL 1 MONTH in the query.
Query to get rows between Today and Last 30 Days
SELECT * FROM table_name WHERE date_column_name BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
MySQL CURDATE() Function
The CURDATE() function returns the current date. The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric). It is equals the CURRENT_DATE() function we saw earlier.
There is a slight problem.
CURDATE() returns only the DATE part from the date, so if you store date as a DATETIME with the time then the query above will not select the today's records.
So, we can use query 2 for those type of records.
SELECT * FROM table_name WHERE date_column_name BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
There are many other ways to play around in MySQL queries and get the same results. So, you might find a different query with same results actually. Post it in comments if you do. Thank you!
Credit: MySQL Docs