The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Sign In or Register to comment.