On-the-Fly Date Dimension

[Deleted User] · March 2018

This tip was authored by Jim Knicely.

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!