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
