datetime - MySQL query to generate a punch card dataviz -

i have database chat history has datime field, author of messsage , message.

i wan´t make query returns table that, correlates weekday hours of day, counting how many messages sent, using same format github uses generate punch card graph:

[day, hour, number of messages]  [ [0,0,23], [0,1,20], [0,2,56], []...] 

i tried sql instruction, don't think working.

select      dayofweek(date_time) day,      hour(date_time) `hour`,      sum(dayofweek(date_time)) msgs  chat group day(date_time) order `day` 

the return is:

[[1,1,4734] [1,20,4503] [1,11,6510] [1,0,7058] [2,0,6518] [2,8,3913] [2,9,3885] [2,0,2305] [2,9,4471] [2,0,3703] [3,0,3315] [3,0,9600] [3,0,3910] [3,9,4956] [3,0,2692] [4,0,5225] [4,10,4815] [4,0,7667] [4,11,5249] [4,0,5121] [5,8,3148] [5,10,4947] [5,0,4109] [5,0,4775] [6,0,4970] [6,0,4342] [6,12,4488] [6,0,7191] [7,0,3790] [7,9,9294] [7,9,4749]] 

i don't know happening, return should have 168 lines, 7 * 24, i'm missing here?

thanks!

the problem grouping day only, not day , hour. instead of

select      dayofweek(date_time) day,      hour(date_time) `hour`,      sum(dayofweek(date_time)) msgs  chat group day(date_time) order `day` 

do

select      dayofweek(date_time) day,      hour(date_time) `hour`,      sum(dayofweek(date_time)) msgs  chat group dayofweek(date_time), hour(date_time) order `day`,hour