Home > OS >  UNNEST on GENERATE_TIMESTAMP_ARRAY() causes syntax error
UNNEST on GENERATE_TIMESTAMP_ARRAY() causes syntax error

Time:11-02

My goal is to use an existing table with patient_id's and corresponding dates (secifically timestamps) of entry and exit at the hospital (called min_time and max_time) to create a new table where each 4-hour bloc is a row.

enter image description here

Script:

CREATE TABLE `myproject.mydataset.newtable` AS (
SELECT 
    patient_id, 
    DATETIME(time) time
FROM `myproject.mydataset.mytable`,
    UNNEST(GENERATE_TIMESTAMP_ARRAY(CAST(min_time AS TIMESTAMP), CAST(max_time AS TIMESTAMP), INTERVAL 4 HOUR)) as time
ORDER BY patient_id, time
)

Output table:

enter image description here

  • Related