MySQL query to get invoice per hour

Question

I need to get a count of the number of activities on an hourly basis.

My database stores the log by timestamp.

I understand what I could do

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

However, there are periods of time when no action occurs. For example, if I have 10 actions during 8:00 in the morning, no actions during 9:00 and 4 actions during 10:00 in the morning,

The request will be returned:

8:00    10
10:00   4

Pass 9:00, because he has no entries.

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

I also need to make the same request for entries by day of the week, but I believe that by answering the first question, I can easily handle another.

Thanks in advance!

Answer 1

, , 24 (00:00, 01:00 ..) ( ) , , 24 , 0 , .

, , , , func, , join on .

testtime 24 test_time

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

0 , , , count (*) 24 1s 0s, , 1 0 , .

23 NULL
23 1

, 1, sum -

Answer 2

-

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;

mysql . . , . .