Vertica - Creating a calendar table

Question

I have a problem in Vertica related to filling dates that don't exist. I have seen solutions online where people have suggested to create a calendar table. Here is one such MYSQL problem from stackoverflow.

Is there a way to create a calendar table using the min() and max() available date from another table using just SQL supported by Vertica and no procedures? Most of the solutions I have come across so far are based on T-SQL and the dates are generated using a procedure. Unfortunately I Vertica doesn't really have that much of PL/SQL or T-SQL ability. But has some analytic functions which I suspect might be able to solve my problem.

Eakan Gopalakrishnan · Accepted Answer · 2013-09-24T09:28:47.700

As I mentioned in the question, I'm answering this now as I have found a solution to my problem of creating a calendar table or view using a start and end date.

CREATE table mytest.calendar
(
    date DATE primary key
);

Insert the boundary dates into the calendar table (min and max dates from the table you want ).

Insert into mytest.calendar (select min(date) from mytest.benchmarks);
Insert into mytest.calendar (select max(date) from mytest.benchmarks);

Now to generate intermediate dates do the following:

SELECT CAST(slice_time AS DATE) date
  FROM mytest.calendar mtc
  TIMESERIES slice_time as '1 day'
  OVER (ORDER BY CAST(mtc.date as TIMESTAMP));

You could use that as a table on its own:

SELECT date from
(SELECT CAST(slice_time AS DATE) date
  FROM mytest.calendar mtc
  TIMESERIES slice_time as '1 day'
  OVER (ORDER BY CAST(mtc.date as TIMESTAMP))) calendar
where mytest.isBusinessDay(date) = 't';

SELECT date
  FROM
(SELECT date
  FROM
       (SELECT CAST(slice_time AS DATE) date
          FROM mytest.calendar mtc
    TIMESERIES slice_time as '1 day'
          OVER (ORDER BY CAST(mtc.date as TIMESTAMP))
        ) calendar
WHERE mytest.isBusinessDay(date) = 't') calendar;

And I have my list of dates from start date (which is min(date) from benchmarks table) to the end date (which is the max(date))

Vertica - Creating a calendar table

1 Answers1