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

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, 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!