EN
MySQL - group datetime by time hour
0 points
In this article, we would like to show you how to group datetime by time hour in MySQL.
Quick solution:
xxxxxxxxxx1
SELECT HOUR(`datetime_column`), COUNT(*)2
FROM `table_name`3
GROUP BY HOUR(`datetime_column`);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.
In this example, we will display the average salary.
Query:
xxxxxxxxxx1
SELECT2
HOUR(`event_datetime`) AS 'hour',3
COUNT(*) AS 'number_of_appearances'4
FROM `events`5
GROUP BY HOUR(`event_datetime`);Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `events`(2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`event_datetime` DATETIME,4
PRIMARY KEY (`id`)5
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `events`2
(`event_datetime`) 3
VALUES 4
('2000-01-01 01:00:00'),5
('2000-01-02 02:00:00'),6
('2000-01-03 02:00:00'),7
('2000-01-04 02:00:00'),8
('2000-01-05 03:00:00'),9
('2000-01-06 03:00:00'),10
('2000-01-07 03:00:00'),11
('2000-01-08 04:00:00'),12
('2000-01-09 04:00:00'),13
('2000-01-10 05:00:00'),14
('2000-01-11 05:00:00');