Salesforce Docs Portal

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.

Syntax

generate_series(<start>, <stop>, <step>)

Arguments

Required

  • <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

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

Considerations

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.

Examples

Basic Usage with Dates

Generate a series of months between two dates.

SELECT * FROM generate_series('2017-01-01'::date, '2017-03-01'::date, '1 month'::interval);

Results:

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

Usage with Timestamps

SELECT * FROM generate_series('2017-01-01 01:02:03'::timestamp, '2017-03-01 01:02:03'::timestamp, '1 month'::interval);

Results:

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

Month-End Handling

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

SELECT * FROM generate_series('2017-01-30'::timestamp, '2017-05-30'::timestamp, '1 month'::interval);

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

Negative Step Sizes

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

SELECT *
FROM generate_series('2017-03-01'::timestamp, '2017-01-01'::timestamp, '-1 month'::interval) WITH ORDINALITY
ORDER BY ordinality ASC

Results:

generate_series ordinality
2017-03-01 00:00:00 1
2017-02-01 00:00:00 2
2017-01-01 00:00:00 3

Related Documentation