
This feature comes in handy if I need to generate a list of dates using some interval, for example 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, which I had 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/latest/HTML/Content/Authoring/AnalyzingData/TimeSeries/UsingTimeSeriesAnalytics.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/TimeSeries/TIMESERIESClauseAndAggregates.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/TimeSeries/TimeSeriesRounding.htm
Have fun!