ARRAY_RANGE

Function

Generate an arithmetic sequence array of numbers or datetimes.

• For numeric types, the default step is 1
• For datetime types, the default step is 1 day

Syntax

• ARRAY_RANGE(end)
• ARRAY_RANGE(start, end)
• ARRAY_RANGE(start, end, step)
• ARRAY_RANGE(start_dt, end_dt)
• ARRAY_RANGE(start_dt, end_dt, interval step unit)

Parameters

• start, end: non-negative integers. end is the upper bound and is excluded from the result.
• step: must be a positive integer; the step length; default is 1.
• start_dt, end_dt: DATETIME. In the two-argument form, the default step is 1 DAY.
• interval step unit: datetime step. unit can be YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|MINUTE|SECOND; step must be a positive integer.

Return value

• Returns ARRAY<T>; returns NULL for illegal arguments; returns an empty array [] for an empty range.
• The element type T matches the input: integers produce INT, datetimes produce DATETIME.

Usage notes

• Numeric sequence: start from start, increment by step, up to but excluding end (left-closed, right-open).
• Datetime sequence: start from start_dt, increment by step in the given unit, up to but excluding end_dt; the two-argument form is equivalent to interval 1 day.
• Illegal arguments return NULL:
  • Numeric: start < 0, end < 0, step <= 0.
  • Datetime: start_dt or end_dt invalid, or step <= 0.
• ARRAY_RANGE and SEQUENCE are equivalent.

Examples

• Numeric: start defaults to 0, step defaults to 1

  • ARRAY_RANGE(5) -> [0, 1, 2, 3, 4]
  • ARRAY_RANGE(0, 5) -> [0, 1, 2, 3, 4]

• Numeric: end is the upper bound and is not included in the result.

  • ARRAY_RANGE(2, 6, 2) -> [2, 4]
  • ARRAY_RANGE(3, 3) -> []

• Numeric: end must be greater than or equal to start, otherwise returns []

  • ARRAY_RANGE(3, 2) -> []

• Numeric: start, end must be non-negative integers, and step must be greater than 0.

  • ARRAY_RANGE(-1, 3) -> NULL
  • ARRAY_RANGE(1, 3, 0) -> NULL

• Datetime: step defaults to 1 day.

  • ARRAY_RANGE('2022-05-15 12:00:00', '2022-05-17 12:00:00') -> ['2022-05-15 12:00:00', '2022-05-16 12:00:00']
  • ARRAY_RANGE('2022-05-15 12:00:00', '2022-05-17 12:00:00', interval 1 day) -> ['2022-05-15 12:00:00', '2022-05-16 12:00:00']

• Datetime: unit can be YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|MINUTE|SECOND

  • ARRAY_RANGE('2022-05-15 12:00:00', '2024-05-17 12:00:00', interval 1 year) -> ["2022-05-15 12:00:00", "2023-05-15 12:00:00"]
  • ARRAY_RANGE('2022-05-15 12:00:00', '2023-05-17 12:00:00', interval 1 quarter); -> ["2022-05-15 12:00:00", "2022-08-15 12:00:00", "2022-11-15 12:00:00", "2023-02-15 12:00:00"]
  • ARRAY_RANGE('2022-05-15 12:00:00', '2022-07-17 12:00:00', interval 1 month); -> ["2022-05-15 12:00:00", "2022-06-15 12:00:00"]
  • ARRAY_RANGE('2022-05-15 12:00:00', '2022-05-17 12:00:00', interval 1 day) -> ['2022-05-15 12:00:00', '2022-05-16 12:00:00']
  • ARRAY_RANGE('2022-05-15 12:00:00', '2022-05-15 14:00:00', interval 1 hour) -> ["2022-05-15 12:00:00", "2022-05-15 13:00:00"]
  • ARRAY_RANGE('2022-05-15 12:00:00', '2022-05-15 12:02:00', interval 1 minute) -> ["2022-05-15 12:00:00", "2022-05-15 12:01:00"]
  • ARRAY_RANGE('2022-05-15 12:00:00', '2022-05-15 12:00:02', interval 1 second) -> ["2022-05-15 12:00:00", "2022-05-15 12:00:01"]