Combine 2 series of timestamps in BigQuery

Question

I'm trying to generate 2 series of timestamps with 30 minute interval like so:

interval_start,interval_end
2023-01-30 05:30:00.000000 +00:00,2023-01-30 06:00:00.000000 +00:00
2023-01-30 05:00:00.000000 +00:00,2023-01-30 05:30:00.000000 +00:00
2023-01-30 04:30:00.000000 +00:00,2023-01-30 05:00:00.000000 +00:00

I can generate each series but cannot combine them:

select *
from unnest(GENERATE_TIMESTAMP_ARRAY('2020-01-01', '2021-01-01', interval 30 minute)) start_times

select *
from unnest(GENERATE_TIMESTAMP_ARRAY(TIMESTAMP_ADD('2020-01-01', interval 30 MINUTE), '2021-01-01', interval 30 minute)) end_times

Answer 1

Consider below:

WITH intervals AS (
  select *
  from unnest(GENERATE_TIMESTAMP_ARRAY('2020-01-01', '2021-01-01', interval 30 minute)) interval_start
)
SELECT
  interval_start, TIMESTAMP_ADD(interval_start, interval 30 minute) interval_end
FROM intervals

Output: