
The Vertica TIMESERIES Clause is an important component of time series analytics computation. It performs gap filling and interpolation (GFI) to generate time slices missing from the input records.
I like to use it to create a dynamic date dimension for my data warehouse.
Example:
dbadmin=> CREATE OR REPLACE VIEW date_dim AS
dbadmin-> SELECT ts::date AS the_date,
dbadmin-> TO_CHAR(ts::date, 'Day') the_day,
dbadmin-> EXTRACT('Day' FROM ts::date) the_day_of_month,
dbadmin-> EXTRACT('DOY' FROM ts::date) the_day_of_year,
dbadmin-> EXTRACT('Week' FROM ts::date) the_week_of_year,
dbadmin-> EXTRACT('Quarter' FROM ts::date) the_quarter,
dbadmin-> EXTRACT('DOQ' FROM ts::date) the_day_of_quarter
dbadmin-> FROM (SELECT '19-MAR-2018'::TIMESTAMP as tm UNION SELECT '23-MAR-2018'::TIMESTAMP as tm) as t
dbadmin-> TIMESERIES ts as '1 DAY' OVER (ORDER BY tm);
CREATE VIEW
dbadmin=> SELECT * FROM date_dim;
the_date | the_day | the_day_of_month | the_day_of_year | the_week_of_year | the_quarter | the_day_of_quarter
------------+-----------+------------------+-----------------+------------------+-------------+--------------------
2018-03-19 | Monday | 19 | 78 | 12 | 1 | 78
2018-03-20 | Tuesday | 20 | 79 | 12 | 1 | 79
2018-03-21 | Wednesday | 21 | 80 | 12 | 1 | 80
2018-03-22 | Thursday | 22 | 81 | 12 | 1 | 81
2018-03-23 | Friday | 23 | 82 | 12 | 1 | 82
(5 rows)
Have Fun!