Today, we’re going to explore a simple query to see how many records your database creates each day. Perfect for getting a quick snapshot of what’s happening in your data.
What You’ll Learn
- How to group records by day using
DATE()(and why notDAYOFMONTH()) - How to filter records by a specific date range
- How to count the number of records per day
- How to interpret the results for real-world business logic
Now that we know what we’re going to do, let’s get started!
1. Understanding the Data and Scenario
Imagine you have a table named records, where each row represents a transaction or event, with a date field (of type DATE or DATETIME) indicating when it occurred.
Your goal: Count how many records were created each day within a given time frame.
2. The Query
Let’s say our time frame is 2 days. Here’s the query that helps us achieve our goal:
SELECT DATE(`date`) AS record_date,
COUNT(*) AS records
FROM records
WHERE `date` >= '2024-09-17'
AND `date` < '2024-09-19'
GROUP BY DATE(`date`);Before we dive into the query itself, it’s important to understand why we aren’t using DAYOFMONTH() when grouping the records by day. Using this function might seem like an easy way to extract the day but, it can actually cause problems that can lead to incorrect results when your date range spans multiple months, let’s see why:
Example Problem
Suppose you want to analyze data from September 15 to October 20. Using DAYOFMONTH(date) to group the data will combine counts for all days numbered “15” from both months because that’s what the function does, it extracts only the day number (from 1 to 31), ignoring the month and year, mixing different dates together.
The Solution: Use DATE(yourdate) Instead
Using DATE(date) extracts the full date (year, month, and day), so each date remains unique in your grouping, ensuring accurate daily counts.
With that said, let’s continue by understanding now what our query solution does:
3. Breaking It Down
Grouping by Day with DATE(yourdate)
- The
DATE()function extracts the full date (year, month, and day) from aDATETIMEorDATEcolumn. - Grouping by
DATE(date)ensures each calendar day is treated separately, even if your query spans across multiple months.
Filtering by Date Range
WHERE `date` >= '2024-09-17'
AND `date` < '2024-09-19'- This retrieves all records between September 17 and 18, 2024 (inclusive of the 17th, exclusive of the 19th).
- Using a half-open interval (
>= startand< end) ensures you include all data from the start date while avoiding time-based issues withDATETIMEfields at the end of the range.
Counting records per day with COUNT(*)
COUNT(*)totals the number of rows (records) in each group giving you the number of records created on each specific date.
4. Sample Output
Here’s what the output of our query will look like:
| record_date | records |
|---|---|
| 2024-09-17 | 120 |
| 2024-09-18 | 95 |
This means 120 records were created on September 17, and 95 on the 18th.
5. Making It Dynamic: Count Records for the Last N Days
To analyze the last N days, you can use MySQL date functions to create a dynamic date range, so you avoid hardcoding dates:
SELECT DATE(`date`) AS record_date,
COUNT(*) AS records
FROM records
WHERE `date` >= CURDATE() - INTERVAL 7 DAY
AND `date` < CURDATE()
GROUP BY DATE(`date`);This new version of our query counts records created each day over the last 7 days, making it perfect for automated reports or dashboards.
Try adjusting the INTERVAL value to fit your own needs and see how your data trends over different time spans!
6. Applying This in Business Logic
Use this query to automatically track daily data for examples like:
- Sales monitoring — daily transactions or activity logs
- User activity — sign-ups or logins per day
- Error/event logs — spotting spikes or drops in system events
Final Thoughts
Counting records per day is one of the simplest yet most powerful ways to gain insights from your data.
📥 Download the example script here
If you liked this article check out this 👉 Counting Daily Records in MySQL – A Step-by-Step Guide