I have such table in BigQuery:

I need to create rows for each minute incuded in time range between start_timestamp and end_timestamp.

expected result:

How can I do this with BigQuery?

example input data:

WITH
  example_table AS (
  SELECT
    1 AS id,
    TIMESTAMP('2021-02-05 21:45:57') AS start_timestamp,
    TIMESTAMP('2021-02-05 21:48:17') AS end_timestamp
  UNION ALL
  SELECT
    2 AS id,
    TIMESTAMP('2021-02-05 23:12:01') AS start_timestamp,
    TIMESTAMP('2021-02-05 23:13:42') AS end_timestamp)
SELECT * FROM example_table;

Thanks for any help.

CodePudding user response：

Try this

WITH
  example_table AS (
  SELECT
    1 AS id,
    TIMESTAMP('2021-02-05 21:45:57') AS start_timestamp,
    TIMESTAMP('2021-02-05 21:48:17') AS end_timestamp
  UNION ALL
  SELECT
    2 AS id,
    TIMESTAMP('2021-02-05 23:12:01') AS start_timestamp,
    TIMESTAMP('2021-02-05 23:13:42') AS end_timestamp ),
  cte AS (
  SELECT
    id,
    TIMESTAMP_TRUNC(start_timestamp,minute) AS start_timestamp,
    rn,
    DATE_ADD(TIMESTAMP_TRUNC(start_timestamp,minute), INTERVAL rn minute) AS end_time
  FROM
    example_table,
    UNNEST (
            GENERATE_ARRAY(
                            1,
                            DATE_DIFF( TIMESTAMP_ADD(end_timestamp, INTERVAL 1 minute),
                            TIMESTAMP_TRUNC(start_timestamp,minute), minute)
            )
    ) AS rn 
)
SELECT
  id,
  ifnull(LAG(end_time) OVER (PARTITION BY id ORDER BY end_time),
    start_timestamp) AS start_time,
  end_time
FROM
  cte

OUTPUT

CodePudding user response：

Consider below approach

select id, 
  ts as start_timestamp, 
  ts   interval 1 minute as end_timestamp
from example_table,
unnest(generate_timestamp_array(
  timestamp_trunc(start_timestamp, minute), 
  timestamp_trunc(end_timestamp, minute), 
  interval 1 minute
) ) ts         

if applied to sample data in your question - output is