Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

On-the-Fly Date Dimension

SarahLSarahL Administrator
edited March 2018 in Tips from the Team

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.