MySQL - group datetime by time hour

In this article, we would like to show you how to group datetime by time hour in MySQL.

Quick solution:

SELECT HOUR(`datetime_column`), COUNT(*)
FROM `table_name`
GROUP BY HOUR(`datetime_column`);

Practical example

To show how to group datetime by time hour, we will use the following table:

Note:

At the end of this article you can find database preparation SQL queries.

Example

In this example, we will display the average salary.

Query:

SELECT
	HOUR(`event_datetime`) AS 'hour',
	COUNT(*) AS 'number_of_appearances'
FROM `events`
GROUP BY HOUR(`event_datetime`);

Output:

Database preparation

create_tables.sql file:

CREATE TABLE `events`(
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`event_datetime` DATETIME,
	PRIMARY KEY (`id`)
);

insert_data.sql file:

INSERT INTO `events`
	(`event_datetime`) 
VALUES 
	('2000-01-01 01:00:00'),
	('2000-01-02 02:00:00'),
	('2000-01-03 02:00:00'),
	('2000-01-04 02:00:00'),
	('2000-01-05 03:00:00'),
	('2000-01-06 03:00:00'),
	('2000-01-07 03:00:00'),
	('2000-01-08 04:00:00'),
	('2000-01-09 04:00:00'),
	('2000-01-10 05:00:00'),
	('2000-01-11 05:00:00');