generate_series

generate_series (Integers)

Generate a series of integer values.

SELECT * FROM TABLE(
    generate_series(
        <series_start>,
        <series_end>
        [, <increment>]
    )

Input Arguments

Parameter	Description	Data Types
<series_start>	Starting integer value, inclusive.	BIGINT
<series_end>	Ending integer value, inclusive.	BIGINT
<series_step> (optional, defaults to 1)	Increment to increase or decrease and values that follow. Integer.	BIGINT

Output Columns

Name	Description	Data Types
generate_series	The integer series specified by the input arguments.	Column<BIGINT>

Example

heavysql> select * from table(generate_series(2, 10, 2)); 
series 
2 
4 
6 
8 
10 
5 rows returned.

heavysql> select * from table(generate_series(8, -4, -3)); 
series 
8 
5 
2 
-1 
-4
5 rows returned.

generate_series (Timestamps)

Generate a series of timestamp values from start_timestamp to end_timestamp .

SELECT * FROM TABLE(
    generate_series(
        <series_start>,
        <series_end>,
        <series_step>
    )
)

Input Arguments

Parameter	Description	Data Types
series_start	Starting timestamp value, inclusive.	TIMESTAMP(9) (Timestamp literals with other precisions will be auto-casted to TIMESTAMP(9) )
series_end	Ending timestamp value, inclusive.	TIMESTAMP(9) (Timestamp literals with other precisions will be auto-casted to TIMESTAMP(9) )
series_step	Time/Date interval signifying step between each element in the returned series.	INTERVAL

Output Columns

Name	Description	Output Types
generate_series	The timestamp series specified by the input arguments.	COLUMN<TIMESTAMP(9)>

Example

SELECT
  generate_series AS ts
FROM
  TABLE(
    generate_series(
      TIMESTAMP(0) '2021-01-01 00:00:00',
      TIMESTAMP(0) '2021-09-04 00:00:00',
      INTERVAL '1' MONTH
    )
  )
  ORDER BY ts;
  
ts
2021-01-01 00:00:00.000000000
2021-02-01 00:00:00.000000000
2021-03-01 00:00:00.000000000
2021-04-01 00:00:00.000000000
2021-05-01 00:00:00.000000000
2021-06-01 00:00:00.000000000
2021-07-01 00:00:00.000000000
2021-08-01 00:00:00.000000000
2021-09-01 00:00:00.000000000