daan.desmedt daan.desmedt - 1 month ago 8
MySQL Question

MySQL - SELECT : count results according current day with interval steps - default count value

I have a message log table, containing different types of logs.
As I'm not so keen with MySQL, I would like some advice from the more experienced MySQL profiles :)

I'm looking for a SQLQuery to COUNT the AMOUNT OF MESSAGE logs with INTERVAL of 1 HOUR, for the current day.

When there are no results for that interval, the default should be set as '0'.

Created Message
-----------------------------------------------
2016-11-07 01:42:06 "Message content here"
2016-11-07 04:10:04 "Message content here"
2016-11-07 12:10:04 "Message content here"
2016-11-07 13:45:24 "Message content here"
2016-11-07 16:22:54 "Message content here"
2016-11-07 17:11:54 "Message content here"
2016-11-07 19:23:54 "Message content here"
2016-11-07 20:15:54 "Message content here"
2016-11-07 20:51:54 "Message content here"
2016-11-07 20:51:57 "Message content here"
2016-11-07 20:51:58 "Message content here"
2016-11-07 20:51:59 "Message content here"
2016-11-07 20:52:05 "Message content here"
2016-11-07 21:02:05 "Message content here"


The expected return should be something like this, so I could visualize the result set as graph data.

2016-11-07 01:00:00 | 1
2016-11-07 02:00:00 | 0
2016-11-07 03:00:00 | 0
2016-11-07 04:00:00 | 1
2016-11-07 05:00:00 | 0
2016-11-07 06:00:00 | 0
2016-11-07 07:00:00 | 0
2016-11-07 08:00:00 | 0
2016-11-07 09:00:00 | 0
2016-11-07 10:00:00 | 0
2016-11-07 11:00:00 | 0
2016-11-07 12:00:00 | 1
2016-11-07 13:00:00 | 1
2016-11-07 14:00:00 | 0
2016-11-07 15:00:00 | 0
2016-11-07 16:00:00 | 1
2016-11-07 17:00:00 | 1
2016-11-07 18:00:00 | 0
2016-11-07 19:00:00 | 1
2016-11-07 20:00:00 | 6
2016-11-07 21:00:00 | 1
2016-11-07 22:00:00 | 0
2016-11-07 23:00:00 | 0
2016-11-07 00:00:00 | 0


Thanks!

mvf mvf
Answer

Will list only hours where some traffic appeared

SELECT
 date_format(Created, '%Y-%m-%d %H:00:00'),
 count(Message)
FROM message_log 
GROUP BY date_format(Created, '%Y-%m-%d %H:00:00');

To list also "empty" hours with zeros you can use something like that:

SET @starttime = '2015-10-10 01:00:00';
SET @endtime   = '2016-01-01 12:00:00'; 

SET @starttime_h = date_format( @starttime , '%Y-%m-%d %H:00:00');

SET @hours_count = TIMESTAMPDIFF(HOUR, @starttime, @endtime) + 1;

SELECT hours.hour, COUNT(message_log.Message) FROM
( SELECT @starttime_h + INTERVAL ( (a.n) + (b.n<<4) + (c.n<<8) + (d.n<<12)) HOUR as hour
FROM  (select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union select 13 union select 14 union select 15) a
  cross join (select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union  select 10 union select 11 union select 12 union select 13 union select 14 union select 15) b
  cross join (select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union  select 10 union select 11 union select 12 union select 13 union select 14 union select 15) c
  cross join (select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union  select 10 union select 11 union select 12 union select 13 union select 14 union select 15) d
WHERE (a.n) + (b.n<<4) + (c.n<<8) + (d.n<<12) <= @hours_count ) hours LEFT JOIN 
message_log  ON (hours.hour = date_format(message_log.Created, '%Y-%m-%d %H:00:00')) 
WHERE hours.hour BETWEEN @starttime AND @endtime 
GROUP BY hours.hour;