How to view hourly results during the time specified in Postgres

I want to create a query that returns a list of timezones as a result when the following conditions are specified.

select day, hour
...
where
  target_datetime between '2021-08-01 00:00:00' and '2021-08-01 23:59:59'

[
  { 'day': '2021-08-01', 'hour': 0 },
  { 'day': '2021-08-01', 'hour': 1 },
  { 'day': '2021-08-01', 'hour': 2 },
...
  { 'day': '2021-08-01', 'hour': 23 }
]

How can I get this?

Answer

Use generate_series to create the records from the time interval, and jsonb_build_objebt with jsonb_agg to create your json document:

SELECT 
  jsonb_agg(
    jsonb_build_object(
      'day',tm::date,
      'hour',EXTRACT(HOUR FROM tm)))
FROM generate_series('2021-08-01 00:00:00'::timestamp, 
                     '2021-08-01 23:59:59'::timestamp,
                      interval '1 hour') j (tm);
      

Demo: db<>fiddle