Oracle8i Time Series User's Guide
Release 8.1.5

A67294-01

Library

Product

Contents

Index

Prev Next

4
Calendar Functions: Reference

The Oracle8i Time Series library consists of:

Calendar functions are mainly used by product developers, such as ISVs, to develop new time series functions and to administer and modify calendars.

Time series and time scaling functions and the administrative tools procedures are used mainly by application developers.

Syntax notes:


CombineCals

Format

ORDSYS.Calendar.CombineCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar,

[startDate DATE,

endDate DATE,]

equalFlag OUT INTEGER

) RETURN ORDSYS.ORDTCalendar;

Description

Combines two calendars. The CombineCals function is provided primarily for use in developing functions that operate on two time series (such as the TSAdd function).

Parameters

cal1

The first calendar to be combined.

cal2

The second calendar to be combined.

startDate

Starting date for the resulting calendar. If startDate is not specified, the starting date is the starting date for the calendars, or the higher (later) of the starting dates if they are different.

endDate

Ending date for the resulting calendar. If endDate is not specified, the ending date is the ending date for the calendars, or the lower (earlier) of the ending dates if they are different.

equalFlag

Contains 1 if the input calendars are equal, and 0 if the input calendars are not equal.

Usage

If the frequencies of the two calendars are not equal, the function returns NULL.

If the aligned patterns of the two calendars are not equal, the function returns NULL.

If startDate is not specified, the starting date of the resulting calendar is the later of the starting dates of the two calendars, that is, resulting minDate = max(minDate1, minDate2).

If endDate is not specified, the ending date of the resulting calendar is the earlier of the ending dates of the two calendars, that is, resulting maxDate = min(maxDate1, maxDate2).

The function intersects the on-exception lists of the two calendars. For example, if cal1 has 30-Mar and 29-Jun as on-exceptions and cal2 has 29-Jun and 28-Sep as on-exceptions, the resulting calendar has only 29-Jun as an on-exception.

The function performs a union of the off-exceptions of the two calendars. For example, if cal1 has 01-Jan and 04-Jul as off-exceptions and cal2 has 01-Jan and 14-Jul as off-exceptions, the resulting calendar has 01-Jan, 04-Jul, and 14-Jul as off-exceptions.

CombineCals and IntersectCals differ as follows:

Example

Combine two calendars (GENERIC-CAL1 and GENERIC-CAL2), then intersect the two calendars:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Combine tstCal1 and tstCal2
 resultCal := ORDSYS.Calendar.CombineCals(tstCal1, tstCal2, equalFlag);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of CombineCals')
 INTO dummyVal 
 FROM dual;
 DBMS_OUTPUT.PUT_LINE('equalFlag = ' || equalFlag);

 -- Intersect tstCal1 and tstCal2
 resultCal := ORDSYS.Calendar.IntersectCals(tstCal1, tstCal2);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of IntersectCals') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output:

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01-JAN-96
 MaxDate = 31-DEC-96
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 07-JAN-96
 onExceptions  :
     21-JAN-96     03-FEB-96     24-MAR-96
     27-APR-96     19-MAY-96     23-JUN-96
     07-JUL-96     04-AUG-96     15-SEP-96
 offExceptions :
     08-JAN-96     02-FEB-96     05-MAR-96
     04-APR-96     08-MAY-96     25-JUN-96
     09-JUL-96

Calendar Name = GENERIC-CAL2
 Frequency = 4 (day)
 MinDate = 01-JAN-96
 MaxDate = 31-DEC-97
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 08-JAN-96
 onExceptions  :
     07-JUL-96     04-AUG-96     15-SEP-96
     13-OCT-96     10-NOV-96     14-DEC-96
     04-JAN-97     09-FEB-97     08-MAR-97
     05-APR-97     11-MAY-97     08-JUN-97
 offExceptions :
     09-JUL-96     05-AUG-96     10-SEP-96
     23-OCT-96     19-NOV-96     12-DEC-96
     01-JAN-97     12-FEB-97     04-MAR-97
     07-APR-97     05-MAY-97     09-JUN-97

result of CombineCals :

 Frequency = 4 (day)
 MinDate = 01-JAN-96
 MaxDate = 31-DEC-96
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 07-JAN-96
 onExceptions  :
     07-JUL-96     04-AUG-96     15-SEP-96
 offExceptions :
     08-JAN-96     02-FEB-96     05-MAR-96
     04-APR-96     08-MAY-96     25-JUN-96
     09-JUL-96     05-AUG-96     10-SEP-96
     23-OCT-96     19-NOV-96     12-DEC-96
equalFlag = 0

result of IntersectCals :

 Frequency = 4 (day)
 MinDate = 01-JAN-96
 MaxDate = 31-DEC-96
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 08-JAN-96
 onExceptions  :
     07-JUL-96     04-AUG-96     15-SEP-96
 offExceptions :
     08-JAN-96     02-FEB-96     05-MAR-96
     04-APR-96     08-MAY-96     25-JUN-96
     09-JUL-96     05-AUG-96     10-SEP-96
     23-OCT-96     19-NOV-96     12-DEC-96

Day

Format

ORDSYS.Calendar.Day(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of day, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Example

Insert into the stockdemo_calendars table a calendar of day frequency with a calendar name of Daily and an anchor date of 01-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Day(
       'Daily', 
       (to_date('01-01-97','MM-DD-YY'))));


DeleteExceptions

Format

ORDSYS.Calendar.DeleteExceptions(

inputCal IN ORDSYS.ORDTCalendar,

delExcDate IN DATE

) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.Calendar.DeleteExceptions(

inputCal IN ORDSYS.ORDTCalendar,

delExcTab IN ORDSYS.ORDTDateTab

) RETURN ORDSYS.ORDTCalendar;

Description

Deletes from the specified calendar all exceptions that either match a specified date (delExcDate) or are included in a table of dates (delExcTab), and returns the resulting calendar.

Parameters

inputCal

The calendar from which one or more exceptions are to be deleted.

delExcDate

The date to be deleted from the exceptions of the calendar.

delExcTab

A table of dates to be deleted from the exceptions of the calendar.

Usage

If a date to be deleted is in either the on-exception list or off-exception list of the calendar, the function deletes the date from the appropriate list.

If delExcDate is not in either the on-exception list or off-exception list of the calendar, the function returns the input calendar with no changes.

For any date in delExcTab that is not in either the on-exception list or off-exception list of the calendar, the function ignores the date. If no date in delExcTab is in either the on-exception list or off-exception list of the calendar, the function returns the input calendar with no changes.

Example

Delete some exceptions from a calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDTab ORDSYS.ordtDateTab;
resultCal ORDSYS.ORDTCalendar;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Delete some exceptions in tstCal.
 tstDTab := ORDSYS.ORDTDateTab(
                  '01/21/1996', -- ON  Exception
                  '05/08/1996', -- OFF Exception
                  '08/04/1996', -- ON  Exception
                  '07/09/1996');-- OFF Exception
 SELECT ORDSYS.TimeSeries.Display(tstDTab, 'Input DateTab') 
 INTO dummyVal
 FROM dual;
 resultCal := ORDSYS.Calendar.DeleteExceptions(tstCal, tstDTab);
 SELECT ORDSYS.TimeSeries.Display(resultCal) INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output. The second display of information about GENERIC-CAL1 does not include the deleted on-exceptions and off-exceptions.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Input DateTab :

     01/21/1996 00:00:00     05/08/1996 00:00:00     08/04/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     02/03/1996 00:00:00     03/24/1996 00:00:00     04/27/1996 00:00:00
     05/19/1996 00:00:00     06/23/1996 00:00:00     07/07/1996 00:00:00
     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     06/25/1996 00:00:00

DisplayValCal Procedure

Format

ORDSYS.Calendar.DisplayValCal(

validFlag IN INTEGER,

outMessage IN VARCHAR2,

invOnExc IN ORDSYS.ORDTDateTab,

invOffExc IN ORDSYS.ORDTDateTab,

impOnExc IN ORDSYS.ORDTDateTab,

impOffExc IN ORDSYS.ORDTDateTab,

inputCal IN ORDSYS.ORDTCalendar,

mesg IN VARCHAR2

);

Description

Displays the results returned by the ValidateCal function.


Note:

DisplayValCal is a procedure, not a function. Procedures do not return values.  


Parameters

validFlag

The return value from the ValidateCal function call:

Value   Meaning  

0  

The calendar is valid. No errors were found.  

1  

Correctable errors were found and corrected. The resulting calendar is valid.  

-1  

Uncorrectable errors were found. The calendar is not valid.  

outMessage

Message output by ValidateCal describing how the calendar was repaired (if the return value = 1) or why the calendar could not be repaired (if the return
value = -1).

invOnExc

Table of the invalid on-exceptions found in the calendar.

invOffExc

Table of the invalid off-exceptions found in the calendar.

impOnExc

Table of the imprecise on-exceptions found in the calendar.

impOffExc

Table of the imprecise off-exceptions found in the calendar.

inputCal

The calendar returned by ValidateCal (repaired if necessary).

mesg

Optional message.

Usage

This procedure is intended to be used with the ValidateCal function. See the information on ValidateCal in this chapter.

Example

Use the IsValidCal and ValidateCal functions and the DisplayValCal procedure with an invalid calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
outMessage  varchar2(32750);
invOnExc    ORDSYS.ORDTDateTab;
invOffExc   ORDSYS.ORDTDateTab;
impOnExc    ORDSYS.ORDTDateTab;
impOffExc   ORDSYS.ORDTDateTab;
dummyval     integer;
validFlag     integer;
tstCal1     ORDSYS.ORDTCalendar := 
               ORDSYS.ORDTCalendar(
                  0,
                  'CALENDAR MYCAL',
                  4, 
                  ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1,1,1,1,1,0,0),
                                              TO_DATE('01-08-1996 01:01:01')),
                  TO_DATE('01-01-1975'),
		  TO_DATE('01-01-1999'),
                  ORDSYS.ORDTExceptions(
           TO_DATE('02-03-1969'), -- Date < minDate,
           TO_DATE('02-14-1969'), -- Date < minDate,
           TO_DATE('02-03-1999'), -- Date > maxDate,
           TO_DATE('02-17-1999'), -- Date > maxDate,
           TO_DATE('12-31-1995'), -- Maps to 0 in pattern (Sunday)
           TO_DATE('01-13-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-24-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('03-30-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-02-1996 01:01:01'), -- Imprecise
           TO_DATE('03-04-1996 01:01:01'), -- Imprecise 
           TO_DATE('04-05-1996 02:02:02'), -- Imprecise
           TO_DATE('03-25-1996'), -- Valid off-exception
           TO_DATE('01-22-1996'), -- Valid, but out of sequence
           TO_DATE('02-12-1996'),  
           TO_DATE('04-30-1996'),  
           NULL,                  -- Null date
           TO_DATE('02-12-1996'), -- Duplicate date within OFFs
           NULL,                  -- Null date
           TO_DATE('04-30-1996'), -- Duplicate off-exception
           NULL,                  -- Null date
           TO_DATE('03-25-1996'), -- Duplicate off-exception
           TO_DATE('01-22-1996'), -- Duplicate off-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('02-02-1996'),  
           TO_DATE('03-04-1996'),
           TO_DATE('05-06-1997')),
		    ORDSYS.ORDTExceptions(
           TO_DATE('02-08-1969'), -- Date < minDate,
           TO_DATE('02-15-1969'), -- Date < minDate,
           TO_DATE('02-13-1999'), -- Date > maxDate,
           TO_DATE('02-20-1999'), -- Date > maxDate,
           TO_DATE('01-03-1996'), -- Maps to 1 in pattern (Wednesday)
           TO_DATE('02-19-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-18-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('05-27-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-23-1996 01:01:01'), -- Imprecise
           TO_DATE('02-18-1996 01:01:01'), -- Imprecise
           TO_DATE('05-26-1996 01:01:01'), -- Imprecise
           TO_DATE('01-13-1996'), -- Valid on-exception
           TO_DATE('01-14-1996'), -- Valid on-exception
           NULL,                  -- Null date
           NULL,                  -- Null date
           TO_DATE('02-24-1996'), -- Valid on-exception
           TO_DATE('03-23-1996'), -- Valid on-exception
           TO_DATE('01-13-1996'), -- Duplicate on-exception
           TO_DATE('01-14-1996'), -- Duplicate on-exception
           TO_DATE('02-24-1996'), -- Duplicate on-exception
           TO_DATE('03-23-1996'), -- Duplicate on-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('01-06-1996'), -- Valid, but out of sequence
           TO_DATE('02-03-1996'),
           TO_DATE('05-04-1997'))
                  );
BEGIN
   SELECT ORDSYS.TIMESERIES.Display(tstCal1, 'tstCal1') INTO dummyval
   FROM dual;
   validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal1);
   IF(validFlag = 0)
   THEN
       validFlag := ORDSYS.CALENDAR.ValidateCal(
                tstCal1, outMessage, invOnExc, invOffExc, impOnExc, impOffExc
                );
 
       ORDSYS.TIMESERIES.DisplayValCal(
             validFlag,
             outMessage,
             invOnExc,
             invOffExc,
             impOnExc,
             impOffExc,
             tstCal1,
             'Your Message'
             );
   END IF;
END;
/

This example might produce the following output:

tstCal1 :

Calendar Name = CALENDAR MYCAL
 Frequency = 4 (day)
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 01:01:01
 onExceptions  :
     02/08/1969 00:00:00     02/15/1969 00:00:00     02/13/1999 00:00:00
     02/20/1999 00:00:00     01/03/1996 00:00:00     02/19/1996 00:00:00
     03/18/1996 00:00:00     05/27/1996 00:00:00     03/23/1996 01:01:01
     02/18/1996 01:01:01     05/26/1996 01:01:01     01/13/1996 00:00:00
     01/14/1996 00:00:00
     02/24/1996 00:00:00     03/23/1996 00:00:00     01/13/1996 00:00:00
     01/14/1996 00:00:00     02/24/1996 00:00:00     03/23/1996 00:00:00
     01/17/1996 00:00:00     05/28/1996 00:00:00     06/18/1996 00:00:00
     04/23/1996 00:00:00     01/06/1996 00:00:00     02/03/1996 00:00:00
     05/04/1997 00:00:00
 offExceptions :
     02/03/1969 00:00:00     02/14/1969 00:00:00     02/03/1999 00:00:00
     02/17/1999 00:00:00     12/31/1995 00:00:00     01/13/1996 00:00:00
     02/24/1996 00:00:00     03/30/1996 00:00:00     02/02/1996 01:01:01
     03/04/1996 01:01:01     04/05/1996 02:02:02     03/25/1996 00:00:00
     01/22/1996 00:00:00     02/12/1996 00:00:00     04/30/1996 00:00:00
          02/12/1996 00:00:00
     04/30/1996 00:00:00          03/25/1996 00:00:00
     01/22/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00     02/02/1996 00:00:00
     03/04/1996 00:00:00     05/06/1997 00:00:00

DisplayValCal Your Message:

TS-WRN: the input calendar has rectifiable errors. See the message for details

message output by validateCal:

TS-WRN: fixed precision of the pattern anchor date
TS-WRN: removed superfluous dates in the on exception list (refer invalidOnExc)
TS-WRN: fixed imprecise dates in the on exception list (refer impreciseOnExc)
TS-WRN: removed null dates in the on exception list
TS-WRN: sorted the on exceptions list
TS-WRN: removed duplicate dates in the on exceptions list
TS-WRN: removed superfluous dates in off exceptions list (refer invalidOffExc)
TS-WRN: fixed imprecise dates in the off exception list (refer impreciseOffExc)
TS-WRN: removed null dates in the off exception list
TS-WRN: sorted the off exceptions list
TS-WRN: removed duplicate dates in the off exceptions list
TS-WRN: the on exceptions list was trimmed between calendar minDate & maxDate
TS-WRN: the off exceptions list was trimmed between calendar minDate & maxDate

list of invalid on exceptions :

     01/03/1996 00:00:00     02/19/1996 00:00:00     03/18/1996 00:00:00
     05/27/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00

list of invalid off exceptions :

     12/31/1995 00:00:00     01/13/1996 00:00:00     02/24/1996 00:00:00
     03/30/1996 00:00:00

list of imprecise on exceptions :

     03/23/1996 01:01:01     02/18/1996 01:01:01     05/26/1996 01:01:01

list of imprecise off exceptions :

     02/02/1996 01:01:01     03/04/1996 01:01:01     04/05/1996 02:02:02

the validated calendar :
                         
Calendar Name = CALENDAR MYCAL
 Frequency = 4 (day)
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     01/06/1996 00:00:00     01/13/1996 00:00:00     01/14/1996 00:00:00
     02/03/1996 00:00:00     02/18/1996 00:00:00     02/24/1996 00:00:00
     03/23/1996 00:00:00     05/26/1996 00:00:00     05/04/1997 00:00:00
 offExceptions :
     01/17/1996 00:00:00     01/22/1996 00:00:00     02/02/1996 00:00:00
     02/12/1996 00:00:00     03/04/1996 00:00:00     03/25/1996 00:00:00
     04/05/1996 00:00:00     04/23/1996 00:00:00     04/30/1996 00:00:00
     05/28/1996 00:00:00     06/18/1996 00:00:00     05/06/1997 00:00:00

EqualCals

Format

ORDSYS.Calendar.EqualCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

) RETURN BINARY_INTEGER;

Description

Checks if two calendars (completely or within a specified date range) are equal.

Parameters

cal1

The first calendar to be checked.

cal2

The second calendar to be checked.

startDate

Starting date for the checking. If startDate is not specified, the starting date is the starting date for the calendars, or the higher (later) of the starting dates if they are different.

endDate

Ending date for the checking. If endDate is not specified, the ending date is the ending date for the calendars, or the lower (earlier) of the ending dates if they are different.

Usage

The function checks if the frequencies, off-exceptions, on-exceptions, and aligned patterns are the same for the two calendars. If they are all the same, the function returns 1; if they are not all the same, the function returns 0.

The function does not require the calendars to have the same starting and ending dates.

Example

Check if two calendars (GENERIC-CAL1 and GENERIC-CAL2) are equal:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Compare tstCal1 and tstCal2 for equality.
 DBMS_OUTPUT.NEW_LINE;
 equalFlag := ORDSYS.Calendar.EqualCals(tstCal1, tstCal2);
 DBMS_OUTPUT.PUT_LINE('EqualCals(GENERIC-CAL1, GENERIC-CAL2) = ' || equalFlag);

END;
/

This example might display the following output. In this example, the returned value of 0 indicates that the calendars are not equal.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL2
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1997 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
     10/13/1996 00:00:00     11/10/1996 00:00:00     12/14/1996 00:00:00
     01/04/1997 00:00:00     02/09/1997 00:00:00     03/08/1997 00:00:00
     04/05/1997 00:00:00     05/11/1997 00:00:00     06/08/1997 00:00:00
 offExceptions :
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
     01/01/1997 00:00:00     02/12/1997 00:00:00     03/04/1997 00:00:00
     04/07/1997 00:00:00     05/05/1997 00:00:00     06/09/1997 00:00:00

EqualCals(GENERIC-CAL1, GENERIC-CAL2) = 0

GenDateRangeTab

Format

ORDSYS.Calendar.GenDateRangeTab(

inputCal ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTDateRangeTab;

Description

Given an input calendar, returns a table of date ranges that represent all of the valid intervals in the calendar (or from startDate through endDate).

Parameters

inputCal

The input calendar.

startDate

Starting date for returning date ranges. If startDate is not specified, the starting date is the starting date for the calendar (minDate).

endDate

Ending date for returning date ranges. The returned ending date is actually the first valid timestamp after endDate. If endDate is not specified, the ending date is the ending date for the calendar (maxDate).

Usage

The function can be used to perform time scaling against any table with a DATE column. It is used in a TABLE construct in the FROM clause of a SQL statement, and it generates a table of intervals based on inputCal. By joining the output of this function with a table containing a DATE column, you can use GROUP BY semantics to aggregate by the generated intervals.

For example, if you specify a monthly calendar starting on 01-Jan-1999 and ending on 31-Mar-1999, with standard U.S. holidays (including 01-Jan), the function returns the following timestamps:

02-Jan-1999  

01-Feb-1999  

01-Feb-1999  

01-Mar-1999  

01-Mar-1999  

01-Apr-1999  

The scope of the date ranges returned is adjusted, if necessary, as follows:

For example, assume a monthly calendar with a '1' pattern (no off days), no exceptions, and starting on the first day of the month. If startDate is 15-Jan-1999 and endDate is 15-Dec-1999, the returned date ranges are from February through December of 1999.

For best performance, especially with large data sets, always follow these guidelines when constructing a date range to be joined with time series data:

If the calendar does not include date bounds (a minDate and maxDate), you must specify startDate and endDate. (The date range table cannot be infinite.)

If startDate is greater (later) than endDate, an exception is raised.

Examples

Create a date range table of 10-day cycles (using the 10-day frequency, described in Table 2-2 in Section 2.2.1) for 1990 through 1993:

SELECT to_char(t.startdate,'DAY'),
       to_char(t.startdate,'DD-MON-YYYY HH24:MI:SS'),
       to_char(t.enddate,'DAY'),
       to_char(t.enddate,  'DD-MON-YYYY HH24:MI:SS')
   FROM TABLE(cast ( ORDSYS.Calendar.GenDateRangeTab(
              ORDSYS.ORDTCalendar(
                0,
                '10-Day',
                10,
                ORDSYS.ORDTPattern(
                  ORDSYS.ORDTPatternBits(1),
                    TO_DATE('01-JAN-1998','DD-MON-YYYY')), 
                TO_DATE('01-JAN-1990','DD-MON-YYYY'),
                TO_DATE('31-DEC-1993','DD-MON-YYYY'), 
                ORDSYS.ORDTExceptions(),
                ORDSYS.ORDTExceptions() 
              )) as ORDSYS.ORDTDateRangeTab)) t;

This example might display the following output:

TO_CHAR(T TO_CHAR(T.STARTDATE, TO_CHAR(T TO_CHAR(T.ENDDATE,'D
--------- -------------------- --------- --------------------
MONDAY    01-JAN-1990 00:00:00 THURSDAY  11-JAN-1990 00:00:00
THURSDAY  11-JAN-1990 00:00:00 SUNDAY    21-JAN-1990 00:00:00
SUNDAY    21-JAN-1990 00:00:00 THURSDAY  01-FEB-1990 00:00:00
THURSDAY  01-FEB-1990 00:00:00 SUNDAY    11-FEB-1990 00:00:00
SUNDAY    11-FEB-1990 00:00:00 WEDNESDAY 21-FEB-1990 00:00:00
WEDNESDAY 21-FEB-1990 00:00:00 THURSDAY  01-MAR-1990 00:00:00
  ...          ...               ...          ...
WEDNESDAY 01-DEC-1993 00:00:00 SATURDAY  11-DEC-1993 00:00:00
SATURDAY  11-DEC-1993 00:00:00 TUESDAY   21-DEC-1993 00:00:00
TUESDAY   21-DEC-1993 00:00:00 SATURDAY  01-JAN-1994 00:00:00
144 rows selected.

Return the count and the minimum, maximum, and average values of closing prices (for all stock tickers, not broken down by ticker) from the tsquick_tab table for 01-Oct-1996 through 31-Dec-1996, using a weekly business-day calendar generated by the GenDateRangeTab function:

select /*+ ORDERED */ to_char(t.startdate,'DAY') "day",
           to_char(t.startdate,'DD-MON-YYYY HH24:MI:SS') "tstamp",
           count(s.close) "count",
           min(s.close) "min",
           max(s.close) "max",
           avg(s.close) "avg"
    from TABLE(cast ( ORDSYS.Calendar.GenDateRangeTab(
 	ORDSYS.ORDTCalendar(
                 0,
                 'BusinessWeek',
                 4,
                 ORDSYS.ORDTPattern(
                          ORDSYS.ORDTPatternBits(0,5,0),
                          TO_DATE('15-DEC-1996','DD-MON-YYYY')), 
                 TO_DATE('01-OCT-1996','DD-MON-YYYY'),
                 TO_DATE('31-DEC-1996','DD-MON-YYYY'), 
                 ORDSYS.ORDTExceptions(),
                 ORDSYS.ORDTExceptions() 
                 )) as ORDSYS.ORDTDateRangeTab)) t,
          tsquick_tab s
    where s.tstamp >= t.startdate and s.tstamp < t.enddate
    group by t.startdate
    order by t.startdate;

Note that this example follows the guidelines in the Usage section for this function, including the use of the /*+ ORDERED */ optimizer hint.

This example might produce the following output:

day       tstamp               count      min        max        avg
--------- -------------------- ---------- ---------- ---------- ----------
MONDAY    28-OCT-1996 00:00:00          6      23.69     79.688 63.7818333
MONDAY    04-NOV-1996 00:00:00         20      23.72      83.25   52.64925
MONDAY    11-NOV-1996 00:00:00         20      23.84     85.813    53.5503
MONDAY    18-NOV-1996 00:00:00         20      23.82     88.938    55.2897
MONDAY    25-NOV-1996 00:00:00         15      23.71      88.75 54.5533333
MONDAY    02-DEC-1996 00:00:00         20      23.75     89.875    57.8124
MONDAY    09-DEC-1996 00:00:00         20       23.4     94.375   60.12525
MONDAY    16-DEC-1996 00:00:00         19      23.36     95.875 59.6052632
MONDAY    23-DEC-1996 00:00:00         15      23.93         97 61.1606667
MONDAY    30-DEC-1996 00:00:00          8      24.11         99  63.951875
10 rows selected.

GetIntervalEnd

Format

ORDSYS.TimeSeries.GetIntervalEnd(

inputCal IN ORDSYS.ORDTCalendar,

inputDate IN DATE

) RETURN DATE;

Description

Given a Calendar and an input timestamp (inputDate), returns the end of the interval that includes the input timestamp.

Parameters

inputCal

The input calendar.

inputDate

Timestamp for which the end of the interval is to be returned.

Usage

If inputDate is a valid timestamp, the function returns a date. Otherwise, the function returns a null.

An exception is returned if inputCal is null.

Example

Return the end of the interval for several timestamps:

DECLARE 
inputCal ORDSYS.ORDTCalendar; 
tstDate  DATE; 
retDate  DATE; 
tstDtTab ordsys.ordtdatetab; 
BEGIN 
 
  -- Select a Calendar into a local variable 
     SELECT value(cal)  
     INTO   inputCal  
     FROM   TSDEV.stockdemo_calendars cal 
     WHERE  cal.name = 'BIWEEKLY'; 
 
  -- Display the input Calendar 
     ORDSYS.TimeSeries.Display(inputCal); 
 
     DBMS_OUTPUT.PUT_LINE(''); 
 
  -- GetIntervalEnd of a Valid timestamp 
     tstDate  := TO_DATE('01-JAN-1996','DD-MON-YYYY'); 
     retDate  := ORDSYS.Calendar.GetIntervalEnd(inputCal, tstDate); 
 
     DBMS_OUTPUT.PUT_LINE('GetIntervalEnd (' || 
                           TO_CHAR(tstDate, 'MM-DD-YYYY')  || 
                                                ') = '                || 
                           TO_CHAR(retDate, 'MM-DD-YYYY')  ); 
 
  -- GetIntervalEnd of an InValid timestamp - returns NULL 
     tstDate  := TO_DATE('01-JUL-1996','DD-MON-YYYY'); 
     retDate  := ORDSYS.Calendar.GetIntervalEnd(inputCal, tstDate); 
 
     DBMS_OUTPUT.PUT_LINE('GetIntervalEnd (' || 
                           TO_CHAR(tstDate, 'MM-DD-YYYY')  || 
                                                ') = '                || 
                           TO_CHAR(retDate, 'MM-DD-YYYY')  ); 
 
  -- GetIntervalEnd of a Covered timestamp 
     tstDate  := TO_DATE('08-JAN-1996','DD-MON-YYYY'); 
     retDate  := ORDSYS.Calendar.GetIntervalEnd(inputCal, tstDate); 
 
     DBMS_OUTPUT.PUT_LINE('GetIntervalEnd (' || 
                           TO_CHAR(tstDate, 'MM-DD-YYYY')  || 
                                                ') = '                || 
                           TO_CHAR(retDate, 'MM-DD-YYYY')  ); 
 
END; 
/ 

This example might produce the following output:

Calendar Name = BIWEEKLY
 Frequency = 5 (week)
 MinDate is NULL
 MaxDate is NULL
 patBits: 2
 patAnchor = 01/01/1996 00:00:00
 onExceptions  :
 offExceptions :
     07/01/1996 00:00:00

GetIntervalEnd (01-01-1996) = 01-15-1996
GetIntervalEnd (07-01-1996) =
GetIntervalEnd (01-08-1996) = 01-15-1996

GetIntervalStart

Format

ORDSYS.TimeSeries.GetIntervalStart(

inputCal IN ORDSYS.ORDTCalendar,

inputDate IN DATE

) RETURN DATE;

Description

Given a Calendar and an input timestamp (inputDate), returns the start of the interval that includes the input timestamp.

Parameters

inputCal

The input calendar.

inputDate

Timestamp for which the start of the interval is to be returned.

Usage

If inputDate is a valid timestamp, the function returns a date. Otherwise, the function returns a null.

An exception is returned if inputCal is null.

Example

Return the start of the interval for several timestamps:

DECLARE 
inputCal ORDSYS.ORDTCalendar; 
tstDate  DATE; 
retDate  DATE; 
tstDtTab ordsys.ordtdatetab; 
BEGIN 
 
  -- Select a Calendar into a local variable 
     SELECT value(cal)  
     INTO   inputCal  
     FROM   TSDEV.stockdemo_calendars cal 
     WHERE  cal.name = 'BIWEEKLY'; 
 
  -- Display the input Calendar 
     ORDSYS.TimeSeries.Display(inputCal); 
 
     DBMS_OUTPUT.PUT_LINE(''); 
 
  -- GetIntervalStart of a Valid timestamp 
     tstDate  := TO_DATE('01-JAN-1996','DD-MON-YYYY'); 
     retDate  := ORDSYS.Calendar.GetIntervalStart(inputCal, tstDate); 
 
     DBMS_OUTPUT.PUT_LINE('GetIntervalStart (' || 
                           TO_CHAR(tstDate, 'MM-DD-YYYY')  || 
                                                ') = '                || 
                           TO_CHAR(retDate, 'MM-DD-YYYY')  ); 
 
  -- GetIntervalStart of an InValid timestamp - returns NULL 
     tstDate  := TO_DATE('01-JUL-1996','DD-MON-YYYY'); 
     retDate  := ORDSYS.Calendar.GetIntervalStart(inputCal, tstDate); 
 
     DBMS_OUTPUT.PUT_LINE('GetIntervalStart (' || 
                           TO_CHAR(tstDate, 'MM-DD-YYYY')  || 
                                                ') = '                || 
                           TO_CHAR(retDate, 'MM-DD-YYYY')  ); 
 
  -- GetIntervalStart of a Covered timestamp 
     tstDate  := TO_DATE('08-JAN-1996','DD-MON-YYYY'); 
     retDate  := ORDSYS.Calendar.GetIntervalStart(inputCal, tstDate); 
 
     DBMS_OUTPUT.PUT_LINE('GetIntervalStart (' || 
                           TO_CHAR(tstDate, 'MM-DD-YYYY')  || 
                                                ') = '                || 
                           TO_CHAR(retDate, 'MM-DD-YYYY')  ); 
 
END; 
/ 

This example might produce the following output:

Calendar Name = BIWEEKLY
 Frequency = 5 (week)
 MinDate is NULL
 MaxDate is NULL
 patBits: 2
 patAnchor = 01/01/1996 00:00:00
 onExceptions  :
 offExceptions :
     07/01/1996 00:00:00

GetIntervalStart (01-01-1996) = 01-01-1996
GetIntervalStart (07-01-1996) =
GetIntervalStart (01-08-1996) = 01-01-1996

GetOffset

Format

ORDSYS.TimeSeries.GetOffset(

inputCal IN ORDSYS.ORDTCalendar,

origin_date IN DATE,

reference_date IN DATE

) RETURN INTEGER;

Description

Given a calendar, one date (origin_date), and another date (reference_date), returns the number of timestamps that the second date is offset from the first.

Parameters

inputCal

The input calendar.

origin_date

Date from which the offset is to be computed.

reference_date

Date whose offset from origin_date is to be returned.

Usage

The function considers the frequency, pattern, and exceptions of the calendar.

The returned integer is positive if reference_date is one or more timestamps in the future with respect to origin_date, and negative if it is in the past with respect to origin_date. For example, assume that the calendar includes Mondays through Fridays, that 04-Jul-1997 (Friday) is an off-exception, and that 03-Jul-1997 (Thursday) is the origin_date. If 10-Jul-1997 (Thursday) is the reference_date, the returned offset is 4; if the reference_date is 01-Jul-1997 (Monday), the returned offset is -2.

If origin_date and reference_date are the same, the function returns 0 (zero).

An exception is returned if the calendar has an empty or null pattern.

Example

Return the offset of 05-Jun-1996 from 04-Mar-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get offset of 05-JUN-1996 from 04-MAR-1996.
 tstDate1 := TO_DATE('04/03/1996');
 tstDate2 := TO_DATE('06/05/1996');
 result  := ORDSYS.Calendar.GetOffset(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('GetOffset(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. In this example, 05-Jun-1996 is 45 timestamps later than 04-Mar-1996.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

GetOffset(04/03/1996 00:00:00 , 06/05/1996 00:00:00) = 45

Hour

Format

ORDSYS.Calendar.Hour(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of hour, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Example

Insert into the stockdemo_calendars table a calendar of hour frequency with a calendar name of Hourly and an anchor date of 01-Jan-1997 (at midnight). The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Hour(
       'Hourly', 
       (to_date('01-01-97 01','MM-DD-YY HH'))));


InsertExceptions

Format

ORDSYS.Calendar.InsertExceptions(

inputCal IN ORDSYS.ORDTCalendar,

newExcDate IN DATE

) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.Calendar.InsertExceptions(

inputCal IN ORDSYS.ORDTCalendar,

newExcTab IN ORDSYS.ORDTDateTab

) RETURN ORDSYS.ORDTCalendar;

Description

Inserts into the specified calendar all exceptions that either match a specified date (newExcDate) or are included in a table of dates (newExcTab), and returns the resulting calendar.

Parameters

inputCal

The calendar into which one or more exceptions are to be inserted.

newExcDate

The date to be inserted as an exception in the calendar.

newExcTab

A table of dates to be inserted as exceptions in the calendar.

Usage

For each date to be inserted, the function inserts it in the appropriate list (off-exceptions or on-exceptions), according to the frequency and pattern of the calendar.

If a date to be inserted is already an exception in the calendar, the function ignores the request to insert the date.

If newExcDate or newExcTab is empty or null, or if all dates to be inserted already exist in the calendar as exceptions, the function returns the input calendar with no changes.

Example

Insert some exceptions into a calendar.

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDTab ORDSYS.ordtDateTab;
resultCal ORDSYS.ORDTCalendar;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Populate tstDTab with some on- and off-exceptions.
 tstDTab := ORDSYS.ORDTDateTab(
                  '02/10/1996', -- ON  Exception
                  '07/09/1996', -- OFF Exception
                  '03/17/1996', -- ON  Exception
                  '04/08/1996');-- OFF Exception
 SELECT ORDSYS.TimeSeries.Display(tstDTab, 'Input DateTab') 
 INTO dummyVal
 FROM dual;

                   
 -- Insert some exceptions in tstCal.
 resultCal := ORDSYS.Calendar.InsertExceptions(tstCal, tstDTab);
 SELECT ORDSYS.TimeSeries.Display(resultCal) INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output. The second display of information about GENERIC-CAL1 includes the added on-exceptions and off-exceptions.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00


Input DateTab :

     02/10/1996 00:00:00     07/09/1996 00:00:00     03/17/1996 00:00:00
     04/08/1996 00:00:00

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     02/10/1996 00:00:00
     03/17/1996 00:00:00     03/24/1996 00:00:00     04/27/1996 00:00:00
     05/19/1996 00:00:00     06/23/1996 00:00:00     07/07/1996 00:00:00
     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     04/08/1996 00:00:00     05/08/1996 00:00:00
     06/25/1996 00:00:00     07/09/1996 00:00:00

IntersectCals

Format

ORDSYS.Calendar.IntersectCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar

) RETURN ORDSYS.ORDTCalendar;

Description

Returns the intersection of two calendars.

Parameters

cal1

The first calendar to be intersected.

cal2

The second calendar to be intersected.

Usage

The function performs an intersection of the two input calendars, as follows:

If the frequencies of the two calendars are not equal, the function returns NULL.

Contrast this function with UnionCals, which performs a union of two calendars.

IntersectCals and CombineCals differ as follows:

Example

Combine two calendars (GENERIC-CAL1 and GENERIC-CAL2), then intersect the two calendars:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Combine tstCal1 and tstCal2.
 resultCal := ORDSYS.Calendar.CombineCals(tstCal1, tstCal2, equalFlag);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of CombineCals')
 INTO dummyVal 
 FROM dual;
 DBMS_OUTPUT.PUT_LINE('equalFlag = ' || equalFlag);

 -- Intersect tstCal1 and tstCal2.
 resultCal := ORDSYS.Calendar.IntersectCals(tstCal1, tstCal2);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of IntersectCals') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output:

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL2
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1997 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
     10/13/1996 00:00:00     11/10/1996 00:00:00     12/14/1996 00:00:00
     01/04/1997 00:00:00     02/09/1997 00:00:00     03/08/1997 00:00:00
     04/05/1997 00:00:00     05/11/1997 00:00:00     06/08/1997 00:00:00
 offExceptions :
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
     01/01/1997 00:00:00     02/12/1997 00:00:00     03/04/1997 00:00:00
     04/07/1997 00:00:00     05/05/1997 00:00:00     06/09/1997 00:00:00

result of CombineCals :

 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
equalFlag = 0

result of IntersectCals :

 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00

InvalidTimeStampsBetween

Format

ORDSYS.Calendar.InvalidTimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN ORDSYS.ORDTDateTab;

Description

Given starting and ending input timestamps, returns a table (ORDTDateTab) containing the invalid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for invalid timestamps.

endDate

Ending date in the range to be checked for invalid timestamps.

Usage

A timestamp is invalid if one or more of the following conditions are true:

startDate and endDate are included in the check for invalid timestamps.

If there are no invalid timestamps in the date range, the function returns an empty ORDTDateTab.

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with TimeStampsBetween, which returns a table containing the valid timestamps in a date range.

Example

Return a table of invalid timestamps between 03-Mar-1996 and 03-Jun-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
resultDTab ORDSYS.ordtDateTab;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get all the invalid timestamps between 03-MAR-1996 and 03-JUN-1996.
 tstDate1 := TO_DATE('03/03/1996');
 tstDate2 := TO_DATE('06/03/1996');
 resultDTab := ORDSYS.Calendar.InvalidTimeStampsBetween
                                     (tstCal, tstDate1, tstDate2);
 SELECT ORDSYS.TimeSeries.Display(resultDTab, 'InValid timestamps') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output:

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

InValid timestamps :

     03/03/1996 00:00:00     03/05/1996 00:00:00     03/09/1996 00:00:00
     03/10/1996 00:00:00     03/16/1996 00:00:00     03/17/1996 00:00:00
     03/23/1996 00:00:00     03/30/1996 00:00:00     03/31/1996 00:00:00
     04/04/1996 00:00:00     04/06/1996 00:00:00     04/07/1996 00:00:00
     04/13/1996 00:00:00     04/14/1996 00:00:00     04/20/1996 00:00:00
     04/21/1996 00:00:00     04/28/1996 00:00:00     05/04/1996 00:00:00
     05/05/1996 00:00:00     05/08/1996 00:00:00     05/11/1996 00:00:00
     05/12/1996 00:00:00     05/18/1996 00:00:00     05/25/1996 00:00:00
     05/26/1996 00:00:00     06/01/1996 00:00:00     06/02/1996 00:00:00

IsValidCal

Format

ORDSYS.Calendar.IsValidCal(

inputCal IN ORDSYS.ORDTCalendar

) RETURN BINARY_INTEGER

Description

Returns 1 if a calendar is valid and 0 if a calendar is not valid.

Parameters

inputCal

The calendar to be checked for validity.

Usage

A calendar is invalid (not valid) if it contains any errors. This function does not correct any errors or perform any repair operations on the calendar.

Contrast this function with the ValidateCal function, which checks the validity of the calendar and repairs any correctable errors. For detailed information on calendar errors, see the information on ValidateCal in this chapter.

If the IsValidCal function returns 0, you should do the following before you attempt to use the calendar:

  1. Use the ValidateCal function to repair any correctable errors.

  2. If there are any errors that ValidateCal cannot correct, correct these errors yourself.

  3. Repeat steps 1 and 2 as often as necessary until the resulting calendar is valid.

Example

Use the IsValidCal and ValidateCal functions and the DisplayValCal procedure with an invalid calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
outMessage  varchar2(32750);
invOnExc    ORDSYS.ORDTDateTab;
invOffExc   ORDSYS.ORDTDateTab;
impOnExc    ORDSYS.ORDTDateTab;
impOffExc   ORDSYS.ORDTDateTab;
dummyval     integer;
validFlag     integer;
tstCal1     ORDSYS.ORDTCalendar := 
               ORDSYS.ORDTCalendar(
                  0,
                  'CALENDAR MYCAL',
                  4, 
                  ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1,1,1,1,1,0,0),
                                              TO_DATE('01-08-1996 01:01:01')),
                  TO_DATE('01-01-1975'),
		  TO_DATE('01-01-1999'),
                  ORDSYS.ORDTExceptions(
           TO_DATE('02-03-1969'), -- Date < minDate,
           TO_DATE('02-14-1969'), -- Date < minDate,
           TO_DATE('02-03-1999'), -- Date > maxDate,
           TO_DATE('02-17-1999'), -- Date > maxDate,
           TO_DATE('12-31-1995'), -- Maps to 0 in pattern (Sunday)
           TO_DATE('01-13-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-24-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('03-30-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-02-1996 01:01:01'), -- Imprecise
           TO_DATE('03-04-1996 01:01:01'), -- Imprecise 
           TO_DATE('04-05-1996 02:02:02'), -- Imprecise
           TO_DATE('03-25-1996'), -- Valid off-exception
           TO_DATE('01-22-1996'), -- Valid, but out of sequence
           TO_DATE('02-12-1996'),  
           TO_DATE('04-30-1996'),  
           NULL,                  -- Null date
           TO_DATE('02-12-1996'), -- Duplicate date within OFFs
           NULL,                  -- Null date
           TO_DATE('04-30-1996'), -- Duplicate off-exception
           NULL,                  -- Null date
           TO_DATE('03-25-1996'), -- Duplicate off-exception
           TO_DATE('01-22-1996'), -- Duplicate off-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('02-02-1996'),  
           TO_DATE('03-04-1996'),
           TO_DATE('05-06-1997')),
		    ORDSYS.ORDTExceptions(
           TO_DATE('02-08-1969'), -- Date < minDate,
           TO_DATE('02-15-1969'), -- Date < minDate,
           TO_DATE('02-13-1999'), -- Date > maxDate,
           TO_DATE('02-20-1999'), -- Date > maxDate,
           TO_DATE('01-03-1996'), -- Maps to 1 in pattern (Wednesday)
           TO_DATE('02-19-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-18-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('05-27-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-23-1996 01:01:01'), -- Imprecise
           TO_DATE('02-18-1996 01:01:01'), -- Imprecise
           TO_DATE('05-26-1996 01:01:01'), -- Imprecise
           TO_DATE('01-13-1996'), -- Valid on-exception
           TO_DATE('01-14-1996'), -- Valid on-exception
           NULL,                  -- Null date
           NULL,                  -- Null date
           TO_DATE('02-24-1996'), -- Valid on-exception
           TO_DATE('03-23-1996'), -- Valid on-exception
           TO_DATE('01-13-1996'), -- Duplicate on-exception
           TO_DATE('01-14-1996'), -- Duplicate on-exception
           TO_DATE('02-24-1996'), -- Duplicate on-exception
           TO_DATE('03-23-1996'), -- Duplicate on-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('01-06-1996'), -- Valid, but out of sequence
           TO_DATE('02-03-1996'),
           TO_DATE('05-04-1997'))
                  );
BEGIN
   SELECT ORDSYS.TIMESERIES.Display(tstCal1, 'tstCal1') INTO dummyval
   FROM dual;
   validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal1);
   IF(validFlag = 0)
   THEN
       validFlag := ORDSYS.CALENDAR.ValidateCal(
                tstCal1, outMessage, invOnExc, invOffExc, impOnExc, impOffExc
                );
 
       ORDSYS.TIMESERIES.DisplayValCal(
             validFlag,
             outMessage,
             invOnExc,
             invOffExc,
             impOnExc,
             impOffExc,
             tstCal1,
             'Your Message'
             );
   END IF;
END;
/

This example might produce the following output:

tstCal1 :

Calendar Name = CALENDAR MYCAL
 Frequency = 4 (day)
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 01:01:01
 onExceptions  :
     02/08/1969 00:00:00     02/15/1969 00:00:00     02/13/1999 00:00:00
     02/20/1999 00:00:00     01/03/1996 00:00:00     02/19/1996 00:00:00
     03/18/1996 00:00:00     05/27/1996 00:00:00     03/23/1996 01:01:01
     02/18/1996 01:01:01     05/26/1996 01:01:01     01/13/1996 00:00:00
     01/14/1996 00:00:00
     02/24/1996 00:00:00     03/23/1996 00:00:00     01/13/1996 00:00:00
     01/14/1996 00:00:00     02/24/1996 00:00:00     03/23/1996 00:00:00
     01/17/1996 00:00:00     05/28/1996 00:00:00     06/18/1996 00:00:00
     04/23/1996 00:00:00     01/06/1996 00:00:00     02/03/1996 00:00:00
     05/04/1997 00:00:00
 offExceptions :
     02/03/1969 00:00:00     02/14/1969 00:00:00     02/03/1999 00:00:00
     02/17/1999 00:00:00     12/31/1995 00:00:00     01/13/1996 00:00:00
     02/24/1996 00:00:00     03/30/1996 00:00:00     02/02/1996 01:01:01
     03/04/1996 01:01:01     04/05/1996 02:02:02     03/25/1996 00:00:00
     01/22/1996 00:00:00     02/12/1996 00:00:00     04/30/1996 00:00:00
          02/12/1996 00:00:00
     04/30/1996 00:00:00          03/25/1996 00:00:00
     01/22/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00     02/02/1996 00:00:00
     03/04/1996 00:00:00     05/06/1997 00:00:00

DisplayValCal Your Message:

TS-WRN: the input calendar has rectifiable errors. See the message for details

message output by validateCal:

TS-WRN: fixed precision of the pattern anchor date
TS-WRN: removed superfluous dates in the on exception list (refer invalidOnExc)
TS-WRN: fixed imprecise dates in the on exception list (refer impreciseOnExc)
TS-WRN: removed null dates in the on exception list
TS-WRN: sorted the on exceptions list
TS-WRN: removed duplicate dates in the on exceptions list
TS-WRN: removed superfluous dates in off exceptions list (refer invalidOffExc)
TS-WRN: fixed imprecise dates in the off exception list (refer impreciseOffExc)
TS-WRN: removed null dates in the off exception list
TS-WRN: sorted the off exceptions list
TS-WRN: removed duplicate dates in the off exceptions list
TS-WRN: the on exceptions list was trimmed between calendar minDate & maxDate
TS-WRN: the off exceptions list was trimmed between calendar minDate & maxDate

list of invalid on exceptions :

     01/03/1996 00:00:00     02/19/1996 00:00:00     03/18/1996 00:00:00
     05/27/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00

list of invalid off exceptions :

     12/31/1995 00:00:00     01/13/1996 00:00:00     02/24/1996 00:00:00
     03/30/1996 00:00:00

list of imprecise on exceptions :

     03/23/1996 01:01:01     02/18/1996 01:01:01     05/26/1996 01:01:01

list of imprecise off exceptions :

     02/02/1996 01:01:01     03/04/1996 01:01:01     04/05/1996 02:02:02

the validated calendar :
                         
Calendar Name = CALENDAR MYCAL
 Frequency = 4 (day)
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     01/06/1996 00:00:00     01/13/1996 00:00:00     01/14/1996 00:00:00
     02/03/1996 00:00:00     02/18/1996 00:00:00     02/24/1996 00:00:00
     03/23/1996 00:00:00     05/26/1996 00:00:00     05/04/1997 00:00:00
 offExceptions :
     01/17/1996 00:00:00     01/22/1996 00:00:00     02/02/1996 00:00:00
     02/12/1996 00:00:00     03/04/1996 00:00:00     03/25/1996 00:00:00
     04/05/1996 00:00:00     04/23/1996 00:00:00     04/30/1996 00:00:00
     05/28/1996 00:00:00     06/18/1996 00:00:00     05/06/1997 00:00:00

IsValidDate

Format

ORDSYS.Calendar.IsValidDate(

inputCal IN ORDSYS.ORDTCalendar,

checkDate IN DATE

) RETURN BINARY_INTEGER;

Description

Checks whether an input date is valid or invalid according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether the input timestamp is valid or invalid.

checkDate

The timestamp to be checked for validity according to the calendar.

Usage

If checkDate is valid, the function returns 1; if checkDate is invalid, the function returns 0.

A timestamp is invalid if one or more of the following conditions are true:

Example

Check if 02-Jan-1996 is a valid timestamp for a calendar (GENERIC-CAL1):

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;
 
 -- Verify if 02-JAN-1996 (a Monday) is a valid date and display the result.
 tstDate1 := TO_DATE('01/02/1996');
 result  := ORDSYS.Calendar.IsValidDate(tstCal,tstDate1);
 DBMS_OUTPUT.PUT_LINE('IsValidDate(' || tstDate1 || ') = ' || result);

END;
/

This example might produce the following output. In this example, the returned value of 1 indicates that 02-Jan-1996 is a valid timestamp for the BUSINESS-96 calendar.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

IsValidDate(01/02/1996 00:00:00) = 1

Minute

Format

ORDSYS.Calendar.Minute(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of minute, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Example

Insert into the stockdemo_calendars table a calendar of minute frequency with a calendar name of Minute and an anchor date of 01-Jan-1997 (at midnight). The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Minute(
       'Minute', 
       (to_date('01-01-97','MM-DD-YY'))));


Month

Format

ORDSYS.Calendar.Month(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of month, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Examples

Insert into the stockdemo_calendars table a calendar of month frequency with a calendar name of Monthly and an anchor date of 01-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Month(
       'Monthly', 
       (to_date('01-01-97','MM-DD-YY'))));

Return the sum of the daily trade volume for stock SAMCO for each month in the entire time series. For scaling, use a monthly calendar with a null name, an anchor date of 01-Jan-2001 (the default), no date boundaries (minDate or maxDate), and no exceptions. This example generates a calendar within the statement, and thus eliminates the need to specify a stored calendar that has the desired frequency.

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupSum(ts.volume,
                   ORDSYS.Calendar.Month())
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='SAMCO';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
11/01/96    10207000
12/01/96     3719450
2 rows selected.

NumInvalidTimeStampsBetween

Format

ORDSYS.Calendar.NumInvalidTimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of invalid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for invalid timestamps.

endDate

Ending date in the range to be checked for invalid timestamps.

Usage

A timestamp is invalid if one or more of the following conditions are true:

startDate and endDate are included in the check for invalid timestamps.

If there are no invalid timestamps in the date range, the function returns 0 (zero).

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with NumTimeStampsBetween, which returns the number of valid timestamps in a date range.

Example

Return the number of invalid timestamps between 03-Feb-1996 and 16-May-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get the number of invalid timestamps between 03-FEB-1996 and 16-MAY-1996.
 tstDate1 := TO_DATE('02/03/1996');
 tstDate2 := TO_DATE('05/16/1996');
 result  := ORDSYS.Calendar.NumInvalidTimeStampsBetween(
                                                   tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumInvalidTimeStampsBetween(' || tstDate1 ||' , ' || 
                                                  tstDate2|| ') = ' || result);
END;
/
 

This example might produce the following output. In this example, there are 30 invalid timestamps in the specified date range.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumInvalidTimeStampsBetween(02/03/1996 00:00:00 , 05/16/1996 00:00:00) = 30

NumOffExceptions

Format

ORDSYS.Calendar.NumOffExceptions(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of off-exceptions within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used in computing the number of off-exceptions.

startDate

Starting date in the range to be checked for off-exceptions.

endDate

Ending date in the range to be checked for off-exceptions.

Usage

startDate and endDate are included in the check for off-exceptions. (For an explanation of off-exceptions and on-exceptions, see Section 2.2.)

If startDate is greater (later) than endDate, an exception is raised.

Example

Return the number of off-exceptions between 02-Feb-1996 and 07-Jul-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get the number of off-exceptions between 02-FEB-1996 and 07-JUL-1996.
 tstDate1 := TO_DATE('02/02/1996');
 tstDate2 := TO_DATE('07/07/1996');
 result  := ORDSYS.Calendar.NumOffExceptions(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumOffExceptions(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. As the last line of the output indicates, there are five off-exceptions in the specified date range (02-Feb-1996 through 07-Jul-1996).

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumOffExceptions(02/02/1996 00:00:00 , 07/07/1996 00:00:00) = 5

NumOnExceptions

Format

ORDSYS.Calendar.NumOnExceptions(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of on-exceptions within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used in computing the number of on-exceptions.

startDate

Starting date in the range to be checked for on-exceptions.

endDate

Ending date in the range to be checked for on-exceptions.

Usage

startDate and endDate are included in the check for on-exceptions. (For an explanation of off-exceptions and on-exceptions, see Section 2.2.)

If startDate is greater (later) than endDate, an exception is raised.

Example

Return the number of on-exceptions between 02-Feb-1996 and 07-Jul-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;
 
 -- Get the number of ON Exceptions between 02-FEB-1996 and 07-JUL-1996.
 tstDate1 := TO_DATE('02/02/1996');
 tstDate2 := TO_DATE('07/07/1996');
 result  := ORDSYS.Calendar.NumOnExceptions(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumOnExceptions(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. As the last line of the output indicates, there are six on-exceptions in the specified date range (02-Feb-1996 through 07-Jul-1996).

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumOnExceptions(02/02/1996 00:00:00 , 07/07/1996 00:00:00) = 6

NumTimeStampsBetween

Format

ORDSYS.Calendar.NumTimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of valid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for invalid timestamps.

endDate

Ending date in the range to be checked for invalid timestamps.

Usage

A timestamp is invalid if one or more of the following conditions are true:

startDate and endDate are included in the check for valid timestamps.

If there are no valid timestamps in the date range, the function returns 0 (zero).

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with NumInvalidTimeStampsBetween, which returns the number of invalid timestamps in a date range.

Example

Return the number of valid timestamps between 03-Feb-1996 and 16-May-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;
 
 -- Get the number of Valid timestamps between 03-FEB-1996 and 16-MAY-1996.
 tstDate1 := TO_DATE('02/03/1996');
 tstDate2 := TO_DATE('05/16/1996');
 result  := ORDSYS.Calendar.NumTimeStampsBetween(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumTimeStampsBetween(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. In this example, there are 74 valid timestamps in the specified date range.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumTimeStampsBetween(02/03/1996 00:00:00 , 05/16/1996 00:00:00) = 74

OffsetDate

Format

ORDSYS.Calendar.OffsetDate(

inputCal IN ORDSYS.ORDTCalendar,

origin IN DATE,

relOffset IN INTEGER

) RETURN DATE;

Description

Given a reference date (origin) and an offset with respect to the origin (relOffset), returns the timestamp corresponding to the offset input.

Parameters

inputCal

Calendar from which the date is to be returned.

origin

The date to which the offset value (relOffset) is to be applied in computing the returned date.

relOffset

The relative offset of the returned date with respect to the origin.

Usage

The function returns the date of the timestamp at the relOffset number of timestamps from the origin date. If relOffset is positive, the returned date is later than origin; if relOffset is negative, the returned date is earlier than origin. If relOffset is zero (0), the returned date is origin if origin is a valid date; however, if relOffset is zero (0) and origin is not a valid date, the function returns NULL.

For example, assume a Monday through Friday business day calendar for 1997 with 04-Jul-1997 (Friday) defined as an off-exception, and assume that origin is 02-Jul-1997 (Wednesday):

If the origin date is not in the calendar (inputCal), the next later date is used if relOffset is positive or zero, and the next earlier date is used if relOffset is negative. Using the calendar in the preceding example, if origin is specified as 04-Jul-1997 and if relOffset = 2, then 07-Jul-1997 (Monday, the next business day) is used as origin, and the returned date is 09-Jul-1997 (Wednesday).

If the calendar pattern is empty or null, an exception is raised.

Example

Get the dates 20 timestamps later and 20 timestamps earlier than 03-Mar-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
resultDate date;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Offset 03-MAR-1996 by 20.
 tstDate1 := TO_DATE('03/03/1996');
 relOffset := 20;
 resultDate := ORDSYS.Calendar.OffsetDate(tstCal, tstDate1, relOffset);
 DBMS_OUTPUT.PUT_LINE('OffsetDate(' || tstDate1 || ' , ' || relOffset
                                                 || ') = ' || resultDate);
 DBMS_OUTPUT.NEW_LINE;

 -- Offset 03-MAR-1996 by -20.
 tstDate1 := TO_DATE('03/03/1996');
 relOffset := -20;
 resultDate := ORDSYS.Calendar.OffsetDate(tstCal, tstDate1, relOffset);
 DBMS_OUTPUT.PUT_LINE('OffsetDate(' || tstDate1 || ' , ' || relOffset
                                                 || ') = ' || resultDate);
 DBMS_OUTPUT.NEW_LINE;

END;
/

This example might produce the following output. In this example, 29-Mar-1996 is 20 timestamps later than 03-Mar-1996, and 05-Feb-1996 is 20 timestamps earlier than 03-Mar-1996.

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

OffsetDate(03/03/1996 00:00:00 , 20) = 03/29/1996 00:00:00

OffsetDate(03/03/1996 00:00:00 , -20) = 02/05/1996 00:00:00

Quarter

Format

ORDSYS.Calendar.Quarter(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of quarter, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Example

Insert into the stockdemo_calendars table a calendar of quarter frequency with a calendar name of Quarterly and an anchor date of 01-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Quarter(
       'Quarterly', 
       (to_date('01-01-97','MM-DD-YY'))));

Second

Format

ORDSYS.Calendar.Second(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of second, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Example

Insert into the stockdemo_calendars table a calendar of second frequency with a calendar name of Second and an anchor date of 01-Jan-1997 (at midnight). The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Second(
       'Second', 
       (to_date('01-01-97','MM-DD-YY'))));

Semi_annual

Format

ORDSYS.Calendar.Semi_annual(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of semi_annual, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Example

Insert into the stockdemo_calendars table a calendar of semi_annual frequency with a calendar name of Semi_annual and an anchor date of 01-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Semi_annual(
       'Semi_annual', 
       (to_date('01-01-97','MM-DD-YY'))));

Semi_monthly

Format

ORDSYS.Calendar.Semi_monthly(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of semi_monthly, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. Must be the 1st or 16th day of a month. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Examples

Insert into the stockdemo_calendars table a calendar of semi_monthly frequency with a calendar name of Semi_monthly and an anchor date of 01-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Semi_monthly(
       'Semi_monthly', 
       (to_date('01-01-97','MM-DD-YY'))));

Return the sum of the daily trade volume for stock SAMCO for each semimonthly period in the entire time series. For scaling, use a semimonthly calendar with a null name, an anchor date of 01-Jan-2001 (the default), no date boundaries (minDate or maxDate), and no exceptions.

SELECT * FROM THE
  (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                 ORDSYS.TimeScale.ScaleupSum(
                   ts.volume,
                   ORDSYS.Calendar.Semi_monthly() ))
              AS ORDSYS.ORDTNumTab)
       FROM TSDEV.stockdemo_ts ts
       WHERE ts.ticker='SAMCO');

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
11/01/96     6403150
11/16/96     3803850
12/01/96     1894200
12/16/96     1825250
4 rows selected.

SetPrecision

Format

ORDSYS.Calendar.SetPrecision(

cal ORDSYS.ORDTCalendar

timestamp IN DATE,

) RETURN DATE;

Description

Given a calendar and a timestamp, returns a timestamp that reflects the level of precision implied by the frequency of the specified calendar.

Parameters

cal

Calendar whose frequency is to be applied in setting the precision.

timestamp

Timestamp whose precision is to be set.

Usage

The returned timestamp reflects the precision implied by the frequency, as explained in Section 2.2.2. For example, if the input timestamp is 29-Dec-1997 12:45:00 and the frequency is 6 (month), the returned timestamp is 01-Dec-1997 00:00:00. Table 4-1 shows the frequencies, their precision conventions, and the resulting precision if an input timestamp of 19-Sep-1997 09:09:09 is supplied.

Table 4-1 SetPrecision and Timestamp of 19-Sep-1997 09:09:09
Frequency (Every:)  Precision Convention  Result 
second   MM-DD-YYYY HH24:MI:SS   09-19-1997 09:09:09  
minute   MM-DD-YYYY HH24:MI:00   09-19-1997 09:09:00  
hour   MM-DD-YYYY HH24:00:00   09-19-1997 09:00:00  
day   MM-DD-YYYY 00:00:00 (midnight)   09-19-1997 00:00:00  
month   MM-01-YYYY 00:00:00 (midnight of first day of month)   09-01-1997 00:00:00  
year   01-01-YYYY 00:00:00 (midnight of first day of year)   01-01-1997 00:00:00  

If the frequency is not valid, an exception is raised.


Note:

The Release 8.0.4 SetPrecision syntax specifying a timestamp and a frequency (timestamp IN INTEGER, frequency IN INTEGER) is still supported, but will not be supported in a future release.  


Example

Set the precision of an imprecise timestamp (here, a timestamp containing hour, minute, and second values where the calendar has a day frequency):

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
resultDate date;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

-- Set the precision of an imprecise date.
 tstDate1 := TO_DATE('03/03/1996 01:01:01');
 resultDate := ORDSYS.Calendar.SetPrecision(tstcal, tstDate1);
 DBMS_OUTPUT.PUT_LINE('SetPrecision with timestamp ' || 
                          TO_CHAR(tstDate1) ||
                          ' and frequency ' || tstCal.frequency);
 DBMS_OUTPUT.PUT_LINE(' returns: ' || TO_CHAR(resultDate) );
END;
/

This example might produce the following output. In this example, the hour, minute, and second components of the timestamp are set to zeroes because the calendar frequency is 4 (day).

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/96 00:00:00
 MaxDate = 12/31/96 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/96 00:00:00
 onExceptions  :
     01/21/96 00:00:00     02/03/96 00:00:00     03/24/96 00:00:00
     04/27/96 00:00:00     05/19/96 00:00:00     06/23/96 00:00:00
     07/07/96 00:00:00     08/04/96 00:00:00     09/15/96 00:00:00
 offExceptions :
     01/08/96 00:00:00     02/02/96 00:00:00     03/05/96 00:00:00
     04/04/96 00:00:00     05/08/96 00:00:00     06/25/96 00:00:00
     07/09/96 00:00:00

SetPrecision with timestamp 03/03/96 01:01:01 and frequency 4
 returns: 03/03/96 00:00:00

Ten_day

Format

ORDSYS.Calendar.Ten_day(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of 10-day, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. Must be the 1st, 11th, or 21st day of a month. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Examples

Insert into the stockdemo_calendars table a calendar of 10-day frequency with a calendar name of Ten_day and an anchor date of 01-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Ten_day(
       'Ten_day', 
       (to_date('01-01-97','MM-DD-YY'))));

Return the sum of the daily trade volume for stock SAMCO for each 10-day period in the entire time series. For scaling, use a 10-day calendar with a null name, an anchor date of 01-Jan-2001 (the default), no date boundaries (minDate or maxDate), and no exceptions.

SELECT * FROM THE
  (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                 ORDSYS.TimeScale.ScaleupSum(
                   ts.volume,
                   ORDSYS.Calendar.Ten_day() ))
              AS ORDSYS.ORDTNumTab)
       FROM TSDEV.stockdemo_ts ts
       WHERE ts.ticker='SAMCO');

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
11/01/96      361600
11/11/96     7281200
11/21/96     2564200
12/01/96     1433850
12/11/96     1437800
12/21/96      847800
6 rows selected.

TimeStampsBetween

Format

ORDSYS.Calendar.TimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN ORDSYS.ORDTDateTab;

Description

Given starting and ending input timestamps, returns a table (ORDTDateTab) containing the valid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for valid timestamps.

endDate

Ending date in the range to be checked for valid timestamps.

Usage

A timestamp is invalid if one or more of the following conditions are true:

startDate and endDate are included in the check for valid timestamps.

If there are no valid timestamps in the date range, the function returns an empty ORDTDateTab.

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with InvalidTimeStampsBetween, which returns a table containing the invalid timestamps in a date range.

Example

Return a table of valid timestamps between 03-Mar-1996 and 03-Jun-1996 in the GENERIC-CAL1 calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
resultDTab ORDSYS.ordtDateTab;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get all the valid timestamps between 03-MAR-1996 and 03-JUN-1996.
 tstDate1 := TO_DATE('03/03/1996');
 tstDate2 := TO_DATE('06/03/1996');
 resultDTab := ORDSYS.Calendar.TimeStampsBetween(tstCal, tstDate1, tstDate2);
 SELECT ORDSYS.TimeSeries.Display(resultDTab, 'Valid timestamps') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output:

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/96 00:00:00
 MaxDate = 12/31/96 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/96 00:00:00
 onExceptions  :
     01/21/96 00:00:00     02/03/96 00:00:00     03/24/96 00:00:00
     04/27/96 00:00:00     05/19/96 00:00:00     06/23/96 00:00:00
     07/07/96 00:00:00     08/04/96 00:00:00     09/15/96 00:00:00
 offExceptions :
     01/08/96 00:00:00     02/02/96 00:00:00     03/05/96 00:00:00
     04/04/96 00:00:00     05/08/96 00:00:00     06/25/96 00:00:00
     07/09/96 00:00:00


Valid timestamps :

     03/04/96 00:00:00     03/06/96 00:00:00     03/07/96 00:00:00
     03/08/96 00:00:00     03/11/96 00:00:00     03/12/96 00:00:00
     03/13/96 00:00:00     03/14/96 00:00:00     03/15/96 00:00:00
     03/18/96 00:00:00     03/19/96 00:00:00     03/20/96 00:00:00
     03/21/96 00:00:00     03/22/96 00:00:00     03/24/96 00:00:00
     03/25/96 00:00:00     03/26/96 00:00:00     03/27/96 00:00:00
     03/28/96 00:00:00     03/29/96 00:00:00     04/01/96 00:00:00
     04/02/96 00:00:00     04/03/96 00:00:00     04/05/96 00:00:00
     04/08/96 00:00:00     04/09/96 00:00:00     04/10/96 00:00:00
     04/11/96 00:00:00     04/12/96 00:00:00     04/15/96 00:00:00
     04/16/96 00:00:00     04/17/96 00:00:00     04/18/96 00:00:00
     04/19/96 00:00:00     04/22/96 00:00:00     04/23/96 00:00:00
     04/24/96 00:00:00     04/25/96 00:00:00     04/26/96 00:00:00
     04/27/96 00:00:00     04/29/96 00:00:00     04/30/96 00:00:00
     05/01/96 00:00:00     05/02/96 00:00:00     05/03/96 00:00:00
     05/06/96 00:00:00     05/07/96 00:00:00     05/09/96 00:00:00
     05/10/96 00:00:00     05/13/96 00:00:00     05/14/96 00:00:00
     05/15/96 00:00:00     05/16/96 00:00:00     05/17/96 00:00:00
     05/19/96 00:00:00     05/20/96 00:00:00     05/21/96 00:00:00
     05/22/96 00:00:00     05/23/96 00:00:00     05/24/96 00:00:00
     05/27/96 00:00:00     05/28/96 00:00:00     05/29/96 00:00:00
     05/30/96 00:00:00     05/31/96 00:00:00     06/03/96 00:00:00

Section 3.8.3 contains an example showing the use of TimeStampsBetween to create a time series for use with the DeriveExceptions function.


UnionCals

Format

ORDSYS.Calendar.UnionCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar

) RETURN ORDSYS.ORDTCalendar;

Description

Returns a calendar that is the union of two input calendars.

Parameters

cal1

The first calendar on which the union operation is to be performed.

cal2

The second calendar on which the union operation is to be performed.

Usage

The function performs a union of the two input calendars, as follows:

If the frequencies of the two calendars are not equal, the function returns NULL.

Contrast this function with IntersectCals, which intersects two calendars.

Example

Perform a union of two calendars:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM TSDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Union tstCal1 and tstCal2.
 resultCal := ORDSYS.Calendar.Unioncals(tstCal1, tstCal2);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of UnionCals') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output:

Calendar Name = GENERIC-CAL1
 Frequency = 4 (day)
 MinDate = 01/01/96 00:00:00
 MaxDate = 12/31/96 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/96 00:00:00
 onExceptions  :
     01/21/96 00:00:00     02/03/96 00:00:00     03/24/96 00:00:00
     04/27/96 00:00:00     05/19/96 00:00:00     06/23/96 00:00:00
     07/07/96 00:00:00     08/04/96 00:00:00     09/15/96 00:00:00
 offExceptions :
     01/08/96 00:00:00     02/02/96 00:00:00     03/05/96 00:00:00
     04/04/96 00:00:00     05/08/96 00:00:00     06/25/96 00:00:00
     07/09/96 00:00:00

Calendar Name = GENERIC-CAL2
 Frequency = 4 (day)
 MinDate = 01/01/96 00:00:00
 MaxDate = 12/31/97 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/96 00:00:00
 onExceptions  :
     07/07/96 00:00:00     08/04/96 00:00:00     09/15/96 00:00:00
     10/13/96 00:00:00     11/10/96 00:00:00     12/14/96 00:00:00
     01/04/97 00:00:00     02/09/97 00:00:00     03/08/97 00:00:00
     04/05/97 00:00:00     05/11/97 00:00:00     06/08/97 00:00:00
 offExceptions :
     07/09/96 00:00:00     08/05/96 00:00:00     09/10/96 00:00:00
     10/23/96 00:00:00     11/19/96 00:00:00     12/12/96 00:00:00
     01/01/97 00:00:00     02/12/97 00:00:00     03/04/97 00:00:00
     04/07/97 00:00:00     05/05/97 00:00:00     06/09/97 00:00:00

result of UnionCals :

 Frequency = 4 (day)
 MinDate = 01/01/96 00:00:00
 MaxDate = 12/31/96 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/96 00:00:00
 onExceptions  :
     01/21/96 00:00:00     02/03/96 00:00:00     03/24/96 00:00:00
     04/27/96 00:00:00     05/19/96 00:00:00     06/23/96 00:00:00
     07/07/96 00:00:00     08/04/96 00:00:00     09/15/96 00:00:00
     10/13/96 00:00:00     11/10/96 00:00:00     12/14/96 00:00:00
 offExceptions :
     07/09/96 00:00:00

ValidateCal

Format

ORDSYS.Calendar.ValidateCal(

cal INOUT ORDSYS.ORDTCalendar,

outMessage OUT VARCHAR2,

invOnExc OUT ORDTDateTab,

invOffExc OUT ORDTDateTab,

impOnExc OUT ORDTDateTab,

impOffExc OUT ORDTDateTab

) RETURN BINARY_INTEGER;

Description

Validates a calendar and, if necessary, repairs the calendar and generates information related to the problems and repairs.

Parameters

cal

The calendar to be validated and (if necessary) repaired.

outMessage

Message describing how the calendar was repaired (if the return value = 1) or why the calendar could not be repaired (if the return value = -1).

invOnExc

Table of the invalid on-exceptions found in the calendar.

invOffExc

Table of the invalid off-exceptions found in the calendar.

impOnExc

Table of the imprecise on-exceptions found in the calendar.

impOffExc

Table of the imprecise off-exceptions found in the calendar.

Usage

This function returns one of the following values:

Value   Meaning  

0  

The calendar is valid. No errors were found.  

1  

Correctable errors were found and corrected. The resulting calendar is valid.  

-1  

Uncorrectable errors were found. The calendar is not valid.  

Errors in the input calendar make it invalid. Depending on the error, it may be correctable or uncorrectable. Correctable errors are repaired by the ValidateCal function. If all errors are correctable, the resulting calendar is valid.

For a calendar to be valid, all timestamps in the off-exception and on-exception lists must be consistent with the defined pattern for the calendar. If one or more exception timestamps are not consistent with the pattern, the calendar is invalid. For example, if 04-Jan-1997 (Saturday) is in the off-exception list of a calendar whose pattern includes only Mondays through Fridays as normal business days, 04-Jan-1997 is an invalid off-exception (because as a Saturday it would normally be an "off" day).

Imprecise exception timestamps are repaired. For an explanation of precision, see Section 2.2.2.

Table 4-2 lists correctable errors and the repair actions taken by the ValidateCal function.

Table 4-2 Errors Repaired by ValidateCal
Error  Repair Action 

Imprecise anchor date  

The precision is adjusted.  

Character other than 1 or 0 in the pattern  

All pattern characters other than 0 or 1 are set to 1.  

Imprecise date  

The precision is adjusted.  

Superfluous date (for example, a regular valid date in the on-exceptions list)  

The date is removed from the exceptions list.  

Null date  

The date is removed from the calendar.  

Unsorted dates  

The dates are sorted.  

Duplicate dates in the on-exceptions or off-exceptions list  

Duplicates are removed; the date appears only once in the list.  

Date appearing in both the on-exceptions and off-exceptions lists  

The date is removed from the inappropriate list, depending on the pattern and the anchor date.  

Date outside the date range of the calendar  

The date is removed from the exceptions list.  

The following errors are not correctable. The function returns -1 if one or more of these errors are found:

If the function returns -1, you should not use the calendar until you have fixed the errors that ValidateCal could not fix. Then use ValidateCal again, and use the calendar only if the function returns 0 or 1.

You can use the DisplayValCal procedure to display the information returned by the ValidateCal function. See the information on DisplayValCal in this chapter.

The IsValidCal function (described in this chapter) checks the validity of the calendar but does not perform any repair operations.

Example

Use the IsValidCal and ValidateCal functions and the DisplayValCal procedure with an invalid calendar:

CONNECT TSUSER/TSUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
outMessage  varchar2(32750);
invOnExc    ORDSYS.ORDTDateTab;
invOffExc   ORDSYS.ORDTDateTab;
impOnExc    ORDSYS.ORDTDateTab;
impOffExc   ORDSYS.ORDTDateTab;
dummyval     integer;
validFlag     integer;
tstCal1     ORDSYS.ORDTCalendar := 
               ORDSYS.ORDTCalendar(
                  0,
                  'CALENDAR MYCAL',
                  4, 
                  ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1,1,1,1,1,0,0),
                                              TO_DATE('01-08-1996 01:01:01')),
                  TO_DATE('01-01-1975'),
		  TO_DATE('01-01-1999'),
                  ORDSYS.ORDTExceptions(
           TO_DATE('02-03-1969'), -- Date < minDate,
           TO_DATE('02-14-1969'), -- Date < minDate,
           TO_DATE('02-03-1999'), -- Date > maxDate,
           TO_DATE('02-17-1999'), -- Date > maxDate,
           TO_DATE('12-31-1995'), -- Maps to 0 in pattern (Sunday)
           TO_DATE('01-13-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-24-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('03-30-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-02-1996 01:01:01'), -- Imprecise
           TO_DATE('03-04-1996 01:01:01'), -- Imprecise 
           TO_DATE('04-05-1996 02:02:02'), -- Imprecise
           TO_DATE('03-25-1996'), -- Valid off-exception
           TO_DATE('01-22-1996'), -- Valid, but out of sequence
           TO_DATE('02-12-1996'),  
           TO_DATE('04-30-1996'),  
           NULL,                  -- Null date
           TO_DATE('02-12-1996'), -- Duplicate date within OFFs
           NULL,                  -- Null date
           TO_DATE('04-30-1996'), -- Duplicate off-exception
           NULL,                  -- Null date
           TO_DATE('03-25-1996'), -- Duplicate off-exception
           TO_DATE('01-22-1996'), -- Duplicate off-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('02-02-1996'),  
           TO_DATE('03-04-1996'),
           TO_DATE('05-06-1997')),
		    ORDSYS.ORDTExceptions(
           TO_DATE('02-08-1969'), -- Date < minDate,
           TO_DATE('02-15-1969'), -- Date < minDate,
           TO_DATE('02-13-1999'), -- Date > maxDate,
           TO_DATE('02-20-1999'), -- Date > maxDate,
           TO_DATE('01-03-1996'), -- Maps to 1 in pattern (Wednesday)
           TO_DATE('02-19-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-18-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('05-27-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-23-1996 01:01:01'), -- Imprecise
           TO_DATE('02-18-1996 01:01:01'), -- Imprecise
           TO_DATE('05-26-1996 01:01:01'), -- Imprecise
           TO_DATE('01-13-1996'), -- Valid on-exception
           TO_DATE('01-14-1996'), -- Valid on-exception
           NULL,                  -- Null date
           NULL,                  -- Null date
           TO_DATE('02-24-1996'), -- Valid on-exception
           TO_DATE('03-23-1996'), -- Valid on-exception
           TO_DATE('01-13-1996'), -- Duplicate on-exception
           TO_DATE('01-14-1996'), -- Duplicate on-exception
           TO_DATE('02-24-1996'), -- Duplicate on-exception
           TO_DATE('03-23-1996'), -- Duplicate on-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('01-06-1996'), -- Valid, but out of sequence
           TO_DATE('02-03-1996'),
           TO_DATE('05-04-1997'))
                  );
BEGIN
   SELECT ORDSYS.TIMESERIES.Display(tstCal1, 'tstCal1') INTO dummyval
   FROM dual;
   validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal1);
   IF(validFlag = 0)
   THEN
       validFlag := ORDSYS.CALENDAR.ValidateCal(
                tstCal1, outMessage, invOnExc, invOffExc, impOnExc, impOffExc
                );
 
       ORDSYS.TIMESERIES.DisplayValCal(
             validFlag,
             outMessage,
             invOnExc,
             invOffExc,
             impOnExc,
             impOffExc,
             tstCal1,
             'Your Message'
             );
   END IF;
END;
/

This example might produce the following output:

tstCal1 :

Calendar Name = CALENDAR MYCAL
 Frequency = 4 (day)
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 01:01:01
 onExceptions  :
     02/08/1969 00:00:00     02/15/1969 00:00:00     02/13/1999 00:00:00
     02/20/1999 00:00:00     01/03/1996 00:00:00     02/19/1996 00:00:00
     03/18/1996 00:00:00     05/27/1996 00:00:00     03/23/1996 01:01:01
     02/18/1996 01:01:01     05/26/1996 01:01:01     01/13/1996 00:00:00
     01/14/1996 00:00:00
     02/24/1996 00:00:00     03/23/1996 00:00:00     01/13/1996 00:00:00
     01/14/1996 00:00:00     02/24/1996 00:00:00     03/23/1996 00:00:00
     01/17/1996 00:00:00     05/28/1996 00:00:00     06/18/1996 00:00:00
     04/23/1996 00:00:00     01/06/1996 00:00:00     02/03/1996 00:00:00
     05/04/1997 00:00:00
 offExceptions :
     02/03/1969 00:00:00     02/14/1969 00:00:00     02/03/1999 00:00:00
     02/17/1999 00:00:00     12/31/1995 00:00:00     01/13/1996 00:00:00
     02/24/1996 00:00:00     03/30/1996 00:00:00     02/02/1996 01:01:01
     03/04/1996 01:01:01     04/05/1996 02:02:02     03/25/1996 00:00:00
     01/22/1996 00:00:00     02/12/1996 00:00:00     04/30/1996 00:00:00
          02/12/1996 00:00:00
     04/30/1996 00:00:00          03/25/1996 00:00:00
     01/22/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00     02/02/1996 00:00:00
     03/04/1996 00:00:00     05/06/1997 00:00:00

DisplayValCal Your Message:

TS-WRN: the input calendar has rectifiable errors. See the message for details

message output by validateCal:

TS-WRN: fixed precision of the pattern anchor date
TS-WRN: removed superfluous dates in the on exception list (refer invalidOnExc)
TS-WRN: fixed imprecise dates in the on exception list (refer impreciseOnExc)
TS-WRN: removed null dates in the on exception list
TS-WRN: sorted the on exceptions list
TS-WRN: removed duplicate dates in the on exceptions list
TS-WRN: removed superfluous dates in off exceptions list (refer invalidOffExc)
TS-WRN: fixed imprecise dates in the off exception list (refer impreciseOffExc)
TS-WRN: removed null dates in the off exception list
TS-WRN: sorted the off exceptions list
TS-WRN: removed duplicate dates in the off exceptions list
TS-WRN: the on exceptions list was trimmed between calendar minDate & maxDate
TS-WRN: the off exceptions list was trimmed between calendar minDate & maxDate

list of invalid on exceptions :

     01/03/1996 00:00:00     02/19/1996 00:00:00     03/18/1996 00:00:00
     05/27/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00

list of invalid off exceptions :

     12/31/1995 00:00:00     01/13/1996 00:00:00     02/24/1996 00:00:00
     03/30/1996 00:00:00

list of imprecise on exceptions :

     03/23/1996 01:01:01     02/18/1996 01:01:01     05/26/1996 01:01:01

list of imprecise off exceptions :

     02/02/1996 01:01:01     03/04/1996 01:01:01     04/05/1996 02:02:02

the validated calendar :
                         
Calendar Name = CALENDAR MYCAL
 Frequency = 4 (day)
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1,1,1,1,1,0,0
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     01/06/1996 00:00:00     01/13/1996 00:00:00     01/14/1996 00:00:00
     02/03/1996 00:00:00     02/18/1996 00:00:00     02/24/1996 00:00:00
     03/23/1996 00:00:00     05/26/1996 00:00:00     05/04/1997 00:00:00
 offExceptions :
     01/17/1996 00:00:00     01/22/1996 00:00:00     02/02/1996 00:00:00
     02/12/1996 00:00:00     03/04/1996 00:00:00     03/25/1996 00:00:00
     04/05/1996 00:00:00     04/23/1996 00:00:00     04/30/1996 00:00:00
     05/28/1996 00:00:00     06/18/1996 00:00:00     05/06/1997 00:00:00

Week

Format

ORDSYS.Calendar.Week(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of week, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Examples

Insert into the stockdemo_calendars table a calendar of week frequency with a calendar name of Weekly and an anchor date of 05-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Week(
       'Weekly', 
       (to_date('01-05-97','MM-DD-YY'))));

Return the sum of the daily trade volume for stock SAMCO for each week in the entire time series. For scaling, use a weekly calendar with a null name, an anchor date of 01-Jan-2001 (the default), no date boundaries (minDate or maxDate), and no exceptions.

SELECT * FROM THE
  (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                 ORDSYS.TimeScale.ScaleupSum(
                   ts.volume,
                   ORDSYS.Calendar.Week() ))
              AS ORDSYS.ORDTNumTab)
       FROM TSDEV.stockdemo_ts ts
       WHERE ts.ticker='SAMCO');

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
10/28/96       41550
11/04/96      320050
11/11/96     6041550
11/18/96     1909850
11/25/96     1894000
12/02/96     1051350
12/09/96      842850
12/16/96      977450
12/23/96      430800
12/30/96      417000
10 rows selected.

Year

Format

ORDSYS.Calendar.Year(

[calname VARCHAR2]

[, anchorDate DATE]

) RETURN ORDSYS.ORDTCalendar;

Description

Creates a calendar with a frequency of year, a pattern of '1' (all timestamps included), no lower or upper boundary dates (minDate or maxDate), no off-exceptions or on-exceptions, a specified or default (null) name, and a specified or default anchor date.

Parameters

calname

The name of the calendar. If calname is not specified, the calendar name is null.

anchorDate

The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).

Usage

This function provides a convenient alternative to providing a complete calendar definition when you are creating a calendar. If you need to modify the definition later, you can do so (for example, using the InsertExceptions function to specify exceptions).

For an explanation of calendar concepts (such as frequency, pattern, anchor date, and exceptions), see Section 2.2.

The following functions create a calendar with a frequency corresponding to the function name: Day, Hour, Minute, Month, Quarter, Second, Semi_annual, Semi_monthly, Ten_day, Week, and Year.

Example

Insert into the stockdemo_calendars table a calendar of year frequency with a calendar name of Yearly and an anchor date of 01-Jan-1997. The calendar has no date boundaries (minDate or maxDate) or exceptions.

INSERT INTO stockdemo_calendars 
	VALUES(
   ORDSYS.Calendar.Year(
       'Yearly', 
       (to_date('01-01-97','MM-DD-YY'))));




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index