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?

You can format the datetime column to show the hour and group based on that. — clinomaniac
– clinomaniac, Commented Nov 29, 2017 at 0:19

clinomaniac · Accepted Answer · 2017-11-29 00:28:39Z

0

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

answered Nov 29, 2017 at 0:28

clinomaniac

2,2182 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chriss Over a year ago

Unfortunately Lovefield doesnt provide a way to write queries manually. And there is no DATE_FORMAT func.

Collectives™ on Stack Overflow

Count items and group by a datetime unit

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related