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 

 
1generate_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.

1SELECT * 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 

 
1SELECT * 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.

1SELECT * 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.

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

Results:

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

Related Documentation 

Data 360 REST API Reference
Data 360 SQL Reference
Data 360 SQL and Tableau Hyper API
Data 360 SQL Syntax
Data Types
Scalar Functions
Aggregate Functions
Window Functions
Set Returning Functions
unnest
external
generate_series (Numerical)
generate_series (Time-based)
regexp_matches
result_scan
SQL Commands for Tableau Hyper API

DID THIS ARTICLE SOLVE YOUR ISSUE?
Let us know so we can improve!