MySQL queries to get data rows from previous month as well as from last 30 days

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,

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

Share this post