How to create an array of hours between 2 timestamps?

[Shikha0711]

Start_time: 2023-01-01 07:06:00 UTC

End_time: 2023-01-01 16:03:00 UTC

I need hours in between the two timestamps, the hours of 7am (on the 1st), 8,9,10,11,12,1,2,3,4

Appreciate any help on this!

[kolban]

SELECT ARRAY(
  SELECT EXTRACT(HOUR FROM t1) FROM UNNEST(
    (SELECT
      GENERATE_TIMESTAMP_ARRAY(
        TIMESTAMP_TRUNC(TIMESTAMP("2023-01-01 07:06:00"), HOUR),
        TIMESTAMP_TRUNC(TIMESTAMP("2023-01-01 16:03:00"), HOUR),
        INTERVAL 1 HOUR)
      )
    ) AS t1
  ) AS result