tuemar29
tuemar29

Reputation: 248

Combine 2 series of timestamps in BigQuery

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

Upvotes: 0

Views: 68

Answers (1)

Damião Martins
Damião Martins

Reputation: 1849

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:

enter image description here

Upvotes: 1

Related Questions