SYS_CALENDAR equivalent in Vertica

[nnani]

Hello all,

I have a TD script where in the select query fetched the data from sys_calendar

SYS_CALENDAR - The System Calendar, as implemented for Teradata, is a high performance set of nested views.The System Calendar is actually a 4-level nested view. Each level of view built on top of the table adds intelligence to the date.

I don't think Vertica has anything similar to this.
The code snippet used in TD is

Code: Select all

SELECT
        calendar_date
        ,year_of_calendar
        FROM sys_calendar.calendar
        WHERE month_of_year = 3
        AND year_of_calendar > 2010
        AND day_of_week = 1
        QUALIFY ROW_NUMBER() OVER(PARTITION BY year_of_calendar,month_of_year ORDER BY calendar_date ASC) = 2

How can I have this Vertica.

[JimKnicely]

Hi,

I wonder if something like this would work for you?

Create a view with a structure like the TD calendar:

Code: Select all

dbadmin=> CREATE OR REPLACE VIEW calendar AS
dbadmin->   SELECT ts::DATE            calendar_date,
dbadmin->          year(ts::DATE)      year_of_calendar,
dbadmin->          month(ts::DATE)     month_of_year,
dbadmin->          dayofweek(ts::DATE) day_of_week
dbadmin->     FROM (SELECT '01-01-0001'::TIMESTAMP as tm
dbadmin(>           UNION
dbadmin(>           SELECT '12-31-2500'::TIMESTAMP as tm) as t
dbadmin->     TIMESERIES ts as '1 Day' OVER (ORDER BY tm);
CREATE VIEW

You can play with the min and max dates for the calendar... I used 01/01/0001 and 12/31/2500.

Now run your select against the view:

Code: Select all

dbadmin=> SELECT calendar_date,
dbadmin->        year_of_calendar
dbadmin->   FROM (SELECT calendar_date,
dbadmin(>                year_of_calendar,
dbadmin(>                row_number() over (partition by year_of_calendar,month_of_year ORDER BY calendar_date ASC) rn
dbadmin(>           FROM calendar
dbadmin(>          WHERE month_of_year = 3
dbadmin(>            AND year_of_calendar > 2010
dbadmin(>            AND day_of_week = 1) foo
dbadmin->  WHERE rn = 2
dbadmin->  ORDER BY calendar_date
dbadmin->  LIMIT 10;
 calendar_date | year_of_calendar
---------------+------------------
 2011-03-13    |             2011
 2012-03-11    |             2012
 2013-03-10    |             2013
 2014-03-09    |             2014
 2015-03-08    |             2015
 2016-03-13    |             2016
 2017-03-12    |             2017
 2018-03-11    |             2018
 2019-03-10    |             2019
 2020-03-08    |             2020
(10 rows)

Note: Thanks to sKwa for the great example of how to get date ranges!

[nnani]

Hello Jim,

That's the best workaround for this. I created a Local Temporary table in place of views and used it in script.

Thank you so much for your quick assistance on this.

Once again Thanks to Jim and skwa

[JimKnicely]

I like the idea of creating the temp table!