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)
Discussion:
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).
INTERVAL
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,
unit Value |
Expected expr Format |
---|---|
MICROSECOND |
MICROSECONDS |
SECOND |
SECONDS |
MINUTE |
MINUTES |
HOUR |
HOURS |
DAY |
DAYS |
WEEK |
WEEKS |
MONTH |
MONTHS |
QUARTER |
QUARTERS |
YEAR |
YEARS |
SECOND_MICROSECOND |
'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
'MINUTES:SECONDS' |
HOUR_MICROSECOND |
'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
'HOURS:MINUTES' |
DAY_MICROSECOND |
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
DAY_SECOND |
'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
'DAYS HOURS:MINUTES' |
DAY_HOUR |
'DAYS HOURS' |
YEAR_MONTH |
'YEARS-MONTHS' |
Now it's quite clear why we added INTERVAL 1 MONTH in the query.
Query to get rows between Today and Last 30 Days
Query 1:
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.
Query 2:
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
database date functions sql query