You already have all your data in MySQL and for some decisions, you want to use statistics based on your data. For example, you may want to query the number of users that signed up during a given month. Or how many actions were triggered in a month.
This tutorial shows you how to filter rows with a date within a given month.
MySQL Series Overview
- Truncate a Foreign Key Constrained Table
- Add Days to a Datetime
- Find Value in Column with Comma-Separated Values
- Ideas to Debug High-Load Situations (Coming soon)
- Calculate Number of Days Between Two Dates
- Get the Last Day of the Month
- Get the First Day of the Month
- Select Rows With Date Within a Month
- Get Weekday From Date (Coming soon)
- Fixing Invalid JSON Text in Argument 1 to Function json_extract: "Invalid value." (Coming soon)
Using the MySQL DATE_FORMAT
Function
MySQL comes with dozens of useful date and time functions. One of these functions is DATE_FORMAT
allowing you to output a given date in a specified format. You can customize the format with unit specifiers. For example, a specifier for a year represented in four digits is %Y
.
Let’s say you want to find all users who signed up in August 2024. Here’s a sample query you can use to filter these rows:
SELECT *
FROM users
WHERE
DATE_FORMAT(createdon, '%Y-%m') = '2024-08'
The first argument to DATE_FORMAT
is the date input, and the second is the date specifier. You can then compare the output against your month of choice.
Notice: the DATE_FORMAT
function returns NULL
when either argument (input and specifier) is NULL
.
Using the MySQL BETWEEN
Function
Another option to filter dates within a month can be MySQL’s BETWEEN
 operator. BETWEEN
checks whether a given input is greater than or equal to a given min value and less than or equal to a given max value. The BETWEEN
comparison is inclusive and can be translated to this expression: min <= expr AND expr <= max
.
Here’s a sample query filtering all rows where a user’s createdon
timestamp is within August 2024.
SELECT *
FROM users
WHERE
createdon BETWEEN '2024-08-01' AND '2024-08-31'
You noticed that we hard-coded the min
and max
values for the BETWEEN
comparison. You may also use MySQL’s LAST_DAY
function. Here’s the rewritten SQL query filtering all users that signed up during August 2024:
SELECT *
FROM users
WHERE
createdon BETWEEN '2024-08-01' AND LAST_DAY('2024-08-01')
You might be interested in similar tutorials here on Future Studio that are related or helpful when working with dates in MySQL:
Enjoy!
Mentioned Resources
- MySQL docs for date and time functions
- MySQL docs for the
BETWEEN
 operator - Future Studio tutorial on how to get the first day of a month in MySQL
- Future Studio tutorial on how to get the last day of a month in MySQL