Generate Functions

Create sequences, arrays, or structured data dynamically.

GENERATE_DATE_ARRAY(<start_date/timestamp>, <end_date/timestamp> , INTERVAL <step_int> <date_part>)

Returns an array of date.

Supported date_part must be either DAY, MONTH, YEAR

The step_int parameter determines the increment used to generate dates.

> SELECT GENERATE_DATE_ARRAY( '2023-03-04', '2023-04-26' , INTERVAL 1 DAY) AS date_array
[2023-03-04, 2023-03-05, 2023-03-06]

GENERATE_TIMESTAMP_ARRAY(<start_timestamp>, <end_timestamp>, INTERVAL <step_int> <date_part>)

Returns an array of timestamp.

Supported date_part must be either DAY, HOUR, MINUTE, SECOND

The step_int parameter determines the increment used to generate timestamps.

> SELECT GENERATE_TIMESTAMP_ARRAY(to_timestamp('2016-10-05 00:00:00'), to_timestamp('2016-10-05 05:00:00'),
                                INTERVAL 1 hour) AS timestamp_array
[2016-10-05 00:00:00, 2016-10-05 01:00:00, 2016-10-05 02:00:00, 2016-10-05 03:00:00, 2016-10-05 04:00:00, 2016-10-05 05:00:00]