CODEWITHSUNDEEP
mysql
Lenny Markus
Lenny Markus

Reputation: 3418

MySQL Query for obtaining count per hour

I need to obtain a count of how many actions occur on an hourly basis.

My database keeps a log by timestamp of the actions.

I understand that I could do a 

SELECT table.time COUNT(table.time) from table t group by t.time

However, there are periods of time where no actions take place. For example if I have 10 actions during 8:00AM, no actions during 9:00AM and 4 actions during 10:00AM,

That query would return:

8:00    10
10:00   4

Skipping 9:00AM because it has no entries.

How can I make a query that will take into account 0-count entries.

I also need to make the same query for entries by days of the week, but I assume that by answering the first question I can easily handle the other.

Thanks in advance!

Upvotes: 5

Views: 10150

Answers (3)

Lummo
Lummo

Reputation: 99

You can use Valentin's solution but without the need to create a table of time slots. The idea is to generate the time slots on the fly and then JOIN them to your table as he suggests.

WITH RECURSIVE timeSlots (t) AS (
    SELECT 0
    UNION ALL
    SELECT t + 3600 FROM timeSlots WHERE t < (23 * 3600)
    )
SELECT t, TIME_FORMAT(SEC_TO_TIME(t), '%H:%i:%s') FROM timeSlots;

Gives:

+-------+----------+
| t     | Time     |
+-------+----------+
| 0     | 00:00:00 |
| 3600  | 01:00:00 |
| 7200  | 02:00:00 |
| 10800 | 03:00:00 |
| 14400 | 04:00:00 |
| 18000 | 05:00:00 |
| 21600 | 06:00:00 |
| 25200 | 07:00:00 |
| 28800 | 08:00:00 |
| 32400 | 09:00:00 |
| 36000 | 10:00:00 |
| 39600 | 11:00:00 |
| 43200 | 12:00:00 |
| 46800 | 13:00:00 |
| 50400 | 14:00:00 |
| 54000 | 15:00:00 |
| 57600 | 16:00:00 |
| 61200 | 17:00:00 |
| 64800 | 18:00:00 |
| 68400 | 19:00:00 |
| 72000 | 20:00:00 |
| 75600 | 21:00:00 |
| 79200 | 22:00:00 |
| 82800 | 23:00:00 |
+-------+----------+

If you want to change your time slot buckets you can just fiddle with the generator arithmetic rather than having to create another table.

An alternative is to use LIMIT:

WITH RECURSIVE timeSlots (t) AS (
    SELECT 0
    UNION ALL
    SELECT t + 3600 FROM timeSlots LIMIT 24
    )
SELECT t, TIME_FORMAT(SEC_TO_TIME(t), '%H:%i:%s') AS Time FROM timeSlots;

Upvotes: 0

dina
dina

Reputation: 1072

A simple way to do the same without creating any table would be as follows

SELECT 
HOUR(time) 'hr', COUNT(DISTINCT id) 
FROM schema.table 
WHERE time BETWEEN '2016-01-23 00:00:00' AND '2016-01-24 00:00:00'
GROUP BY hr;

Hour function in mysql gives the hour from a datetime or timestamp data type. This query is grouping them based on particular hour withing the date range. Distinct is not mandatory but if you are looking for unique order or id in the time range per hour. This is the query.

Upvotes: 2

Valentin Kuzub
Valentin Kuzub

Reputation: 12073

you can solve this by creating a table that will contain 24 values for hours (00:00, 01:00 etc) and perform a left (or right) join with it and your table allowing nulls so you will have all 24 rows even if your table contains 0 rows at all, then group by should work fine.

Dont forget to truncate everything but hour from your table when you perform join so result of func you call & perform join on can be equal to value of this help table.

you can use following query to do the job after populating testtime table with 24 test_time values

select test_time,sum(sign(coalesce(idFromYourTable,0))) as count from testtime 
left join yourTable on test_time=hour(yourTableTime) 
group by test_time

This will provide 0 as count if there are no values matching row from test table, while having count(*) will provide 24 rows with 1s instead of 0s even if your table is empty, also if there is just 1 row in your table it is impossible to distinguish the difference between 0 rows cause results will look the same for following 2 different rows

23 NULL
23 1

cause will both provide same result row count equal to 1 , while sum technique treats this rows differently

Upvotes: 4

Related Questions