generate_series (Time-based)

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Generate a series of time-based values, from start to stop with a given step size.

  • <start>: The start value of the series. Can be of type DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.
  • <stop>: The end value of the series. Must be of the same type as <start>.
  • <step>: The step size as an INTERVAL. Unlike the numerical version of generate_series, the step size is always required for time-based series.

Returns a set of rows, where each row has a single column, containing the generated series of values.

There are two versions of generate_series:

  • The numerical version
  • The time-based version

This page describes the time-based version. The numerical version is described in the generate_series (Numerical) section. In contrast to the numerical version, the step size is always required for time-based series.

Furthermore, all considerations from the generate_series (Numerical) section regarding start, stop, and step parameters apply to the time-based version, too.

Generate a series of months between two dates.

Results:

generate_series
2017-01-01
2017-02-01
2017-03-01

Results:

generate_series
2017-01-01 01:02:03
2017-02-01 01:02:03
2017-03-01 01:02:03

If the series starts near the end of a month, generate_series adjusts to the end of subsequent months.

Results:

generate_series
2017-01-30 00:00:00
2017-02-28 00:00:00
2017-03-30 00:00:00
2017-04-30 00:00:00
2017-05-30 00:00:00

For negative step sizes, the function returns the series in reverse order. start must be greater than stop in this case.

Results:

generate_seriesordinality
2017-03-01 00:00:001
2017-02-01 00:00:002
2017-01-01 00:00:003