You don't want recursion, you want pivot table (Excel) behavior to reformat
rows into columns. The easiest way to get this data in its raw form would
be to group by date and hour of day and compute the count.
If you have the option to add extensions in your environment, then you
should be able to pivot your data pretty simply.
https://www.postgresql.org/docs/12/tablefunc.html