YogeshChauhan . com

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

in SQL/MySQL on 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 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

amazon

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Arguments in @mixin rules in SCSS (Sass)SCSSSelector Lists and Combinators in SCSS (Sass)SCSSSolution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software”MiscHow to set up the local environment and workspace for Angular development?AngularHow to swap images on hover using CSS?CSSHow to create bouncing balls using HTML canvas and JavaScript?HTML