Oracle8 Time Series Cartridge User's Guide Release 8.0.4 A5750101 

The Oracle8 Time Series Cartridge library consists of:
Two separate reference chapters are provided for the functions because the functions described in each are typically done at different times in the application development cycle and by people performing different job roles:
Syntax notes:
select CAST(TimeSeries.ExtractTable(close) AS ORDTNumTab) select cast(TIMESERIES.extracttable(close) as ordtnumtab) select cast(TiMeSeRiEs.eXtRaCtTaBlE(ClosE) As ordtNUMtab)
Usage note:
All time series functions accept both references and instances as parameters. (For example, an ORDTNumSeriesIOTRef parameter could also be ORDTNumSeries.) All time series functions return instances. Thus, if you nest functions, such as Cmax(Cmax(...), ...), the innermost nesting accepts a reference and outputs an instance, and any other functions in the nesting accept an instance and output an instance.
For an explanation of the referencebased interface, see Section 2.5.2.
ORDSYS.TimeSeries.Cavg(
Given a time series, returns an ORDTNumSeries with each element containing the cumulative average up to and including the corresponding element in the input ORDTNumSeries.
The input time series.
Starting date within the time series for which the cumulative average is to be computed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative average is to be computed. If endDate is specified, startDate must also be specified.
Only nonnull values are considered in computing the cumulative average.
An exception is returned if any of the following conditions is true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative average is computed.
Return the cumulative average of the closing price of stock ACME for November 1996:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cavg(ts.close,to_date('01NOV96','DDMONYY'), to_date('30NOV96','DDMONYY')) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE   01NOV96 59 04NOV96 59.5 05NOV96 60 06NOV96 60.5 07NOV96 61 08NOV96 61.5 11NOV96 62 12NOV96 62.5 13NOV96 63 14NOV96 63.5 15NOV96 64 18NOV96 64.5 19NOV96 65 20NOV96 65.5 21NOV96 66 22NOV96 66.5 25NOV96 67 26NOV96 67.5 27NOV96 68 29NOV96 68.5 20 rows selected.
ORDSYS.TimeSeries.Cmax(
Given a time series, returns an ORDTNumSeries with each element containing the cumulative maximum up to and including the corresponding element in the input ORDTNumSeries.
The input time series.
Starting date within the time series for which the cumulative maximum is to be returned. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative maximum is to be returned. If endDate is specified, startDate must also be specified.
Only nonnull values are considered in determining the cumulative maximum.
An exception is returned if any of the following conditions is true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative maximum is computed.
Return the cumulative maximum of the closing price of stock ACME for November 1996:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cmax(ts.close,to_date('01NOV96','DDMONYY'), to_date('30NOV96','DDMONYY')) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output. (Note that this output reflects the simplified artificial data in the usage demo database, where the closing price rises one point each day.)
TSTAMP VALUE   01NOV96 59 04NOV96 60 05NOV96 61 06NOV96 62 07NOV96 63 08NOV96 64 11NOV96 65 12NOV96 66 13NOV96 67 14NOV96 68 15NOV96 69 18NOV96 70 19NOV96 71 20NOV96 72 21NOV96 73 22NOV96 74 25NOV96 75 26NOV96 76 27NOV96 77 29NOV96 78 20 rows selected.
ORDSYS.TimeSeries.Cmin(
Given a time series, returns an ORDTNumSeries with each element containing the cumulative minimum up to and including the corresponding element in the input ORDTNumSeries.
The input time series.
Starting date within the time series for which the cumulative minimum is to be returned. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative minimum is to be returned. If endDate is specified, startDate must also be specified.
Only nonnull values are considered in determining the cumulative minimum.
An exception is returned if any of the following conditions is true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative minimum is computed.
Return the cumulative minimum of the closing price of stock ACME for November 1996:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cmin(ts.close,to_date('01NOV96','DDMONYY'), to_date('30NOV96','DDMONYY')) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output. (Note that this output reflects the simplified artificial data in the usage demo database, where the closing price rises one point each day.)
TSTAMP VALUE   01NOV96 59 04NOV96 59 05NOV96 59 06NOV96 59 07NOV96 59 08NOV96 59 11NOV96 59 12NOV96 59 13NOV96 59 14NOV96 59 15NOV96 59 18NOV96 59 19NOV96 59 20NOV96 59 21NOV96 59 22NOV96 59 25NOV96 59 26NOV96 59 27NOV96 59 29NOV96 59 20 rows selected.
ORDSYS.TimeSeries.Cprod(
Given a time series, returns an ORDTNumSeries with each element containing the cumulative product of multiplication up to and including the corresponding element in the input ORDTNumSeries.
The input time series.
Starting date within the time series for which the cumulative product is to be computed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative product is to be computed. If endDate is specified, startDate must also be specified.
Only nonnull values are considered in computing the cumulative product.
An exception is returned if any of the following conditions is true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative product is computed.
Return the cumulative product of the daily volume of stock ACME for the first four trading days of November 1996. (This example is presented merely to illustrate the function; the results of this query have no practical value for financial analysis.)
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Cprod(ts.volume,to_date('01NOV96','DDMONYY'), to_date('06NOV96','DDMONYY')) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE   01NOV96 1000 04NOV96 1000000 05NOV96 1000000000 06NOV96 1.0000E+12 4 rows selected.
ORDSYS.TimeSeries.Csum(
Given a time series, returns an ORDTNumSeries with each element containing the cumulative sum up to and including the corresponding element in the input ORDTNumSeries.
The input time series.
Starting date within the time series for which the cumulative sum is to be computed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the cumulative sum is to be computed. If endDate is specified, startDate must also be specified.
Only nonnull values are considered in computing the cumulative sum.
An exception is returned if any of the following conditions is true:
If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative sum is computed.
Return the cumulative sum of the daily volume of stock ACME for November 1996:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Csum(ts.volume,to_date('01NOV96','DDMONYY'), to_date('30NOV96','DDMONYY')) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE   01NOV96 1000 04NOV96 2000 05NOV96 3000 06NOV96 4000 07NOV96 5000 08NOV96 6000 11NOV96 7000 12NOV96 8000 13NOV96 9000 14NOV96 10000 15NOV96 11000 18NOV96 12000 19NOV96 13000 20NOV96 14000 21NOV96 15000 22NOV96 16000 25NOV96 17000 26NOV96 18000 27NOV96 19000 29NOV96 20000 20 rows selected.
Approach 1:
ORDSYS.TimeSeries.DeriveExceptions(
Approach 2:
ORDSYS.TimeSeries.DeriveExceptions(
or
ORDSYS.TimeSeries.DeriveExceptions(
Derives calendar exceptions from a calendar and a table of dates (Approach 1) or from two time series (Approach 2).
The calendar that contains no exceptions and for which exceptions are to be derived.
The table of dates that includes all dates in the time series (for example, all dates on which stock XYZ traded).
The "reference" time series that contains no exceptions and all valid timestamps from the calendar (for example, all Monday through Friday dates within the date range of the calendar).
The time series that contains the timestamps to be used in deriving the exceptions for the resulting calendar (for example, all dates on which stock XYZ traded).
See Section 2.2.4 for a detailed explanation of the two approaches to using this function.
See Sections 3.3.1 and 3.3.2 for examples of the two approaches to using this function.
ORDSYS.TimeSeries.Display(
Displays various information (see the description of the ts parameter) using DBMS_OUTPUT routines.
The object to be displayed. Because the function is overloaded, this parameter can be any of the following datatypes:
Optional message text to be included in the display heading ("Timeseries dump for <mesg>").
Use the SET SERVEROUTPUT ON statement to view the output of the Display function. However, the default display buffer of 2000 bytes is often too small to display a large time series. In such cases you must use the ENABLE procedure of the DBMS_OUTPUT package to specify a larger display buffer size. For example:
DBMS_OUTPUT.ENABLE(1000000);
You should use Display only for development and debugging. Specify a display buffer larger than 2000 only when necessary, because the display buffer uses shared system resources, and a large value might affect the performance of other users.
Because the Display function uses DBMS_OUTPUT routines, it is subject to the limitations of these routines. These limitations include the following:
Display the output for a query that returns the 10 highest closing prices for stock AONE for the month of January 1996:
SET SERVEROUTPUT ON DECLARE tmp INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSMaxN(close,10, to_date('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY'))) INTO tmp FROM ORDTDEV.stocks_ts WHERE ticker ='AONE'; END; /
This example might produce the following output:
Tab Data:  Date Value 01/24/1996 00:00:00 43.9138 01/25/1996 00:00:00 42.9925 01/31/1996 00:00:00 42.9925 01/26/1996 00:00:00 42.7413 01/30/1996 00:00:00 42.7413 01/29/1996 00:00:00 42.5738 01/23/1996 00:00:00 41.9875 01/22/1996 00:00:00 41.82 01/19/1996 00:00:00 41.485 01/18/1996 00:00:00 40.815 
The preceding example works from both SQL*Plus and the Server Manager (svrmgrl) prompt. The following version of the example works from the Server Manager prompt but not from SQL*Plus:
SET SERVEROUTPUT ON SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSMaxN(close,10, to_date('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY'))) FROM ORDTDEV.stocks_ts WHERE ticker ='AONE';
See the TSMaxN function for an example that returns the same information, but that uses a subquery instead of the Display function.
ORDSYS.TimeSeries.DisplayValTS(
validFlag IN INTEGER,
outMessage IN VARCHAR2,
loDateTab IN ORDSYS.ORDTDateTab,
hiDateTab IN ORDSYS.ORDTDateTab,
impreciseDateTab IN ORDSYS.ORDTDateTab,
duplicateDateTab IN ORDSYS.ORDTDateTab,
extraDateTab IN ORDSYS.ORDTDateTab,
missingDateTab IN ORDSYS.ORDTDateTab,
mesg IN VARCHAR2
);
Displays the results returned by the ValidateTS function.
The return value from the ValidateTS function.
The diagnostic returned by the ValidateTS function.
A table of dates before the starting date of the calendar associated with the time series.
A table of dates after the starting date of the calendar associated with the time series.
A table of the imprecise dates found in the time series.
A table of the duplicate dates (dates that appear more than once in the time series).
A table of dates that are included in the time series but that should be excluded based on the calendar definition (for example, a Saturday timestamp that is in a MondayFriday calendar and that is not an onexception).
A table of dates that are excluded from the time series but that should be included based on the calendar definition (for example, a Wednesday date that is not a holiday in a MondayFriday calendar and for which there is no data). Such dates can be considered as "holes" in the time series.
Optional message.
This procedure is intended to be used with the ValidateTS function. See the information on ValidateTS in this chapter.
The DisplayValTS procedure uses the DBMS_OUTPUT package. See the Usage information for the Display function for limitations relating to the use of DBMS_OUTPUT.
Use the IsValidTS and ValidateTS functions and the DisplayValTS procedure with an invalid time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE numTS ORDSYS.ORDTNumSeries; tempVal integer; retIsValid integer; retValTS integer; loDateTab ORDSYS.ORDTDateTab := NULL; hiDateTab ORDSYS.ORDTDateTab := NULL; impDateTab ORDSYS.ORDTDateTab := NULL; dupDateTab ORDSYS.ORDTDateTab := NULL; extraDateTab ORDSYS.ORDTDateTab := NULL; missingDateTab ORDSYS.ORDTDateTab := NULL; outMesg varchar2(2000); BEGIN  Set the buffer size DBMS_OUTPUT.ENABLE(100000);   NOTE: Here an instance of the time series is materialized  so that it could be modified to generate an invalid time series.  SELECT ORDSYS.TIMESERIES.GetSeries(ts.open) INTO numTS FROM ordtdev.stockdemo_ts ts WHERE ts.ticker = 'ACME';  Example of validating a valid time series. SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = '  retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = '  retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); DBMS_OUTPUT.NEW_LINE;  For illustration let us first create an invalid timeseries.   Here we are adjusting the calendar's minDate and maxDate to avoid  getting a huge list of missing dates.  numTS.cal.minDate := TO_DATE('10/28/1996'); numTS.cal.maxDate := TO_DATE('01/05/1997');  Add Dates Before numTS.cal.minDate numTS.series(10).tstamp := numTS.cal.minDate  1; numTS.series(11).tstamp := numTS.cal.minDate  2;  Add Dates Beyond numTS.cal.maxDate numTS.series(12).tstamp := numTS.cal.maxDate + 1; numTS.series(13).tstamp := numTS.cal.maxDate + 2;  Add some null timestamps numTS.series(14).tstamp := NULL; numTS.series(15).tstamp := NULL;  Add some imprecise dates (some are duplicated) numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24; numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24;  Add some duplicate timestamps numTS.series(19).tstamp := numTS.series(18).tstamp; numTS.series(21).tstamp := numTS.series(20).tstamp;  Add some extra dates in the middle numTS.series(37).tstamp := TO_DATE('12/28/1996'); numTS.series(36).tstamp := TO_DATE('12/29/1996');  Add some holes at the end numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997');  Example of validating an invalid time series. SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = '  retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = '  retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); END; /
This example might produce the following output:
A VALID TIME SERIES : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99  Value returned by IsValid = 1 Value returned by ValidateTS = 1 DisplayValTS: Testing DisplayValTS: TSSUC: the input time series is a valid time series AN INVALID TIME SERIES : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 10/27/1996 00:00:00 68 10/26/1996 00:00:00 69 01/06/1997 00:00:00 70 01/07/1997 00:00:00 71 72 73 11/22/1996 00:00:00 74 11/22/1996 01:00:00 75 11/22/1996 15:00:00 76 11/22/1996 15:00:00 77 11/29/1996 00:00:00 78 11/29/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/29/1996 00:00:00 94 12/28/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 01/04/1997 00:00:00 99  Value returned by IsValid = 0 Value returned by ValidateTS = 0 DisplayValTS: Testing DisplayValTS: TSWRN: the input time series has errors. See the message for details message output by validateTS: TSERR: the input time series is unsorted TSERR: the time series has null timestamps TSERR: the time series has timestamps < calendar minDate (refer LoDateTab) TSERR: the time series has timestamps > calendar maxDate (refer HiDateTab) TSERR: the time series has imprecise timestamps (refer impreciseDateTab) TSERR: the time series has duplicate timestamps (refer DuplicateDateTab) list of dates < calendar minDate  lowDateTab : 10/26/1996 00:00:00 10/27/1996 00:00:00 list of dates > calendar maxDate  hiDateTab : 01/06/1997 00:00:00 01/07/1997 00:00:00 list of imprecise dates  impreciseDateTab : 11/22/1996 01:00:00 11/22/1996 15:00:00 list of duplicate dates  duplicateDateTab : 11/22/1996 15:00:00 11/29/1996 00:00:00 ExtraDateTab : 12/28/1996 00:00:00 12/29/1996 00:00:00 01/04/1997 00:00:00 MissingDateTab : 10/28/1996 00:00:00 10/29/1996 00:00:00 10/30/1996 00:00:00 10/31/1996 00:00:00 11/14/1996 00:00:00 11/15/1996 00:00:00 11/18/1996 00:00:00 11/19/1996 00:00:00 11/20/1996 00:00:00 11/21/1996 00:00:00 11/25/1996 00:00:00 11/26/1996 00:00:00 11/27/1996 00:00:00 12/02/1996 00:00:00 12/23/1996 00:00:00 12/24/1996 00:00:00 12/31/1996 00:00:00 01/01/1997 00:00:00 01/02/1997 00:00:00 01/03/1997 00:00:00
ORDSYS.TimeSeries.ExtractCal(
or
ORDSYS.TimeSeries.ExtractCal(
Given a time series, returns a calendar that is the same as the calendar on which the time series is based.
The input time series.
The function returns a calendar that has the same starting and ending timestamps, pattern, frequency, and exceptions (on and off) as the calendar on which the specified time series is based.
An exception is returned if the time series (ts) is null.
Return a calendar that matches the one on which the time series for the ACME ticker is based:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.ExtractCal(ts.open), 'ExtractCal Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
ExtractCal Results : Calendar Name = BUSINESS96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00
ORDSYS.TimeSeries.ExtractDate(
or
ORDSYS.TimeSeries.ExtractDate(
Given an element in a time series, returns the date.
The time series element for which you want the date.
The time series element must first be identified, such as by using the GetNthElement function.
An exception is returned if the time series element (cell) is null.
Return the date associated with the tenth element in a specified time series:
SELECT to_char( ORDSYS.TimeSeries.ExtractDate( ORDSYS.TimeSeries.GetNthElement(open, 10)), 'MM/DD/YYYY HH24:MI:SS') FROM ORDTDEV.stocks_ts WHERE ticker = 'AONE';
This example might produce the following output:
TO_CHAR(ORDSYS.TIME  01/15/1990 00:00:00 1 row selected.
ORDSYS.TimeSeries.ExtractTable(
or
ORDSYS.TimeSeries.ExtractTable(
Given a time series, returns the time series table (ORDTNumTab or ORDTVarchar2Tab) associated with the time series.
The input time series.
The function returns the time series table (ORDTNumTab or ORDTVarchar2Tab) associated with the time series.
An exception is returned if the time series (ts) is null.
Return the closing prices for stock ACME:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(ts.close) as ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE   01NOV96 59 04NOV96 60 05NOV96 61 ... ... 31DEC96 99 41 rows selected.
ORDSYS.TimeSeries.ExtractValue(
or
ORDSYS.TimeSeries.ExtractValue(
Given an element in a time series, returns the value stored in it.
The time series element for which you want the value.
The time series element must first be identified, such as by using the GetNthElement function.
An exception is returned if the time series element (cell) is null.
Return the value of the tenth opening price in the stocks_ts table:
SELECT ORDSYS.TimeSeries.ExtractValue( ORDSYS.TimeSeries.GetNthElement(open, 10)) FROM ORDTDEV.stocks_ts WHERE ticker = 'AONE';
This example might produce the following output:
ORDSYS.TIM  15.1875 1 row selected.
ORDSYS.TimeSeries.Fill(
Given a time series and optionally a fill type, returns a time series in which values for missing dates are inserted. A missing date is a date that is defined by the calendar and within the time series bounds, but that is not in the current time series.
The input time series.
One of the following integers indicating how missing values are to be filled:
If fill_type is omitted, 0 is assumed.
The function inserts timestamps and associated values for timestamps that are included in a calendar but for which no entries exist in the time series.
The fill_type parameter lets you choose the manner in which missing values will be defaulted. For example, assume that data for 30Jan1997 (Thursday) is missing from a time series and that it should be included because this date is within the calendar definition. Assume the following closing prices for stock XYZ:
The following table shows the closing price that would be inserted for 30Jan1997 with each of the fill_type parameter values:
fill_type  Closing Price for 30Jan1997 

0 
null 
1 
49 
2 
50 
Some potential uses for this function include:
For example, you may want to compare prices for a stock that trades on several stock exchanges, where the exchanges have different trading days.
For example, earnings per share (EPS) is computed quarterly, and stocks trade daily. To compute a priceearnings (PE) ratio, earnings per share is first converted to a daily time series using forward repeat. Then, the daily PE ratio is calculated by dividing the daily price time series value by the corresponding daily EPS time series value.
An exception is returned if the specified fill_type value is not 0, 1, or 2.
Return a time series illustrating each fill_type value:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';  For illustrating Fill we need a timeseries with missing dates.  In the following example, the timeseries 'FOO' has some missing dates  (07DEC1996 and 08DEC1996). Also, note that the calendar associated  with 'FOO' has an 'all one' pattern.  DECLARE tstCal ORDSYS.ORDTCalendar; ts ORDSYS.ordtnumseries := ORDSYS.ordtnumseries( 'FOO', ORDSYS.ORDTCalendar( 0, 'FOO CALENDAR', 4, ORDSYS.ORDTPattern( ORDSYS.ORDTPatternBits(1,1,1,1,1,1,1), TO_DATE('01/07/1996')), TO_DATE('01/01/1996'), TO_DATE('01/01/1997'), ORDSYS.ORDTExceptions(), ORDSYS.ORDTExceptions() ), ORDSYS.ordtnumtab( ORDSYS.ordtnumcell(TO_DATE('12/02/1996'), 1), ORDSYS.ordtnumcell(TO_DATE('12/03/1996'), 2), ORDSYS.ordtnumcell(TO_DATE('12/04/1996'), 3), ORDSYS.ordtnumcell(TO_DATE('12/05/1996'), 4), ORDSYS.ordtnumcell(TO_DATE('12/06/1996'), 5), ORDSYS.ordtnumcell(TO_DATE('12/09/1996'), 6), ORDSYS.ordtnumcell(TO_DATE('12/10/1996'), 7), ORDSYS.ordtnumcell(TO_DATE('12/11/1996'), 8), ORDSYS.ordtnumcell(TO_DATE('12/12/1996'), 9), ORDSYS.ordtnumcell(TO_DATE('12/13/1996'), 10)) ); dummyval INTEGER; BEGIN  Generate a timeseries by from XCORP's high (repeat forward). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 1), 'Fill Forward') INTO dummyval FROM dual;  Generate a timeseries by from XCORP's high (repeat backward). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 2), 'Fill Backward') INTO dummyval FROM dual;  Generate a timeseries by from XCORP's high (null fill). SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Fill(ts, 0), 'Null Fill') INTO dummyval FROM dual; END; /
This example might produce the following output:
Fill Forward : Calendar Data: Calendar Name = FOO CALENDAR Frequency = 4 MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1111111 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : Series Data:  Date Value 12/02/1996 00:00:00 1 12/03/1996 00:00:00 2 12/04/1996 00:00:00 3 12/05/1996 00:00:00 4 12/06/1996 00:00:00 5 12/07/1996 00:00:00 5 12/08/1996 00:00:00 5 12/09/1996 00:00:00 6 12/10/1996 00:00:00 7 12/11/1996 00:00:00 8 12/12/1996 00:00:00 9 12/13/1996 00:00:00 10  Fill Backward : Calendar Data: Calendar Name = FOO CALENDAR Frequency = 4 MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1111111 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : Series Data:  Date Value 12/02/1996 00:00:00 1 12/03/1996 00:00:00 2 12/04/1996 00:00:00 3 12/05/1996 00:00:00 4 12/06/1996 00:00:00 5 12/07/1996 00:00:00 6 12/08/1996 00:00:00 6 12/09/1996 00:00:00 6 12/10/1996 00:00:00 7 12/11/1996 00:00:00 8 12/12/1996 00:00:00 9 12/13/1996 00:00:00 10  Null Fill : Calendar Data: Calendar Name = FOO CALENDAR Frequency = 4 MinDate = 01/01/1996 00:00:00 MaxDate = 01/01/1997 00:00:00 patBits: 1111111 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : Series Data:  Date Value 12/02/1996 00:00:00 1 12/03/1996 00:00:00 2 12/04/1996 00:00:00 3 12/05/1996 00:00:00 4 12/06/1996 00:00:00 5 12/07/1996 00:00:00 12/08/1996 00:00:00 12/09/1996 00:00:00 6 12/10/1996 00:00:00 7 12/11/1996 00:00:00 8 12/12/1996 00:00:00 9 12/13/1996 00:00:00 10 
ORDSYS.TimeSeries.First(
Given a time series, returns the first element in it.
The input time series.
A null is returned if the time series (ts) is empty.
An exception is returned if the time series (ts) is null.
Return the first timestamp and opening price for stock ACME in the stockdemo_ts time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.First(ts.open), 'First Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
First Results : Timestamp : 11/01/1996 00:00:00 Value : 59
ORDSYS.TimeSeries.FirstN(
Given a time series and a number of elements (NumValues) to return, returns the first NumValues elements in the time series.
The input time series.
Number of elements from the beginning of the time series to be returned.
The function returns a time series populated with the first NumValues cells from the input time series (ts). The calendar of the output time series is the same as that of the input time series.
An exception is returned if the time series (ts) is null or if NumValues is zero (0) or negative.
Return the first 10 timestamps and opening prices in the time series for stock ACME.:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.FirstN(ts.open, 10), 'FirstN Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
FirstN Results : Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 
ORDSYS.TimeSeries.GetDatedElement (
Given a time series and a date, returns the time series element for that date.
The input time series.
Positive integer specifying the date of the element to be returned.
The function returns the cell from the input time series (ts) at the specified date (target_date). If there is no data in ts at target_date, the function returns a null.
An exception is returned if the time series (ts) is null.
Return the timestamp and opening price for 26Nov1996 for stock ACME:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; tstDate date; BEGIN  Get the cell for 26NOV1996 from ACME's open and display it tstDate := TO_DATE('11/26/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.GetDatedElement(ts.open, tstDate), 'GetDatedElement Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
GetDatedElement Results : Timestamp : 11/26/1996 00:00:00 Value : 76
ORDSYS.TimeSeries.GetNthElement
(ts ORDSYS.ORDTNumSeriesIOTRef,
target_index INTEGER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumCell;
Given a time series, a number (target_index), and optionally a date range, returns the Nth element (element whose position corresponds to target_index) in the specified time series, or within the date range if one is specified.
The input time series.
Positive integer specifying the position of the element to be returned.
Starting date within the time series to which target_index is to be applied. If target_index = 1, the function returns the element for startDate. If startDate is specified, endDate must also be specified.
Ending date within the time series to which target_index is to be applied. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the tenth opening price for stock AONE:
SELECT ORDSYS.TimeSeries.ExtractValue( ORDSYS.TimeSeries.GetNthElement(open, 10)) FROM ORDTDEV.stocks_ts WHERE ticker = 'AONE';
This example might produce the following output:
ORDSYS.TIM  15.1875 1 row selected.
ORDSYS.TimeSeries.GetSeries(
or
ORDSYS.TimeSeries.GetSeries(
Given a reference to a time series of references (ORDTNumSeriesIOTRef or ORDTVarchar2SeriesIOTRef), returns a time series instance (ORDTNumSeries or ORDTVarchar2Series).
The input time series.
The function materializes the input time series.
An exception is returned if the time series (ts) is null.
Return an instance of a specified time series (opening prices for stock ACME):
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.GetSeries(ts.open), 'GetSeries Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
GetSeries Results : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99 
ORDSYS.TimeSeries.IsValidTS(
or
ORDSYS.TimeSeries.IsValidTS(
Returns 1 if the time series is valid and 0 if the time series is invalid.
The input time series.
A time series is invalid if one or more of the following conditions are true:
Contrast this function with ValidateTS, which checks whether a time series is valid, and if the time series is not valid, outputs a diagnostic message and tables with timestamps that are causing the time series to be invalid.
Use the IsValidTS and ValidateTS functions and the DisplayValTS procedure with an invalid time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE numTS ORDSYS.ORDTNumSeries; tempVal integer; retIsValid integer; retValTS integer; loDateTab ORDSYS.ORDTDateTab := NULL; hiDateTab ORDSYS.ORDTDateTab := NULL; impDateTab ORDSYS.ORDTDateTab := NULL; dupDateTab ORDSYS.ORDTDateTab := NULL; extraDateTab ORDSYS.ORDTDateTab := NULL; missingDateTab ORDSYS.ORDTDateTab := NULL; outMesg varchar2(2000); BEGIN  Set the buffer size DBMS_OUTPUT.ENABLE(100000);   NOTE: Here an instance of the time series is materialized  so that it could be modified to generate an invalid time series.  SELECT ORDSYS.TIMESERIES.GetSeries(ts.open) INTO numTS FROM ordtdev.stockdemo_ts ts WHERE ts.ticker = 'ACME';  Example of validating a valid time series. SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = '  retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = '  retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); DBMS_OUTPUT.NEW_LINE;  For illustration let us first create an invalid timeseries.   Here we are adjusting the calendar's minDate and maxDate to avoid  getting a huge list of missing dates.  numTS.cal.minDate := TO_DATE('10/28/1996'); numTS.cal.maxDate := TO_DATE('01/05/1997');  Add Dates Before numTS.cal.minDate numTS.series(10).tstamp := numTS.cal.minDate  1; numTS.series(11).tstamp := numTS.cal.minDate  2;  Add Dates Beyond numTS.cal.maxDate numTS.series(12).tstamp := numTS.cal.maxDate + 1; numTS.series(13).tstamp := numTS.cal.maxDate + 2;  Add some null timestamps numTS.series(14).tstamp := NULL; numTS.series(15).tstamp := NULL;  Add some imprecise dates (some are duplicated) numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24; numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24;  Add some duplicate timestamps numTS.series(19).tstamp := numTS.series(18).tstamp; numTS.series(21).tstamp := numTS.series(20).tstamp;  Add some extra dates in the middle numTS.series(37).tstamp := TO_DATE('12/28/1996'); numTS.series(36).tstamp := TO_DATE('12/29/1996');  Add some holes at the end numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997');  Example of validating an invalid time series. SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = '  retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = '  retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); END; /
This example might produce the following output:
A VALID TIME SERIES : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99  Value returned by IsValid = 1 Value returned by ValidateTS = 1 DisplayValTS: Testing DisplayValTS: TSSUC: the input time series is a valid time series AN INVALID TIME SERIES : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 10/27/1996 00:00:00 68 10/26/1996 00:00:00 69 01/06/1997 00:00:00 70 01/07/1997 00:00:00 71 72 73 11/22/1996 00:00:00 74 11/22/1996 01:00:00 75 11/22/1996 15:00:00 76 11/22/1996 15:00:00 77 11/29/1996 00:00:00 78 11/29/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/29/1996 00:00:00 94 12/28/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 01/04/1997 00:00:00 99  Value returned by IsValid = 0 Value returned by ValidateTS = 0 DisplayValTS: Testing DisplayValTS: TSWRN: the input time series has errors. See the message for details message output by validateTS: TSERR: the input time series is unsorted TSERR: the time series has null timestamps TSERR: the time series has timestamps < calendar minDate (refer LoDateTab) TSERR: the time series has timestamps > calendar maxDate (refer HiDateTab) TSERR: the time series has imprecise timestamps (refer impreciseDateTab) TSERR: the time series has duplicate timestamps (refer DuplicateDateTab) list of dates < calendar minDate  lowDateTab : 10/26/1996 00:00:00 10/27/1996 00:00:00 list of dates > calendar maxDate  hiDateTab : 01/06/1997 00:00:00 01/07/1997 00:00:00 list of imprecise dates  impreciseDateTab : 11/22/1996 01:00:00 11/22/1996 15:00:00 list of duplicate dates  duplicateDateTab : 11/22/1996 15:00:00 11/29/1996 00:00:00 ExtraDateTab : 12/28/1996 00:00:00 12/29/1996 00:00:00 01/04/1997 00:00:00 MissingDateTab : 10/28/1996 00:00:00 10/29/1996 00:00:00 10/30/1996 00:00:00 10/31/1996 00:00:00 11/14/1996 00:00:00 11/15/1996 00:00:00 11/18/1996 00:00:00 11/19/1996 00:00:00 11/20/1996 00:00:00 11/21/1996 00:00:00 11/25/1996 00:00:00 11/26/1996 00:00:00 11/27/1996 00:00:00 12/02/1996 00:00:00 12/23/1996 00:00:00 12/24/1996 00:00:00 12/31/1996 00:00:00 01/01/1997 00:00:00 01/02/1997 00:00:00 01/03/1997 00:00:00
ORDSYS.TimeSeries.Lag (
ts ORDSYS.ORDTNumSeriesIOTRef,
units INTEGER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.Lag (
ts ORDSYS.ORDTNumSeriesIOTRef,
lead_date DATE
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a positive or negative number (units) or a date (lead_date), and optionally a starting and ending timestamp within the time series, returns a time series that lags or (for negative numeric values) leads the input time series by the appropriate number of timestamps.
The input time series.
Integer specifying the number of timestamps by which the output time series is to be adjusted. If units is positive, each element in the output time series is the same as the element in the input time series for that relative position minus the units. If units is negative, each element in the output time series is the same as the element in the input time series for that relative position plus the units.
The date relative to the starting date reflecting the number of timestamps by which the output time series is to be adjusted. The function calculates the number of timestamps between lead_date and startDate, and then uses that number as if it were a units parameter value. (If lead_date is later than startDate, the effective units value is positive; if lead_date is before the starting date, the effective units value is negative.)
Starting date to be used in calculating the lead or lag value; also the starting date in the input time series for which the output time series is to be created. If startDate is specified, endDate must also be specified.
Ending date in the input time series for which the output time series is to be created. If endDate is specified, startDate must also be specified.
The function creates a time series whose elements reflect an input time series adjusted by a number of timestamps. For example, using the United States stock trading calendar for 1997, if the first timestamp in the input time series is 06Jan1997 (Monday) and the units value is 2, the first timestamp in the output time series is 02Jan1997 (Thursday) and its associated value (such as closing price) is the same as that for 06Jan1997 in the input time series. Subsequent elements of the output time series reflect the timestamp adjustment.
For example, assuming the United States stock trading calendar for 1997, Table 51 shows some time series data with a twoday lag period.
For convenience, both the Lead and Lag functions are provided.The functions operate identically, except that they interpret the sign of the units value in opposite ways. For example, Lead with 10 for units is equivalent to Lag with 10 for units. Moreover, because of the way the lead_date parameter is interpreted, Lead and Lag with a lead_date operate identically.
Return a time series starting with 03Mar1997 using closing prices from the time series from 01Nov1996 through 30Nov1996 for stock ACME. The returned time series has the same number of timestamps as are in the specified date range (startDate through endDate).
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Lag(ts.close, to_date('03MAR97','DDMONYY'), to_date('01NOV96','DDMONYY'), to_date('30NOV96','DDMONYY')) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE   03MAR97 59 04MAR97 60 05MAR97 61 06MAR97 62 07MAR97 63 10MAR97 64 ... ... 27MAR97 77 28MAR97 78 20 rows selected.
ORDSYS.TimeSeries.Last(
Given a time series, returns the last element in it.
The input time series.
A null is returned if the time series (ts) is empty.
An exception is returned if the time series (ts) is null.
Return the last timestamp and opening price for stock ACME in the stockdemo_ts time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.Last(ts.open), 'Last Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
Last Results : Timestamp : 12/31/1996 00:00:00 Value : 99
ORDSYS.TimeSeries.LastN(
Given a time series and a number of elements (NumValues) to return, returns the last NumValues elements in the time series.
The input time series.
Number of elements from the end of the time series to be returned.
The function returns a time series populated with the last NumValues cells from the input time series (ts). The calendar of the output time series is the same as that of the input time series.
An exception is returned if the time series (ts) is null or if NumValues is zero (0) or negative.
Return the last 10 timestamps and opening prices in the time series for stock ACME.:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE dummyval INTEGER; BEGIN SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.LastN(ts.open, 10), 'LastN Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME'; END; /
This example might produce the following output:
LastN Results : Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99 
ORDSYS.TimeSeries.Lead (
ts ORDSYS.ORDTNumSeriesIOTRef,
units INTEGER
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.Lead (
ts ORDSYS.ORDTNumSeriesIOTRef,
lead_date DATE
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a positive or negative number (units) or a date (lead_date), and optionally a starting and ending timestamp within the time series, returns a time series that leads or (for negative numeric values) lags the input time series by the appropriate number of timestamps.
The input time series.
Integer specifying the number of timestamps by which the output time series is to be adjusted. If units is positive, each element in the output time series is the same as the element in the input time series for that relative position plus the units. If units is negative, each element in the output time series is the same as the element in the input time series for that relative position minus the units.
The date relative to the starting date reflecting the number of timestamps by which the output time series is to be adjusted. The function calculates the number of timestamps between lead_date and startDate, and then uses that number as if it were a units parameter value. (If lead_date is later than startDate, the effective units value is positive; if lead_date is before startDate, the effective units value is negative.)
Starting date to be used in calculating the lead or lag value; also the starting date in the input time series for which the output time series is to be created. If startDate is specified, endDate must also be specified.
Ending date in the input time series for which the output time series is to be created. If endDate is specified, startDate must also be specified.
The function creates a time series whose elements reflect an input time series adjusted by a number of timestamps. For example, using the United States stock trading calendar for 1997, if the first timestamp in the input time series is 02Jan1997 (Thursday) and the units value is 2, the first timestamp in the output time series is 06Jan1997 (Monday) and its associated value (such as closing price) is the same as that for 02Jan1997 in the input time series. Subsequent elements of the output time series reflect the timestamp adjustment.
For example, assuming the United States stock trading calendar for 1997, Table 52 shows some time series data with a twoday lead period:
For convenience, both the Lead and Lag functions are provided. The functions operate identically, except that they interpret the sign of the units value in opposite ways. For example, Lead with 10 for units is equivalent to Lag with 10 for units. Moreover, because of the way the lead_date parameter is interpreted, Lead and Lag with a lead_date operate identically.
Return a time series starting with 03Mar1997 using closing prices from the time series from 01Nov1996 through 30Nov1996 for stock ACME. The returned time series has the same number of timestamps as are in the specified date range (startDate through endDate).
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Lead(ts.close, to_date('03MAR97','DDMONYY'), to_date('01NOV96','DDMONYY'), to_date('30NOV96','DDMONYY')) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE   03MAR97 59 04MAR97 60 05MAR97 61 06MAR97 62 07MAR97 63 10MAR97 64 ... ... 27MAR97 77 28MAR97 78 20 rows selected.
ORDSYS.TimeSeries.Mavg(
ts ORDSYS.ORDTNumSeriesIOTRef,
[startDate DATE, endDate DATE,]
k INTEGER
) RETURN ORDSYS.ORDTNumSeries;
Given an input ORDTNumSeries, returns a moving average for the time series, or for the date range if one is specified. Each value in the returned time series is the average of the value for the current timestamp plus the value for each of the previous specified number of timestamps minus one.
For example, a 30day moving average of closing prices for a stock on any given date is the average of that day's closing price and the 29 preceding closing prices.
The input time series.
Starting date within the time series for which to return moving averages. If startDate is specified, endDate must also be specified.
Ending date within the time series for which to return moving averages. If endDate is specified, startDate must also be specified.
Positive integer specifying the lookback window (number of timestamps, including the current one, over which to compute the moving average).
The returned time series has nulls for any entry where there are not k1 timestamps preceding it in the calendar. For example, if a stock trading calendar for 1997 starts on 02Jan1997, the series of 5day moving averages of the closing price for a stock for the year has nulls for the closing price for the first four timestamps (02Jan, 03Jan, 06Jan, and 07Jan), because there are insufficient timestamps for computing the average.
Any nulls in the entries for the k timestamps are ignored, as explained in Section 2.8.2.1.
An exception is returned if any of the following conditions is true:
Return a table of 10day moving average values of the closing price for stock ACME for the month of December 1996:
SELECT * FROM the (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.Mavg(ts.close,to_date('02DEC96','DDMONYY'), to_date('31DEC96','DDMONYY'),10) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stockdemo_ts ts WHERE ts.ticker='ACME');
This example might produce the following output:
TSTAMP VALUE   02DEC96 74.5 03DEC96 75.5 04DEC96 76.5 05DEC96 77.5 06DEC96 78.5 09DEC96 79.5 10DEC96 80.5 11DEC96 81.5 12DEC96 82.5 13DEC96 83.5 16DEC96 84.5 17DEC96 85.5 18DEC96 86.5 19DEC96 87.5 20DEC96 88.5 23DEC96 89.5 24DEC96 90.5 26DEC96 91.5 27DEC96 92.5 30DEC96 93.5 31DEC96 94.5 21 rows selected. SVRMGR>
ORDSYS.TimeSeries.Msum(
ts ORDSYS.ORDTNumSeriesIOTRef,
[startDate DATE, endDate DATE,]
k INTEGER
) RETURN ORDSYS.ORDTNumSeries;
Given an input ORDTNumSeries, returns a moving sum for the time series, or for the date range if one is specified. Each value in the returned time series is the sum of the value for the current timestamp plus the value for each of the previous specified number of timestamps minus one.
For example, a 30day moving sum for a stock's daily trading volume on any given date is the sum of that day's volume and the 29 preceding daily volumes.
The input time series.
Starting date within the time series for which to return moving sums. If startDate is specified, endDate must also be specified.
Ending date within the time series for which to return moving sums. If endDate is specified, startDate must also be specified.
Positive integer specifying the lookback window (number of timestamps, including the current one, over which to compute the moving sum).
The returned time series has nulls for any entry where there are not k1 timestamps preceding it in the calendar. For example, if a stock trading calendar for 1997 starts on 02Jan1997, the series of 5day moving sums of the trading volume for a stock for the year has nulls for the volume for the first four timestamps (02Jan, 03Jan, 06Jan, and 07Jan), because there are insufficient timestamps for computing the sum.
Any nulls in the entries for the k timestamps are ignored, as explained in Section 2.8.2.1.
An exception is returned if any of the following conditions is true:
Return a table of 30day moving sum values of trading volume for stock AONE for 1996:
SELECT * FROM THE( SELECT CAST(ORDTS.Extract(ORDTS.MSUM(volume, to_date(`010196','MMDDYY'), to_date(`123196','MMDDYY'), 30)) AS ORDTNumTab) FROM StockTabView WHERE ticker = `AONE');
ORDSYS.TimeSeries.Scaleup(
Given an input ORDTCalendar and a date, returns a scaled date.
The date to be used for scaling.
The calendar to be used for scaling the date.
For an explanation of concepts related to time scaling, see Section 2.9.
This function is used in a SQL GROUP BY clause for scaling of dates.
An exception is returned if inDate or calendar is null.
For all tickers accessible through the stockdemo_sv view (ACME, FUNCO, SAMCO, and XCORP), scale daily data to monthly summary data for the summed volume and average closing price.
  Scaleup  Group By interface  For all tickers in stockdemo, scale daily data to monthly  summary data, reporting summed volumes and average closes.  SELECT ticker, ORDSYS.TimeSeries.Scaleup(sv.tstamp, value(cal)),sum(volume),avg(close) FROM ORDTDEV.stockdemo_sv sv, ordtdev.stockdemo_calendars cal WHERE cal.name = 'MONTHLY' GROUP BY ticker,ORDSYS.TimeSeries.Scaleup(sv.tstamp, value(cal));
This example might produce the following output:
TICKE ORDSYS.OR SUM(VOLUME AVG(CLOSE)     ACME 01NOV96 20000 68.5 ACME 01DEC96 21000 89 FUNCO 01NOV96 20000 23.823 FUNCO 01DEC96 21000 23.8257143 SAMCO 01NOV96 10207000 39.83125 SAMCO 01DEC96 3719450 38.2738095 XCORP 01OCT96 10270250 79.1458333 XCORP 01NOV96 100243350 84.6973684 XCORP 01DEC96 141838350 91.9572368 9 rows selected.
ORDSYS.TimeSeries.ScaleupAvg(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the average value of each scaled group of nonnull values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
Nulls are ignored in computing the average for each group of values.
For an explanation of concepts related to time scaling, see Section 2.9.
Return the average closing prices for stock SAMCO for each month for the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupAvg( ts.close, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE   01JAN90 29.7074045 01FEB90 29.0477211 01MAR90 30.7003091 . . . 01OCT96 42.7717391 01NOV96 39.83125 01DEC96 38.2738095 84 rows selected.
ORDSYS.TimeSeries.ScaleupCount(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the count of nonnull timestamps in each scaled group.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
Nulls are ignored in computing the count for each group of values.
For an explanation of concepts related to time scaling, see Section 2.9.
Return the quarterly count of daily closing prices for stock SAMCO for the period 01June1996 through 31December 1996:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupCount( ts.close, sc.calendar, to_date('01JUL1996','DDMONYYYY'), to_date('31DEC1996','DDMONYYYY') ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='QUARTERLY');
This example might produce the following output:
TSTAMP VALUE   01JUL96 64 01OCT96 64 2 rows selected.
ORDSYS.TimeSeries.ScaleupFirst(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the first nonnull value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the first closing prices for stock SAMCO for the months of October, November, and December of 1996:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupFirst( ts.close, sc.calendar, to_date('01OCT1996','DDMONYYYY'), to_date('01JAN1997','DDMONYYYY') ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE   01OCT96 42.75 01NOV96 41.875 01DEC96 38.125 3 rows selected.
ORDSYS.TimeSeries.ScaleupLast(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the last nonnull value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the last closing prices for stock SAMCO for the months of October, November, and December of 1996:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupLast( ts.close, sc.calendar, to_date('01OCT1996','DDMONYYYY'), to_date('01JAN1997','DDMONYYYY') ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE   01OCT96 42.375 01NOV96 38.25 01DEC96 39.75 3 rows selected.
Note that each timestamp reflects the first date of the month in the calendar (following the rules explained in Section 2.2.1), and each value in this case reflects the closing price on the last date for that month in the calendar.
ORDSYS.TimeSeries.ScaleupMax(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the maximum value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the highest (maximum) closing prices for stock SAMCO for each month in the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupMax( ts.close, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE   01JAN90 31.2813 01FEB90 29.7813 01MAR90 31.1875 01APR90 31.5938 01MAY90 32.875 01JUN90 33.7813 01JUL90 34.6875 01AUG90 31.875 ... ... 01OCT96 43.375 01NOV96 43.75 01DEC96 39.75 84 rows selected.
ORDSYS.TimeSeries.ScaleupMin(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the minimum value of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the lowest (minimum) closing prices for stock SAMCO for each month in the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupMin( ts.close, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE   01JAN90 27.6875 01FEB90 28.2813 01MAR90 30.0938 01APR90 30.1875 01MAY90 30.7813 01JUN90 32.0938 01JUL90 32.2813 01AUG90 28.5938 ... ... 01OCT96 42 01NOV96 37.375 01DEC96 37.875 84 rows selected.
ORDSYS.TimeSeries.ScaleupSum(
ts ORDSYS.ORDTNumSeriesIOTRef,
calendar ORDSYS.ORDTCalendar
[, startDate DATE
, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the sum of each scaled group of values.
The input time series.
The calendar to be used for the scaling.
The starting date to be used. If startDate is specified, endDate must also be specified.
The ending date to be used. If endDate is specified, startDate must also be specified.
An exception is returned for any of the following conditions:
For an explanation of concepts related to time scaling, see Section 2.9.
Return the sum of the daily trade volume for stock SAMCO for each month in the entire time series:
SELECT * FROM THE (SELECT CAST(ORDSYS.TimeSeries.ExtractTable( ORDSYS.TimeSeries.ScaleupSum( ts.volume, sc.calendar ) ) AS ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc WHERE ts.ticker='SAMCO' and sc.name ='MONTHLY');
This example might produce the following output:
TSTAMP VALUE   01JAN90 3117750 01FEB90 2036500 01MAR90 1424375 01APR90 981500 01MAY90 1348875 01JUN90 1395875 01JUL90 1088125 01AUG90 1503000 ... ... 01OCT96 1615350 01NOV96 10207000 01DEC96 3719450 84 rows selected.
ORDSYS.TimeSeries.TrimSeries(ts ORDSYS.ORDTNumSeriesIOTRef
or
ORDSYS.TimeSeries.TrimSeries(ts ORDSYS.ORDTVarchar2SeriesIOTRef
Given an input ORDT series, returns an ORDT series of the same type with all data outside of the given date range removed. The calendar of the returned series will be the same as that of the original series.
The input time series.
Starting date within the time series. If startDate is specified, endDate must also be specified.
Ending date within the time series. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the opening prices for stock AONE for dates in the calendar from 01Dec1996 through 31Dec1996:
SET SERVEROUTPUT ON DECLARE tmp INTEGER; tstDate1 DATE; tstDate2 DATE; BEGIN  Set tstDate values tstDate1 := TO_DATE('12/01/1996 00:00:00','MM/DD/YYYY HH24:MI:SS'); tstDate2 := TO_DATE('12/31/1996 00:00:00','MM/DD/YYYY HH24:MI:SS'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TrimSeries(open, tstDate1, tstDate2)) INTO tmp FROM ORDTDEV.stocks_ts WHERE ticker = 'AONE'; END; /
This statement might produce the following output:
Calendar Data: Calendar Name = AONE Frequency = 4 MinDate = 01JAN80 MaxDate = 01JAN01 patBits: 0111110 patAnchor = 06APR97 onExceptions : offExceptions : 19FEB90 13APR90 28MAY90 04JUL90 03SEP90 22NOV90 25DEC90 01JAN91 18FEB91 29MAR91 27MAY91 04JUL91 02SEP91 28NOV91 25DEC91 01JAN92 17FEB92 17APR92 25MAY92 03JUL92 07SEP92 26NOV92 25DEC92 01JAN93 15FEB93 09APR93 31MAY93 05JUL93 06SEP93 25NOV93 24DEC93 21FEB94 01APR94 27APR94 30MAY94 04JUL94 05SEP94 24NOV94 26DEC94 02JAN95 20FEB95 14APR95 29MAY95 04JUL95 04SEP95 23NOV95 25DEC95 01JAN96 19FEB96 05APR96 27MAY96 04JUL96 02SEP96 17OCT96 28NOV96 25DEC96 27DEC96 Series Data:  Date Value 02DEC96 59.875 03DEC96 60.875 04DEC96 60.625 05DEC96 57.75 06DEC96 56.5 09DEC96 57 10DEC96 60.875 11DEC96 59.625 12DEC96 59.75 13DEC96 54.875 16DEC96 55.625 17DEC96 53.25 18DEC96 54.375 19DEC96 53.875 20DEC96 53.375 23DEC96 54.375 24DEC96 53.5 26DEC96 54.375 30DEC96 54.125 31DEC96 52.875 
ORDSYS.TimeSeries.TSAdd (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSAdd (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the addition of the first two parameters.
The time series (or first time series) whose elements are to be added either to corresponding elements in the second time series or to a constant.
The time series whose elements are to be added to corresponding elements in the first time series.
A constant to be added to corresponding elements in the first time series.
Starting date within the time series for which the addition is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the addition is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise addition operation on each element of the time series. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if any of the following conditions is true:
Add the high price for stock ACME and the low price for stock FUNCO for each trading day from 14Nov1996 through 14Dec1996:
CONNECT ORDTUSER/ORDTUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSAdd(ts1.high, ts2.low, startDate, endDate), 'TSAdd Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSAdd Results : Calendar Data: Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/14/1996 00:00:00 92.87 11/15/1996 00:00:00 93.84 11/18/1996 00:00:00 94.87 11/19/1996 00:00:00 95.85 11/20/1996 00:00:00 96.82 11/21/1996 00:00:00 97.84 11/22/1996 00:00:00 98.85 11/25/1996 00:00:00 99.81 11/26/1996 00:00:00 100.78 11/27/1996 00:00:00 101.71 11/29/1996 00:00:00 102.75 12/02/1996 00:00:00 103.88 12/03/1996 00:00:00 105.03 12/04/1996 00:00:00 106.02 12/05/1996 00:00:00 107.13 12/06/1996 00:00:00 107.75 12/09/1996 00:00:00 108.77 12/10/1996 00:00:00 109.8 12/11/1996 00:00:00 110.5 12/12/1996 00:00:00 111.41 12/13/1996 00:00:00 112.4 
ORDSYS.TimeSeries.TSAvg (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the average of all nonnull time series entries.
The input time series.
Starting date within the time series for which the average is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the average is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the average, variance, and standard deviation of the closing price of stock ACME:
  Compute various aggregate statistics.  SELECT ORDSYS.TimeSeries.TSAvg(close), ORDSYS.TimeSeries.TSVariance(close), ORDSYS.TimeSeries.TSStdDev(close) FROM ORDTDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD    79 143.5 11.9791486
1 row selected.
ORDSYS.TimeSeries.TSCount (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the count of all nonnull time series entries.
The input time series.
Starting date within the time series for which the count is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the count is to be calculated. If endDate is specified, startDate must also be specified.
Nulls are ignored in computing the count.
An exception is returned if any of the following conditions is true:
Return the total number of daily closing prices for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSCount(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSCount FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSCOUNT  22 1 row selected.
ORDSYS.TimeSeries.TSDivide (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSDivide (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the division of the first parameter by the second parameter.
The time series (or first time series) whose elements are to be divided by either the corresponding elements in the second time series or a constant.
The time series whose elements are to be divided into corresponding elements in the first time series.
A constant to be divided into corresponding elements in the first time series.
Starting date within the time series for which the division is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the division is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise division operation on each element of the time series (or first time series) by the corresponding element in the second time series or by a constant. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if any of the following conditions is true:
Divide the high price for stock ACME by the low price for stock FUNCO for each trading day from 14Nov1996 through 14Dec1996:
CONNECT ORDTUSER/ORDTUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSDivide(ts1.high, ts2.low, startDate, endDate), 'TSDivide Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSDivide Results : Calendar Data: Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/14/1996 00:00:00 2.89065772936740678676162547130289065773 11/15/1996 00:00:00 2.93624161073825503355704697986577181208 11/18/1996 00:00:00 2.97444490992878089652283200670297444491 11/19/1996 00:00:00 3.01886792452830188679245283018867924528 11/20/1996 00:00:00 3.0646515533165407220822837951301427372 11/21/1996 00:00:00 3.10402684563758389261744966442953020134 11/22/1996 00:00:00 3.1446540880503144654088050314465408805 11/25/1996 00:00:00 3.19193616127677446451070978580428391432 11/26/1996 00:00:00 3.23801513877207737594617325483599663583 11/27/1996 00:00:00 3.28975115984816533108393083087304934627 11/29/1996 00:00:00 3.32631578947368421052631578947368421053 12/02/1996 00:00:00 3.35008375209380234505862646566164154104 12/03/1996 00:00:00 3.37078651685393258426966292134831460674 12/04/1996 00:00:00 3.41382181515403830141548709408825978351 12/05/1996 00:00:00 3.43970161624533775383340240364691255698 12/06/1996 00:00:00 3.53684210526315789473684210526315789474 12/09/1996 00:00:00 3.57593605384938998737904922170803533866 12/10/1996 00:00:00 3.61344537815126050420168067226890756303 12/11/1996 00:00:00 3.70212765957446808510638297872340425532 12/12/1996 00:00:00 3.75907731738573259290901324220418624519 12/13/1996 00:00:00 3.8034188034188034188034188034188034188 
ORDSYS.TimeSeries.TSMax (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the highest (maximum) of all nonnull time series entries.
The input time series.
Starting date within the time series for which the maximum is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the maximum is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the highest closing price for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSMax(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMax FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSMAX  16.3914 1 row selected.
ORDSYS.TimeSeries.TSMaxN (
ts ORDSYS.ORDTNumSeriesIOTRef,
NumValues INTEGER,
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumTab;
Given an input ORDTNumSeries, a number of values to return, and optionally starting and ending dates, returns an ORDTNumTab with the specified number (NumValues) of the top (highest) values.
The input time series.
Number of values to return.
Starting date within the time series for which the top values are to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the top values are to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the 10 highest closing prices for stock AONE for the month of January 1996:
SELECT * FROM THE( SELECT CAST( ORDSYS.TimeSeries.TSMaxN(close, 10, to_date('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY')) as ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts WHERE ticker ='AONE');
This example might produce the following output:
TSTAMP VALUE   24JAN96 43.9138 25JAN96 42.9925 31JAN96 42.9925 26JAN96 42.7413 30JAN96 42.7413 29JAN96 42.5738 23JAN96 41.9875 22JAN96 41.82 19JAN96 41.485 18JAN96 40.815 10 rows selected.
ORDSYS.TimeSeries.TSMedian (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the median of all nonnull time series entries.
The input time series.
Starting date within the time series for which the median is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the median is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the median closing price for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSMedian(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMedian FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSMEDIAN  15.4649 1 row selected.
ORDSYS.TimeSeries.TSMin (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the lowest (minimum) of all nonnull time series entries.
The input time series.
Starting date within the time series for which the minimum is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the minimum is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the lowest closing price for stock AONE for the month of January 1990:
SELECT ORDSYS.TimeSeries.TSMin(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSMin FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSMIN  15.1038 1 row selected.
ORDSYS.TimeSeries.TSMinN (
ts ORDSYS.ORDTNumSeriesIOTRef,
NumValues INTEGER,
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumTab;
Given an input ORDTNumSeries, a number of values to return, and optionally starting and ending dates, returns an ORDTNumTab with the specified number (NumValues) of the bottom (lowest) values.
The input time series.
Number of values to return.
Starting date within the time series for which the bottom values are to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the bottom values are to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the 10 lowest closing prices for stock AONE for the month of January 1996:
SELECT * FROM THE( SELECT CAST( ORDSYS.TimeSeries.TSMinN(close, 10, to_date('01011996','MMDDYYYY'), to_date('01311996','MMDDYYYY')) as ORDSYS.ORDTNumTab) FROM ORDTDEV.stocks_ts WHERE ticker ='AONE');
This example might produce the following output:
TSTAMP VALUE   15JAN96 37.8 09JAN96 37.9675 04JAN96 38.3025 10JAN96 38.47 03JAN96 38.6375 16JAN96 38.9725 11JAN96 39.0563 08JAN96 39.3075 12JAN96 39.5588 17JAN96 39.6425 10 rows selected.
ORDSYS.TimeSeries.TSMultiply (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSMultiply (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the multiplication of the first parameter by the second parameter.
The time series (or first time series) whose elements are to be multiplied by either the corresponding elements in the second time series or a constant.
The time series whose elements are to be multiplied by corresponding elements in the first time series.
A constant to be multiplied by corresponding elements in the first time series.
Starting date within the time series for which the multiplication is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the multiplication is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise multiplication operation on each element of the time series (or first time series) by the corresponding element in the second time series or by a constant. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if any of the following conditions is true:
Multiply the high price for stock ACME by the low price for stock FUNCO for each trading day from 14Nov1996 through 14Dec1996:
CONNECT ORDTUSER/ORDTUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSMultiply(ts1.high, ts2.low, startDate, endDate), 'TSMultiply Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSMultiply Results : Calendar Data: Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/14/1996 00:00:00 1647.03 11/15/1996 00:00:00 1668.8 11/18/1996 00:00:00 1694.77 11/19/1996 00:00:00 1717.2 11/20/1996 00:00:00 1738.86 11/21/1996 00:00:00 1764.16 11/22/1996 00:00:00 1788.75 11/25/1996 00:00:00 1809.56 11/26/1996 00:00:00 1831.06 11/27/1996 00:00:00 1849.38 11/29/1996 00:00:00 1876.25 12/02/1996 00:00:00 1910.4 12/03/1996 00:00:00 1946.43 12/04/1996 00:00:00 1969.64 12/05/1996 00:00:00 2002.79 12/06/1996 00:00:00 1995 12/09/1996 00:00:00 2020.45 12/10/1996 00:00:00 2046.8 12/11/1996 00:00:00 2044.5 12/12/1996 00:00:00 2060.08 12/13/1996 00:00:00 2082.6 
ORDSYS.TimeSeries.TSProd (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the product (result of multiplication) of all nonnull time series entries.
The input time series.
Starting date within the time series for which the product is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the product is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the product resulting from multiplying the daily closing prices for stock AONE for the month of January 1990. (This example is not very plausible, but is presented merely to illustrate the syntax.)
SELECT ORDSYS.TimeSeries.TSProd(close, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSProd FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSPROD  1.7126E+26 1 row selected.
ORDSYS.TimeSeries.TSStdDev (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the standard deviation of all nonnull time series entries. (This function returns a value that is the square root of the value returned by the TSVar function.)
The input time series.
Starting date within the time series for which the standard deviation is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the standard deviation is to be calculated. If endDate is specified, startDate must also be specified.
If the date range refers to a time series with fewer than two timestamps, a null is returned.
An exception is returned if any of the following conditions is true:
Return the average, variance, and standard deviation of the closing price of stock ACME:
  Compute various aggregate statistics.  SELECT ORDSYS.TimeSeries.TSAvg(close), ORDSYS.TimeSeries.TSVariance(close), ORDSYS.TimeSeries.TSStdDev(close) FROM ORDTDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD    79 143.5 11.9791486
1 row selected.
ORDSYS.TimeSeries.TSSubtract (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
ts2 ORDSYS.ORDTNumSeriesIOTRef
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
or
ORDSYS.TimeSeries.TSSubtract (
ts1 ORDSYS.ORDTNumSeriesIOTRef,
k NUMBER
[,startDate DATE, endDate DATE]
) RETURN ORDSYS.ORDTNumSeries;
Given two input time series or a time series and a constant, and optionally starting and ending dates, returns a time series that reflects the subtraction of the second parameter from the first parameter.
The time series (or first time series) whose elements are to be decreased either by corresponding elements in the second time series or by a constant.
The time series whose elements are to be subtracted from corresponding elements in the first time series.
A constant to be subtracted from corresponding elements in the first time series.
Starting date within the time series for which the subtraction is to be performed. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the subtraction is to be performed. If endDate is specified, startDate must also be specified.
The function performs a pairwise subtraction operation on each element of ts1, decreasing it by either the corresponding element in ts2 or by k. This operation determines the value of each element of the returned time series. For example:
If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.
An exception is returned if any of the following conditions is true:
Subtract the low price for stock FUNCO from the high price for stock ACME for each trading day from 14Nov1996 through 14Dec1996:
CONNECT ORDTUSER/ORDTUSER SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE tstCal ORDSYS.ORDTCalendar; startDate date; endDate date; dummyval INTEGER; BEGIN startDate := TO_DATE('11/14/1996'); endDate := TO_DATE('12/14/1996'); SELECT ORDSYS.TimeSeries.Display( ORDSYS.TimeSeries.TSSubtract(ts1.high, ts2.low, startDate, endDate), 'TSSubtract Results') INTO dummyval FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2 WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO'; END; /
This example might produce the following output:
TSSubtract Results : Calendar Data: Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/14/1996 00:00:00 45.13 11/15/1996 00:00:00 46.16 11/18/1996 00:00:00 47.13 11/19/1996 00:00:00 48.15 11/20/1996 00:00:00 49.18 11/21/1996 00:00:00 50.16 11/22/1996 00:00:00 51.15 11/25/1996 00:00:00 52.19 11/26/1996 00:00:00 53.22 11/27/1996 00:00:00 54.29 11/29/1996 00:00:00 55.25 12/02/1996 00:00:00 56.12 12/03/1996 00:00:00 56.97 12/04/1996 00:00:00 57.98 12/05/1996 00:00:00 58.87 12/06/1996 00:00:00 60.25 12/09/1996 00:00:00 61.23 12/10/1996 00:00:00 62.2 12/11/1996 00:00:00 63.5 12/12/1996 00:00:00 64.59 12/13/1996 00:00:00 65.6 
ORDSYS.TimeSeries.TSSum (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the sum of all nonnull time series entries.
The input time series.
Starting date within the time series for which the sum is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the sum is to be calculated. If endDate is specified, startDate must also be specified.
An exception is returned if any of the following conditions is true:
Return the sum of the daily trading volumes for stock AONE for the month of January 1990 (that is, the total AONE volume for the month):
SELECT ORDSYS.TimeSeries.TSSum(volume, to_date('01/01/1990 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/31/1990 23:59:59', 'MM/DD/YYYY HH24:MI:SS')) TSSum FROM ORDTDEV.Stocks_TS WHERE ticker='AONE';
This example might produce the following output:
TSSUM  104434900 1 row selected.
ORDSYS.TimeSeries.TSVariance (
Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the variance of all nonnull time series entries. (This function is analogous to the SQL group function VAR.)
The input time series.
Starting date within the time series for which the variance is to be calculated. If startDate is specified, endDate must also be specified.
Ending date within the time series for which the variance is to be calculated. If endDate is specified, startDate must also be specified.
If the date range refers to a time series with fewer than two timestamps, a null is returned.
An exception is returned if any of the following conditions is true:
Return the average, variance, and standard deviation of the closing price of stock ACME:
  Compute various aggregate statistics.  SELECT ORDSYS.TimeSeries.TSAvg(close), ORDSYS.TimeSeries.TSVariance(close), ORDSYS.TimeSeries.TSStdDev(close) FROM ORDTDEV.stockdemo_ts WHERE ticker='ACME';
This example might produce the following output:
ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD    79 143.5 11.9791486
1 row selected.
ORDSYS.TimeSeries.ValidateTS(
ts IN ORDSYS.ORDTNumSeriesIOTRef,
outMesg OUT VARCHAR2,
loDateTab OUT ORDSYS.ORDTDateTab,
hiDateTab OUT ORDSYS.ORDTDateTab,
impreciseDateTab OUT ORDSYS.ORDTDateTab,
duplicateDateTab OUT ORDSYS.ORDTDateTab,
extraDateTab OUT ORDSYS.ORDTDateTab,
missingDateTab OUT ORDSYS.ORDTDateTab
) RETURN INTEGER;
or
ORDSYS.TimeSeries.ValidateTS(
ts IN ORDSYS.ORDTVarchar2SeriesIOTRef,
outMesg OUT VARCHAR2,
loDateTab OUT ORDSYS.ORDTDateTab,
hiDateTab OUT ORDSYS.ORDTDateTab,
impreciseDateTab OUT ORDSYS.ORDTDateTab,
duplicateDateTab OUT ORDSYS.ORDTDateTab,
extraDateTab OUT ORDSYS.ORDTDateTab,
missingDateTab OUT ORDSYS.ORDTDateTab
) RETURN INTEGER;
Checks whether a time series is valid, and if the time series is not valid, outputs a diagnostic message and tables with timestamps that are causing the time series to be invalid.
The time series to be checked for validity.
If the time series is invalid (if the return value = 0), contains a diagnostic message describing any problems.
A table of dates before the starting date of the calendar associated with the time series.
A table of dates after the ending date of the calendar associated with the calendar.
A table of the imprecise timestamps found in the time series.
A table of the duplicate timestamps found in the time series.
A table of dates that are included in the time series but that should be excluded based on the calendar definition (for example, a Saturday timestamp that is in a MondayFriday calendar and that is not an onexception).
A table of dates that are excluded from the time series but that should be included based on the calendar definition (for example, a Wednesday date that is not a holiday in a MondayFriday calendar and for which there is no data). Such dates can be considered as "holes" in the time series.
The function returns one of the following values:
Value  Meaning 

1 
The time series is valid. No errors were found. 
0 
The time series in invalid. 
A time series is invalid if one or more of the following conditions are true:
Contrast this function with IsValidTS, which simply checks whether a time series is valid.
You can use the DisplayValTS procedure (documented in this chapter) to display the information returned by the ValidateTS function.
The ValidateTS function cannot be called from SQL. It must be called from PL/SQL because of the OUT parameters.
Use the IsValidTS and ValidateTS functions and the DisplayValTS procedure with an invalid time series:
SET SERVEROUTPUT ON ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS'; DECLARE numTS ORDSYS.ORDTNumSeries; tempVal integer; retIsValid integer; retValTS integer; loDateTab ORDSYS.ORDTDateTab := NULL; hiDateTab ORDSYS.ORDTDateTab := NULL; impDateTab ORDSYS.ORDTDateTab := NULL; dupDateTab ORDSYS.ORDTDateTab := NULL; extraDateTab ORDSYS.ORDTDateTab := NULL; missingDateTab ORDSYS.ORDTDateTab := NULL; outMesg varchar2(2000); BEGIN  Set the buffer size DBMS_OUTPUT.ENABLE(100000);   NOTE: Here an instance of the time series is materialized  so that it could be modified to generate an invalid time series.  SELECT ORDSYS.TIMESERIES.GetSeries(ts.open) INTO numTS FROM ordtdev.stockdemo_ts ts WHERE ts.ticker = 'ACME';  Example of validating a valid time series. SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = '  retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = '  retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); DBMS_OUTPUT.NEW_LINE;  For illustration let us first create an invalid timeseries.   Here we are adjusting the calendar's minDate and maxDate to avoid  getting a huge list of missing dates.  numTS.cal.minDate := TO_DATE('10/28/1996'); numTS.cal.maxDate := TO_DATE('01/05/1997');  Add Dates Before numTS.cal.minDate numTS.series(10).tstamp := numTS.cal.minDate  1; numTS.series(11).tstamp := numTS.cal.minDate  2;  Add Dates Beyond numTS.cal.maxDate numTS.series(12).tstamp := numTS.cal.maxDate + 1; numTS.series(13).tstamp := numTS.cal.maxDate + 2;  Add some null timestamps numTS.series(14).tstamp := NULL; numTS.series(15).tstamp := NULL;  Add some imprecise dates (some are duplicated) numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24; numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24;  Add some duplicate timestamps numTS.series(19).tstamp := numTS.series(18).tstamp; numTS.series(21).tstamp := numTS.series(20).tstamp;  Add some extra dates in the middle numTS.series(37).tstamp := TO_DATE('12/28/1996'); numTS.series(36).tstamp := TO_DATE('12/29/1996');  Add some holes at the end numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997');  Example of validating an invalid time series. SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES') INTO tempVal FROM dual; retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS); retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab); DBMS_OUTPUT.PUT_LINE('Value returned by IsValid = '  retIsValid); DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS = '  retValTS); ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab, impDateTab, dupDateTab, extraDateTab, missingDateTab, 'Testing DisplayValTS'); END; /
This example might produce the following output:
A VALID TIME SERIES : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 01/01/1990 00:00:00 MaxDate = 01/01/2001 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 11/14/1996 00:00:00 68 11/15/1996 00:00:00 69 11/18/1996 00:00:00 70 11/19/1996 00:00:00 71 11/20/1996 00:00:00 72 11/21/1996 00:00:00 73 11/22/1996 00:00:00 74 11/25/1996 00:00:00 75 11/26/1996 00:00:00 76 11/27/1996 00:00:00 77 11/29/1996 00:00:00 78 12/02/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/23/1996 00:00:00 94 12/24/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 12/31/1996 00:00:00 99  Value returned by IsValid = 1 Value returned by ValidateTS = 1 DisplayValTS: Testing DisplayValTS: TSSUC: the input time series is a valid time series AN INVALID TIME SERIES : Name = ACME open NumSeries Calendar Data: Calendar Name = BUSINESS96 Frequency = 4 MinDate = 10/28/1996 00:00:00 MaxDate = 01/05/1997 00:00:00 patBits: 0111110 patAnchor = 01/07/1996 00:00:00 onExceptions : offExceptions : 11/28/1996 00:00:00 12/25/1996 00:00:00 Series Data:  Date Value 11/01/1996 00:00:00 59 11/04/1996 00:00:00 60 11/05/1996 00:00:00 61 11/06/1996 00:00:00 62 11/07/1996 00:00:00 63 11/08/1996 00:00:00 64 11/11/1996 00:00:00 65 11/12/1996 00:00:00 66 11/13/1996 00:00:00 67 10/27/1996 00:00:00 68 10/26/1996 00:00:00 69 01/06/1997 00:00:00 70 01/07/1997 00:00:00 71 72 73 11/22/1996 00:00:00 74 11/22/1996 01:00:00 75 11/22/1996 15:00:00 76 11/22/1996 15:00:00 77 11/29/1996 00:00:00 78 11/29/1996 00:00:00 79 12/03/1996 00:00:00 80 12/04/1996 00:00:00 81 12/05/1996 00:00:00 82 12/06/1996 00:00:00 83 12/09/1996 00:00:00 84 12/10/1996 00:00:00 85 12/11/1996 00:00:00 86 12/12/1996 00:00:00 87 12/13/1996 00:00:00 88 12/16/1996 00:00:00 89 12/17/1996 00:00:00 90 12/18/1996 00:00:00 91 12/19/1996 00:00:00 92 12/20/1996 00:00:00 93 12/29/1996 00:00:00 94 12/28/1996 00:00:00 95 12/26/1996 00:00:00 96 12/27/1996 00:00:00 97 12/30/1996 00:00:00 98 01/04/1997 00:00:00 99  Value returned by IsValid = 0 Value returned by ValidateTS = 0 DisplayValTS: Testing DisplayValTS: TSWRN: the input time series has errors. See the message for details message output by validateTS: TSERR: the input time series is unsorted TSERR: the time series has null timestamps TSERR: the time series has timestamps < calendar minDate (refer LoDateTab) TSERR: the time series has timestamps > calendar maxDate (refer HiDateTab) TSERR: the time series has imprecise timestamps (refer impreciseDateTab) TSERR: the time series has duplicate timestamps (refer DuplicateDateTab) list of dates < calendar minDate  lowDateTab : 10/26/1996 00:00:00 10/27/1996 00:00:00 list of dates > calendar maxDate  hiDateTab : 01/06/1997 00:00:00 01/07/1997 00:00:00 list of imprecise dates  impreciseDateTab : 11/22/1996 01:00:00 11/22/1996 15:00:00 list of duplicate dates  duplicateDateTab : 11/22/1996 15:00:00 11/29/1996 00:00:00 ExtraDateTab : 12/28/1996 00:00:00 12/29/1996 00:00:00 01/04/1997 00:00:00 MissingDateTab : 10/28/1996 00:00:00 10/29/1996 00:00:00 10/30/1996 00:00:00 10/31/1996 00:00:00 11/14/1996 00:00:00 11/15/1996 00:00:00 11/18/1996 00:00:00 11/19/1996 00:00:00 11/20/1996 00:00:00 11/21/1996 00:00:00 11/25/1996 00:00:00 11/26/1996 00:00:00 11/27/1996 00:00:00 12/02/1996 00:00:00 12/23/1996 00:00:00 12/24/1996 00:00:00 12/31/1996 00:00:00 01/01/1997 00:00:00 01/02/1997 00:00:00 01/03/1997 00:00:00