YogeshChauhan.com
MySQL queries to get data rows from previous month as well as from last 30 days
May 4, 2020

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,

MySQL Temporal Interval Expression and Unit Arguments
unit ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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

dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress? Mar 3 substring() Method in JavaScript Mar 3 Window setInterval() Method in JavaScript Mar 2 How to zoom an element on hover using CSS? Mar 2 the box-sizing property in CSS

You might also like these

How to create a secure random number using JavaScript?JavaScriptaddcslashes() and addslashes() String Functions in PHPPHPSome SQL LIKE Operators We Need to Keep in MindSQL/MySQLHow to Use SQL MAX() Function with Dates?SQL/MySQLHow to add multiple Columns in CSS like Bootstrap?CSSHow to create a cross-browser smooth scrolling with jQuery?jQuery