SYS_CALENDAR equivalent in Vertica

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

SYS_CALENDAR equivalent in Vertica

Post by nnani » Thu Sep 12, 2013 12:36 pm

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.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: SYS_CALENDAR equivalent in Vertica

Post by JimKnicely » Thu Sep 12, 2013 12:57 pm

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: SYS_CALENDAR equivalent in Vertica

Post by nnani » Thu Sep 12, 2013 1:48 pm

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 :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: SYS_CALENDAR equivalent in Vertica

Post by JimKnicely » Thu Sep 12, 2013 3:58 pm

I like the idea of creating the temp table!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Database Development”