How Can I Group MySQL Data by Year, Month, and Day?

Group time-based data in MySQL using YEAR, MONTH and DAY functions

In SQL, you can leverage the built-in YEAR(), MONTH(), and DAY() functions to group data based on time periods. This allows you to determine the number of records within a specific time interval.

Group by year

To count the number of records in a specific year, use the YEAR() function in the GROUP BY clause:

<code class="language-sql">SELECT COUNT(id)
FROM stats
WHERE YEAR(record_date) = 2009
GROUP BY YEAR(record_date);</code>

This query will count the number of records for the year 2009 and provide a single value.

Group by year and month

For monthly segmentation, you can combine the YEAR() and MONTH() functions:

<code class="language-sql">SELECT COUNT(id)
FROM stats
GROUP BY YEAR(record_date), MONTH(record_date);</code>

This query will display the count of records for each unique combination of year and month.

Group by year, month and day

You can also group by day using the DAY() function:

<code class="language-sql">SELECT COUNT(id)
FROM stats
GROUP BY YEAR(record_date), MONTH(record_date), DAY(record_date);</code>

This query will provide daily statistics.

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