Use Time Series Analytics to Generate a List of Dates at a Specific Start Date

Jim_Knicely · April 2019

Time series analytics evaluate the values of a given set of variables over time and group those values into a window (based on a time interval) for analysis and aggregation.

This feature comes in handy if I need to generate a list of dates using some interval, i.e. 1 MONTH.

Example:

dbadmin=> SELECT ts::date the_date
dbadmin->   FROM (SELECT '2019-01-15 00:00:00'::TIMESTAMP as tm UNION ALL SELECT '2019-12-15 00:00:00'::TIMESTAMP) as t
dbadmin-> TIMESERIES ts as '1 MONTH' OVER (ORDER BY t.tm);
  the_date
------------
2018-12-22
2019-01-21
2019-02-20
2019-03-22
2019-04-21
2019-05-21
2019-06-20
2019-07-20
2019-08-19
2019-09-18
2019-10-18
2019-11-17
(12 rows)

But why did the list start on the date 2018-12-22 and not the date 2019-01-15 I specified as the start date in the query? Because time series of months are divided into equal 30-day intervals, relative to the baseline timestamp 2000-01-01 00:00:00.

dbadmin=> SELECT (('2018-12-22'::DATE - '2000-01-01'::DATE) / 30)::NUMERIC(4, 1) "Number of 30 Day Intervals Between";
Number of 30 Day Intervals Between
------------------------------------
                              231.0
(1 row)

But what if I want my list to start on the date 2019-01-15? I can do something like this:

dbadmin=> SELECT ADD_MONTHS((EXTRACT (YEAR FROM ts::DATE) || '-' || EXTRACT(MONTH FROM ts::date) || '-15')::DATE, 1) the_date
dbadmin->   FROM (SELECT '2019-01-15 00:00:00'::TIMESTAMP as tm UNION ALL SELECT '2019-12-15 00:00:00'::TIMESTAMP) as t
dbadmin-> TIMESERIES ts as '1 MONTH' OVER (ORDER BY t.tm);
  the_date
------------
2019-01-15
2019-02-15
2019-03-15
2019-04-15
2019-05-15
2019-06-15
2019-07-15
2019-08-15
2019-09-15
2019-10-15
2019-11-15
2019-12-15
(12 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/TimeSeries/UsingTimeSeriesAnalytics.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/TimeSeries/TIMESERIESClauseAndAggregates.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/TimeSeries/TimeSeriesRounding.htm

Have fun!