Languages
[Edit]
EN

MySQL - group datetime by time hour

0 points
Created by:
Admin Dirask Community
1720

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:

MySQL - example data used to group datetime by time hour
MySQL - example data used to group datetime by time hour

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:

MySQL -  select hour from datetime and group - result
MySQL - group datetime by time hour - result

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');

MySQL - dates

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join