15.10 - Using System Views - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

The Sys_Calendar.Calendar system view helps to extend the properties of a DATE data type column by means of a join. The columns of the view contain data only for the active calendar for the session.

The calendar dates range from 1900 to 2100 and are stored in a table in the Sys_Calendar database.

The administrator must run DIPCAL SQL and DIPSYSFNC scripts from the DIP utility to create the Sys_Calendar database and the two versions of the Calendar view.

There are two versions of the Sys_Calendar.Calendar view. One for the current release, and the version that was provided in the Teradata Database 13.10 release, which is named Sys_Calendar.Calendar_TD1310.

The current version (named Sys_Calendar.Calendar_TD_ISO_COMPATIBLE), is an internal view and can only be accessed by user DBC.

Release 13.10 Version

Note: The Release 13.10 version can only be used for the Teradata system-defined calendar. If you try to use it when the session calendar is ISO or COMPATIBLE, the returned values will not be valid because they will always be from the Teradata calendar.

The current version uses embedded services system functions to compute some column values. The Release 13.10 version computes all column values using arithmetic, which generally takes less time than computing values using UDFs.

To begin using Release 13.10 version, follow these steps:

1 Use this statement to view the definition of the Release 13.10 version:

	SHOW VIEW Sys_Calendar.Calendar_TD1310;

2 Use REPLACE to replace the definition of the current version with the definition of the Release 13.10 version.

After you redefine the current version, it no longer uses embedded services system functions to compute column values for the following columns:

  • day_of_month
  • weekday_of_month
  • month_of_quarter
  • month_of_year
  • quarter_of_year
  • year_of_calendar
  • If the view was redefined to the Release 13.10 version and you want to use the current version, you need to redefine it so that is has the definition of the current version. The current version (named Sys_Calendar.Calendar_TD_ISO_COMPATIBLE), is an internal view and has restricted access.

    Note: The current version can be used with the Teradata, ISO, and COMPATIBLE session calendars.

    To redefine the Release 13.10 version, follow these steps:

    1 Use this statement to view the definition of the Release 13.10 version:

    	SHOW VIEW Sys_Calendar.Calendar_TD_ISO_COMPATIBLE; 

    2 Use REPLACE to replace the definition of the Release 13.10 version with the definition of the current version.

    After you redefine the Release 13.10 version, the current version uses Embedded Services functions to compute column values for the following columns:

  • day_of_month
  • weekday_of_month
  • month_of_quarter
  • month_of_year
  • quarter_of_year
  • year_of_calendar
  • Privilege

    By default, the system grants the SELECT privilege on Sys_Calendar.Calendar to PUBLIC.

    View Definition

    This table lists and describes the columns of the view.

     

    View Column Name

    Description

    Data Type

    Format

    calendar_date

    The default date format.

    DATE

    YY/MM/DD

    day_of_week

    The day of the week (1-7) where Sunday = 1 and Saturday = 7.

    INTEGER

    -(10)9

    day_of_month

    The day of the month (1-31).

    INTEGER

    -(10)9

    day_of_year

    The day of the year (1-366).

    INTEGER

    -(10)9

    day_of_calendar

    The number of days since and including 01/01/1900.

    INTEGER

    -(10)9

    weekday_of_month

    The nth occurrence of the weekday in the month (1-5).

    INTEGER

    -(10)9

    week_of_month

    The nth full week of the month, first partial week is 0 (0-5).

    INTEGER

    -(10)9

    week_of_year

    The nth full week of the year, first partial week is 0 (0-53).

    INTEGER

    -(10)9

    week_of_calendar

    The nth full week of the calendar, the first partial week is 0.

    INTEGER

    -(10)9

    month_of_quarter

    The month of the quarter (1-3).

    INTEGER

    -(10)9

    month_of_year

    The month of the year (1-12).

    INTEGER

    -(10)9

    month_of_calendar

    The number of the months since and including 01/01/1900.

    INTEGER

    -(10)9

    quarter_of_year

    The quarter of the year. 1 (Jan/Feb/Mar) - 4 (Oct/Nov/Dec).

    INTEGER

    -(10)9

    quarter_of_calendar

    The number of quarters since and including Q1/1900.

    INTEGER

    -(10)9

    year_of_calendar

    The number of calendar years in 4 digit format.

    INTEGER

    -(10)9

    Example  

    You are encouraged to define views on the Calendar system view because of its convenience.

    A useful view to define on Calendar is Today:

    CREATE VIEW Today AS (
    SELECT * FROM Sys_Calendar.Calendar
       WHERE Sys_Calendar.Calendar.calendar_date = DATE
       );

    The Calendar system view permits easy specification of arithmetic expressions and aggregation. This is particularly useful in online analytical processing environments where requests commonly aggregate values by weeks, months, year-to-date, years, and so on. The following is an example.

    What are the dollar sales for this week, last week, and the same weeks last year for all items in the sportswear department for women?

    SELECT a2.week_of_calendar, SUM(a1.price)
       FROM Sales a1, CALENDAR a2, Item a3, Department a4, Today a5
       WHERE a1.calendar_date=a2.calendar_date
       AND (a2.week_of_calendar=a5.week_of_calendar
       OR a2.week_of_calendar=a5.week_of_calendar - 1
       OR a2.week_of_calendar=a5.week_of_calendar - 52
       OR a2.week_of_calendar=a5.week_of_calendar - 53
       )
       AND a1.itemID=a3.itemID
       AND a3.classID=a4.classID
       AND a4.classDesc='Women’s Sportswear'
       GROUP BY a2.week_of_calendar
       ORDER BY a2.week_of_calendar;