Generating a datetime range

Question

I'd like to generate an array of datetime timestamps with incrementing steps of 1 hour

Example:

[2018-01-01 10:00:00, 2018-01-01 11:00:00, 2018-01-01 12:00:00]

The array functionGENERATE_DATE_ARRAY does what I want, but unfortunately it appears to work only for day/week/month, and not intra-day time granularity

Any suggestions? Thanks

Answer 1

You can use the GENERATE_ARRAY function with the ARRAY and TIMESTAMP_ADD functions to build an array with the desired range. Here is an example:

SELECT
  ARRAY(
    SELECT TIMESTAMP_ADD('2018-01-01 10:00:00', INTERVAL x HOUR)
    FROM UNNEST(GENERATE_ARRAY(0, TIMESTAMP_DIFF('2018-01-01 12:00:00', '2018-01-01 10:00:00', HOUR))) AS x
  ) AS timestamps

If you want to, you can make a SQL UDF and then call that instead:

CREATE TEMP FUNCTION MakeRange(lower TIMESTAMP, upper TIMESTAMP) AS (
  ARRAY(
    SELECT TIMESTAMP_ADD(lower, INTERVAL x HOUR)
    FROM UNNEST(GENERATE_ARRAY(0, TIMESTAMP_DIFF(upper, lower, HOUR))) AS x
  )
);
SELECT MakeRange('2018-01-01 10:00:00', '2018-01-01 12:00:00') AS timestamps