How Can I Group MySQL Data by Time Intervals (Year, Month, Day)?

MySQL Time Interval Grouping: A Practical Guide

Data analysis often requires organizing information by time intervals. MySQL provides efficient functions for grouping data by year, month, or day.

Imagine a table named stats with a record_date column storing timestamps. To determine the record count for each year, month, or day, utilize the GROUP BY clause along with relevant date/time functions.

For instance, to count records per year:

SELECT COUNT(id), YEAR(record_date) AS Year
FROM stats
WHERE record_date >= '2009-01-01' AND record_date < '2010-01-01'
GROUP BY Year;

This query filters records within the 2009 timeframe and groups them by year using the YEAR() function. The Year alias enhances readability.

To count records by year and month:

SELECT COUNT(id), YEAR(record_date) AS Year, MONTH(record_date) AS Month
FROM stats
GROUP BY Year, Month;

Here, YEAR() and MONTH() extract the year and month, respectively, providing counts for each unique year-month combination.

By employing appropriate date and time functions within the GROUP BY clause, you can effectively aggregate data across various time intervals, facilitating the analysis of temporal trends and patterns within your dataset.

The above is the detailed content of How Can I Group MySQL Data by Time Intervals (Year, Month, Day)?. For more information, please follow other related articles on the PHP Chinese website!