Lomse Lomse - 5 months ago 8
MySQL Question

Get hourly data with gaps from midnight till now

I'm using the code below in order to generate data from midnight till now.

SELECT CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours, IFNULL(COUNT(product_id), 0) AS `total_count`
FROM clicks
RIGHT JOIN (
SELECT 0 AS Hour
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
UNION ALL SELECT 22 UNION ALL SELECT 23
) AS AllHours ON HOUR(clicked_at) = Hour
WHERE ( clicked_at BETWEEN CURRENT_DATE() AND NOW() OR clicked_at IS NULL ) AND clicks.site='awesome-site.com'
GROUP BY Hour
ORDER BY Hour


I need the code to return something like

Hours total_count
----------------------
0:00-1:00 19
1:00-2:00 2
2:00-3:00 0
3:00-4:00 0
4:00-5:00 0
5:00-6:00 1
6:00-7:00 0
7:00-8:00 0
8:00-9:00 0
9:00-10:00 4
10:00-11:00 2
11:00-12:00 0
12:00-13:00 17
13:00-14:00 1


The issue is that the query above is return is returning data with gap in the Hours column; something like:

Hours total_count
----------------------
0:00-1:00 19
1:00-2:00 2
5:00-6:00 1
9:00-10:00 4
10:00-11:00 2
12:00-13:00 17
13:00-14:00 1


Thanks for the help.

Answer

right join is the correct approach, but you are using columns from clicks table in the where statement. Instead put the filter in on:

SELECT CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours, IFNULL(COUNT(product_id), 0) AS `total_count`
FROM     clicks
  RIGHT JOIN (
               SELECT  0 AS Hour
         UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3
         UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6
         UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
  )      AS AllHours ON HOUR(clicked_at) = Hour
  and   ( clicked_at BETWEEN CURRENT_DATE() AND NOW()  OR clicked_at IS NULL ) AND clicks.site='awesome-site.com'
GROUP BY Hour
ORDER BY Hour