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.
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: