Count items and group by a datetime unit

Question

I'm trying to organize a query which should print a count of items ordered by a time unit such as a hour, a day etc.

My limitation is that I'm using LovefieldDB javascript library and complex queries are limited.

I have a table of Units where the main field is the date of creation like this:

2017/11/29 08:17
2017/11/29 08:47
2017/11/29 09:25
2017/11/29 11:39

The result I expect is to count per hour the number of items:

08:00 (2)
09:00 (1)
11:00 (1)

What I want to avoid is to select all rows and process them in a loop because this software is going to have thousands of rows and I'll have to create similar queries for day, month and also year.

A possible solution I thought is to add 4 more datetime fields where I would save created_hour, created_day, created_month, created_year so I can order them easly.

Do you have some tips?

Answer 1

This is what I mean. You can format the time to be in hours and group by the formatted time.

SELECT DATE_FORMAT(datecolumn, '%H:00'), COUNT(*)
FROM SOURCETABLE
GROUP BY DATE_FORMAT(datecolumn, '%H:00')