Create a by Date and hour breakdown from TIMESTAMP field MYSQL

Question

I'm trying to create a query that provides a breakdown of ids by date and hour which shows zeroes for blank results.

Here's MYSQL query so far:

SELECT DATE(received),
  CONCAT(hourly.hour, ':00 - ', hourly.hour+1, ':00') AS Hours,  
  COALESCE(COUNT(id),0) AS "Leads"
FROM hourly
LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received)
WHERE 
  digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR) AND
  digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR) 
GROUP BY DATE(received), hourly.hour
ORDER BY DATE(received)

I have a table of integers from 0 - 23 that make up the hourly table.

At the moment, this query only shows the timeslots where results exist - like so :

DATE(received)       Hours  Leads
2014-11-12           11:00 - 12:00  23
2014-11-12           12:00 - 13:00  19
2014-11-12           13:00 - 14:00  18
2014-11-12           14:00 - 15:00  17
2014-11-12           15:00 - 16:00  23
2014-11-12           16:00 - 17:00  13
2014-11-12           17:00 - 18:00  17

The query should show zero results for the timeslots from 00:00 - 11:00 rather than missing them out from the query result. It seems to be ignoring the COALESCE function, which I've tried substituting with IFNULL(COUNT(id), 0) with no difference.

I've tried the solution in this question/answer - STACKOVERFLOW LINK

However, this doesn't work for me.

Any suggestions?

EDIT

Here's an SQL FIDDLE for further details

left join with where makes it an inner join. You may move the where condition as joining condition. — Abhik Chakraborty, Dec 01 '14 at 13:05
`LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received) and digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR) and digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR)` also remove the `where` clause — Abhik Chakraborty, Dec 01 '14 at 13:37
@AbhikChakraborty - Removing the `WHERE` clause and replacing with your suggested statement produces the same result. Any further suggestions? — aphextwix, Dec 01 '14 at 13:41
hmm could yo u please create a sqlfiddle with some sample data so that we could have a look ? — Abhik Chakraborty, Dec 01 '14 at 13:59
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/65968/discussion-between-abhik-chakraborty-and-aphextwix). — Abhik Chakraborty, Dec 01 '14 at 14:46
This is getting more complicated the dates are derived from one column and the hours are generated using a different table. However its easy to get all the missing hours but need to look how to get the dates http://sqlfiddle.com/#!2/f1a64/14 may need sometime. — Abhik Chakraborty, Dec 01 '14 at 14:50
Ok - I've joined the chat and I'll keep it open if you want to discuss further. thanks — aphextwix, Dec 01 '14 at 14:55

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

Ok this took some time. This is what I could best come up with. May be there is a better way to do it but the missing date or time while joining is always complicated. I have given few answers related to missing dates but this is completely different.

So using the same idea I did some changes within the query to retrieve the data as below

select
t1.date_received,
t1.Hours,
COALESCE(t2.`Leads`,0) as `Leads`
from
(
  select
  distinct DATE(received) as date_received,
  concat (DATE(received),'-',h.hour) as date_hour,
  CONCAT(h.hour, ':00 - ', h.hour+1, ':00') AS Hours 
  from digital_lead
  cross join (select hour from hourly)h
)t1
left join
(
  SELECT DATE(received) as date_received,
  concat (DATE(received),'-',hour) as date_hour,
  CONCAT(hourly.hour, ':00 - ', hourly.hour+1, ':00') AS Hours,  
  COALESCE(COUNT(id),0) AS "Leads"
  FROM hourly
  LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received)
  and digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR)
  and digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR) 
  GROUP BY DATE(received), hourly.hour
)t2
on t1.date_hour = t2.date_hour
order by t1.date_received,
cast(substring_index(t1.Hours,':',1) as unsigned)

FIDDLE

edited May 23 '17 at 12:05

Community

1
1

answered Dec 01 '14 at 17:06

Abhik Chakraborty

44,654
6
52
63

That's extremely complex - it'll take some time to digest. Much appreciation for your help on this problem. Thanks. – aphextwix Dec 01 '14 at 19:30
Yeah its a bit complex but not so much.. The idea is to first get the distict dates and then cross join with hours by concating date and hour. Then use your current query and do a left with it so that all the dates-hour from left table is shown and the count. – Abhik Chakraborty Dec 02 '14 at 07:50
I've just noticed that the dates without any data are not present in the results, so for example, the dates between `2014-11-01 - 2014-11-11` are not shown in the results. Would it be possible to show zeroes for the days without any data? Perhaps join with a calendar table with a the date range already generated? – aphextwix Dec 02 '14 at 08:33
Yes this could be done but will make it more complicated,now we will need to generate the dynamic dates depending on the range . Here it is http://sqlfiddle.com/#!2/715cee7/30 – Abhik Chakraborty Dec 02 '14 at 09:02
Thanks again! I've made some minor alterations to your answer. – aphextwix Dec 02 '14 at 09:42

Create a by Date and hour breakdown from TIMESTAMP field MYSQL

1 Answers1

Linked