PHPMySQL count with left join not providing result expected-CodePudding

I am doing an interval of 1 hour counting number of new transactions per day, the query is showing all transactions in the first row which should be split accordingly the proper hourly times. Any help will be appreciated.

SELECT
   date_generator.date as the_date,
   IFNULL(COUNT(transactions.edited), 0) as count
from (
   select DATE_ADD('2022-12-29 00:00:00', INTERVAL (@i:=@i 1)-1 HOUR) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD('2022-12-29 00:00:00',INTERVAL @i HOUR) BETWEEN '2022-12-29 00:00:00' AND '2022-12-29 23:59:59'
) date_generator
left join `transactions` on DATE(edited) = date_generator.date
AND status = 'NEW'
GROUP BY date

The currently output is below:

the_date    count   
2022-12-29 00:00:00     11
2022-12-29 01:00:00     0
2022-12-29 02:00:00     0
2022-12-29 03:00:00     0
2022-12-29 04:00:00     0
2022-12-29 05:00:00     0
2022-12-29 06:00:00     0
2022-12-29 07:00:00     0
2022-12-29 08:00:00     0
2022-12-29 09:00:00     0
2022-12-29 10:00:00     0
2022-12-29 11:00:00     0
2022-12-29 12:00:00     0
2022-12-29 13:00:00     0
2022-12-29 14:00:00     0
2022-12-29 15:00:00     0
2022-12-29 16:00:00     0
2022-12-29 17:00:00     0
2022-12-29 18:00:00     0
2022-12-29 19:00:00     0
2022-12-29 20:00:00     0
2022-12-29 21:00:00     0
2022-12-29 22:00:00     0
2022-12-29 23:00:00     0

But the correct output should be as below:

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

CodePudding user response：

DATE(edited) discards the time from edited. So it will only compare equal to a datetime whose time is 00:00:00. I think you want to compare the hour as well as the date, so it should be:

on DATE(edited) = DATE(date_generator.date) AND HOUR(edited) = HOUR(date_generator.date)