How to Group MySQL Records by Day, Month, or Year?

MySQL group records by day, month or year

MySQL provides an efficient way to count the number of records within a specific time period by combining the GROUP BY clause with datetime functions. Here are examples of grouping by day, month or year:

Group by day:

To count the number of records per day, use the following query:

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

Group by month:

To group by month, use the following query:

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

Group by year:

To group by year, use the following query:

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

These queries effectively aggregate records based on the date and time part (day, month, year) specified in the record_date field. By using functions such as DATE(), YEAR() and MONTH() you can extract relevant datetime parts for grouping without having to deal with date calculations manually.

For more details on date and time functions, see the MySQL documentation.

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