Release 8.1.5
A67294-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Time Series User's Guide Release 8.1.5 A67294-01 |
|
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:
select CAST(TimeSeries.ExtractTable(close) AS ORDTNumTab) select cast(TIMESERIES.extracttable(close) as ordtnumtab) select cast(TiMeSeRiEs.eXtRaCtTaBlE(ClosE) As ordtNUMtab)
ORDSYS.Calendar.CombineCals(
cal1 ORDSYS.ORDTCalendar,
cal2 ORDSYS.ORDTCalendar,
[startDate DATE,
endDate DATE,]
equalFlag OUT INTEGER
) RETURN ORDSYS.ORDTCalendar;
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).
The first calendar to be combined.
The second calendar to be combined.
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.
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.
Contains 1 if the input calendars are equal, and 0 if the input calendars are not equal.
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:
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
ORDSYS.Calendar.Day(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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'))));
ORDSYS.Calendar.DeleteExceptions(
or
ORDSYS.Calendar.DeleteExceptions(
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.
The calendar from which one or more exceptions are to be deleted.
The date to be deleted from the exceptions of the calendar.
A table of dates to be deleted from the exceptions of the calendar.
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.
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
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
);
Displays the results returned by the ValidateCal function.
The return value from the ValidateCal function call:
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).
Table of the invalid on-exceptions found in the calendar.
Table of the invalid off-exceptions found in the calendar.
Table of the imprecise on-exceptions found in the calendar.
Table of the imprecise off-exceptions found in the calendar.
The calendar returned by ValidateCal (repaired if necessary).
Optional message.
This procedure is intended to be used with the ValidateCal function. See the information on ValidateCal in this chapter.
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
ORDSYS.Calendar.EqualCals(
cal1 ORDSYS.ORDTCalendar,
cal2 ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN BINARY_INTEGER;
Checks if two calendars (completely or within a specified date range) are equal.
The first calendar to be checked.
The second calendar to be checked.
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.
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.
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.
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
ORDSYS.Calendar.GenDateRangeTab(
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).
The input calendar.
Starting date for returning date ranges. If startDate is not specified, the starting date is the starting date for the calendar (minDate).
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).
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.
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.
ORDSYS.TimeSeries.GetIntervalEnd(
Given a Calendar and an input timestamp (inputDate), returns the end of the interval that includes the input timestamp.
The input calendar.
Timestamp for which the end of the interval is to be returned.
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.
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
ORDSYS.TimeSeries.GetIntervalStart(
Given a Calendar and an input timestamp (inputDate), returns the start of the interval that includes the input timestamp.
The input calendar.
Timestamp for which the start of the interval is to be returned.
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.
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
ORDSYS.TimeSeries.GetOffset(
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.
The input calendar.
Date from which the offset is to be computed.
Date whose offset from origin_date is to be returned.
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.
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
ORDSYS.Calendar.Hour(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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'))));
ORDSYS.Calendar.InsertExceptions(
or
ORDSYS.Calendar.InsertExceptions(
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.
The calendar into which one or more exceptions are to be inserted.
The date to be inserted as an exception in the calendar.
A table of dates to be inserted as exceptions in the calendar.
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.
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
ORDSYS.Calendar.IntersectCals(
Returns the intersection of two calendars.
The first calendar to be intersected.
The second calendar to be intersected.
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:
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
ORDSYS.Calendar.InvalidTimeStampsBetween(
Given starting and ending input timestamps, returns a table (ORDTDateTab) containing the invalid timestamps within that range according to the specified calendar.
The calendar to be used to determine whether a timestamp is valid or invalid.
Starting date in the range to be checked for invalid timestamps.
Ending date in the range to be checked for invalid timestamps.
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.
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
ORDSYS.Calendar.IsValidCal(
Returns 1 if a calendar is valid and 0 if a calendar is not valid.
The calendar to be checked for validity.
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:
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
ORDSYS.Calendar.IsValidDate(
Checks whether an input date is valid or invalid according to the specified calendar.
The calendar to be used to determine whether the input timestamp is valid or invalid.
The timestamp to be checked for validity according to the calendar.
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:
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
ORDSYS.Calendar.Minute(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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'))));
ORDSYS.Calendar.Month(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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.
ORDSYS.Calendar.NumInvalidTimeStampsBetween(
Given starting and ending input timestamps, returns the number of invalid timestamps within that range according to the specified calendar.
The calendar to be used to determine whether a timestamp is valid or invalid.
Starting date in the range to be checked for invalid timestamps.
Ending date in the range to be checked for invalid timestamps.
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.
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
ORDSYS.Calendar.NumOffExceptions(
Given starting and ending input timestamps, returns the number of off-exceptions within that range according to the specified calendar.
The calendar to be used in computing the number of off-exceptions.
Starting date in the range to be checked for off-exceptions.
Ending date in the range to be checked for off-exceptions.
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.
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
ORDSYS.Calendar.NumOnExceptions(
inputCal IN ORDSYS.ORDTCalendar,
startDate IN DATE,
endDate IN DATE
) RETURN INTEGER;
Given starting and ending input timestamps, returns the number of on-exceptions within that range according to the specified calendar.
The calendar to be used in computing the number of on-exceptions.
Starting date in the range to be checked for on-exceptions.
Ending date in the range to be checked for on-exceptions.
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.
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
ORDSYS.Calendar.NumTimeStampsBetween(
Given starting and ending input timestamps, returns the number of valid timestamps within that range according to the specified calendar.
The calendar to be used to determine whether a timestamp is valid or invalid.
Starting date in the range to be checked for invalid timestamps.
Ending date in the range to be checked for invalid timestamps.
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.
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
ORDSYS.Calendar.OffsetDate(
Given a reference date (origin) and an offset with respect to the origin (relOffset), returns the timestamp corresponding to the offset input.
Calendar from which the date is to be returned.
The date to which the offset value (relOffset) is to be applied in computing the returned date.
The relative offset of the returned date with respect to the origin.
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.
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
ORDSYS.Calendar.Quarter(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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'))));
ORDSYS.Calendar.Second(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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'))));
ORDSYS.Calendar.Semi_annual(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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'))));
ORDSYS.Calendar.Semi_monthly(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
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).
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.
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.
ORDSYS.Calendar.SetPrecision(
Given a calendar and a timestamp, returns a timestamp that reflects the level of precision implied by the frequency of the specified calendar.
Calendar whose frequency is to be applied in setting the precision.
Timestamp whose precision is to be set.
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.
If the frequency is not valid, an exception is raised.
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
ORDSYS.Calendar.Ten_day(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
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).
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.
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.
ORDSYS.Calendar.TimeStampsBetween(
Given starting and ending input timestamps, returns a table (ORDTDateTab) containing the valid timestamps within that range according to the specified calendar.
The calendar to be used to determine whether a timestamp is valid or invalid.
Starting date in the range to be checked for valid timestamps.
Ending date in the range to be checked for valid timestamps.
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.
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.
ORDSYS.Calendar.UnionCals(
Returns a calendar that is the union of two input calendars.
The first calendar on which the union operation is to be performed.
The second calendar on which the union operation is to be performed.
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.
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
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;
Validates a calendar and, if necessary, repairs the calendar and generates information related to the problems and repairs.
The calendar to be validated and (if necessary) repaired.
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).
Table of the invalid on-exceptions found in the calendar.
Table of the invalid off-exceptions found in the calendar.
Table of the imprecise on-exceptions found in the calendar.
Table of the imprecise off-exceptions found in the calendar.
This function returns one of the following values:
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.
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.
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
ORDSYS.Calendar.Week(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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.
ORDSYS.Calendar.Year(
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.
The name of the calendar. If calname is not specified, the calendar name is null.
The anchor date for the calendar pattern. If anchorDate is not specified, the anchor date is 01-Jan-2001 (a Monday).
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.
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'))));
