How to Group By Hour in MySQL

Grouping data by hour in MySQL involves extracting the hour part from a datetime or timestamp column and using it in a GROUP BY clause. The technique is common in time series analysis for aggregating data into hourly intervals.

Understanding the HOUR function

MySQL’s HOUR() function extracts the hour from a time value, which makes it the basis for grouping data by hour. The basic syntax looks like this:

SELECT HOUR(time_column) AS hour
FROM your_table;

Simple hourly grouping

To group records by hour, use the HOUR() function within the GROUP BY clause. This approach aggregates data for each hour of the day (0-23).

SELECT HOUR(datetime_column) AS hour, COUNT(*)
FROM your_table
GROUP BY HOUR(datetime_column);

Grouping by date and hour

When dealing with timestamps, you often need to group by both the date and the hour to avoid mixing data from different days.

SELECT DATE(datetime_column) AS date, HOUR(datetime_column) AS hour, COUNT(*)
FROM your_table
GROUP BY DATE(datetime_column), HOUR(datetime_column);

Handling time zones

If your data involves different time zones, convert the datetime to a consistent time zone before grouping.

SELECT CONVERT_TZ(datetime_column, '+00:00', your_timezone) AS converted_time
FROM your_table;

Then, use converted_time for grouping.

Aggregating results

Combine the GROUP BY clause with aggregate functions like SUM(), AVG(), or COUNT() to summarize data.

SELECT HOUR(datetime_column) AS hour, SUM(value_column)
FROM your_table
GROUP BY HOUR(datetime_column);

Advanced usage with date ranges

To filter within a specific date range, combine WHERE with GROUP BY.

SELECT HOUR(datetime_column) AS hour, COUNT(*)
FROM your_table
WHERE datetime_column BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY HOUR(datetime_column);

Visualizing results

If this query pattern is part of recurring reporting, Basedash helps you turn it into reusable, AI-native BI workflows: prompt-to-SQL, shared dashboards, and trusted answers that stay aligned with your data model.