This topic is locked
[SOLVED]

 MySQL query for time chart

2/3/2021 8:23:34 PM
PHPRunner General questions
D
DealerModules authorDevClub member

I have a field called 'datepromised' that has data like:
2020/03/23 21:46

2020/06/20 22:00

2020/07/06 16:04

2020/07/06 19:55

2021/02/03 17:00

2021/02/03 17:31

2021/02/04 09:00

2021/02/04 11:30
What I am trying to achieve is to have a daily line chart of hours and above each hour would be a count of the requests.
Similar to:


I would like the baseline of my chart to start at 00, 01, 02, 03, etc through 24 and show the count above each hour.
I have the MySQL doing the query correctly for extracting the data for the current date.
Question: How do I get the empty hours to still show.

I am looking for the data to look like:
theHour | numberOfItems

00 | 0

01 | 0

02 | 1

03 | 3

04 | 1

etc. through 24.
What I am currently getting is:
theHour | numberOfItems
17 | 2 (I ran this on FEB 3, so this I only had two requests at 17:00 for this day as shown in the data example above)
The MySQL query that I am using is:
SELECT

EXTRACT(hour from datepromised) AS theHour,

COUNT(*) AS numberOfItems

FROM makeready

WHERE (DATE(datepromised) = CURDATE())

GROUP BY EXTRACT(hour from datepromised)

ORDER BY EXTRACT(hour from datepromised)
Just can not figure out how to add the "empty hours" so the chart shows the full 24 hours on the baseline. If they are empty I need them to populate with a zero.
Any direction greatly appreciated.
Paul

N
Nir Frumer 2/4/2021

hi

this is done the other way around

you create a table or cursor of all hours

then you run a query with left join to your table
hope it helps,

Sergey Kornilov admin 2/4/2021

What Nir says, you need some sort of a temporary/additional table that would contain all 24 hours. Then you LEFT JOIN the results of your query with this table and those entries that don't have a value will have NULLs in the results.

D
DealerModules authorDevClub member 2/4/2021

Thanks Nir and Sergey.

Now that you put me on the right track it makes sense now.
I appreciate your direction.

Paul