Oracle8i Time Series User's Guide
Release 8.1.5

Part Number A67294-01

Library

Product

Contents

Index

Go to previous page Go to next page

5
Time Series Functions: Reference

The Oracle8i Time Series library consists of:

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

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

Syntax notes:

All time series and time scaling 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 returns an instance, and any other functions in the nesting accept an instance and return an instance.

For an explanation of the reference-based interface, see Section 2.7.2.


Cavg

Format

ORDSYS.TimeSeries.Cavg(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

startDate

Starting date within the time series for which the cumulative average is to be computed. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the cumulative average is to be computed. If endDate is specified, startDate must also be specified.

Usage

Only non-null values are considered in computing the cumulative average.

An exception is returned if one or more of the following conditions are true:

If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative average is computed.

Example

Return the cumulative average of the closing price of stock ACME for November 1996:

SELECT to_char(tstamp) tstamp, value 
FROM tsquick ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Cavg(ts.close,to_date('01-NOV-96','DD-MON-YY'),
             to_date('30-NOV-96','DD-MON-YY')) 
       ) AS ORDSYS.ORDTNumTab)) t 
WHERE ts.ticker='ACME';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96         59
04-NOV-96       59.5
05-NOV-96         60
06-NOV-96       60.5
07-NOV-96         61
08-NOV-96       61.5
11-NOV-96         62
12-NOV-96       62.5
13-NOV-96         63
14-NOV-96       63.5
15-NOV-96         64
18-NOV-96       64.5
19-NOV-96         65
20-NOV-96       65.5
21-NOV-96         66
22-NOV-96       66.5
25-NOV-96         67
26-NOV-96       67.5
27-NOV-96         68
29-NOV-96       68.5
20 rows selected.

Cmax

Format

ORDSYS.TimeSeries.Cmax(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

startDate

Starting date within the time series for which the cumulative maximum is to be returned. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the cumulative maximum is to be returned. If endDate is specified, startDate must also be specified.

Usage

Only non-null values are considered in determining the cumulative maximum.

An exception is returned if one or more of the following conditions are true:

If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative maximum is computed.

Example

Return the cumulative maximum of the closing price of stock ACME for November 1996:

SELECT to_char(tstamp) tstamp, value 
FROM tsquick ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Cmax(ts.close,to_date('01-NOV-96','DD-MON-YY'),
             to_date('30-NOV-96','DD-MON-YY')) 
       ) AS ORDSYS.ORDTNumTab)) t 
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
--------- ----------
01-NOV-96         59
04-NOV-96         60
05-NOV-96         61
06-NOV-96         62
07-NOV-96         63
08-NOV-96         64
11-NOV-96         65
12-NOV-96         66
13-NOV-96         67
14-NOV-96         68
15-NOV-96         69
18-NOV-96         70
19-NOV-96         71
20-NOV-96         72
21-NOV-96         73
22-NOV-96         74
25-NOV-96         75
26-NOV-96         76
27-NOV-96         77
29-NOV-96         78
20 rows selected.

Cmin

Format

ORDSYS.TimeSeries.Cmin(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

startDate

Starting date within the time series for which the cumulative minimum is to be returned. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the cumulative minimum is to be returned. If endDate is specified, startDate must also be specified.

Usage

Only non-null values are considered in determining the cumulative minimum.

An exception is returned if one or more of the following conditions are true:

If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative minimum is computed.

Example

Return the cumulative minimum of the closing price of stock ACME for November 1996:

SELECT to_char(tstamp) tstamp, value 
FROM tsquick ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Cmin(ts.close,to_date('01-NOV-96','DD-MON-YY'),
             to_date('30-NOV-96','DD-MON-YY')) 
       ) AS ORDSYS.ORDTNumTab)) t 
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
--------- ----------
01-NOV-96         59
04-NOV-96         59
05-NOV-96         59
06-NOV-96         59
07-NOV-96         59
08-NOV-96         59
11-NOV-96         59
12-NOV-96         59
13-NOV-96         59
14-NOV-96         59
15-NOV-96         59
18-NOV-96         59
19-NOV-96         59
20-NOV-96         59
21-NOV-96         59
22-NOV-96         59
25-NOV-96         59
26-NOV-96         59
27-NOV-96         59
29-NOV-96         59
20 rows selected.

Cprod

Format

ORDSYS.TimeSeries.Cprod(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

startDate

Starting date within the time series for which the cumulative product is to be computed. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the cumulative product is to be computed. If endDate is specified, startDate must also be specified.

Usage

Only non-null values are considered in computing the cumulative product.

An exception is returned if one or more of the following conditions are true:

If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative product is computed.

Example

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 to_char(tstamp) tstamp, value 
FROM tsquick ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Cprod(ts.volume,to_date('01-NOV-96','DD-MON-YY'),
             to_date('06-NOV-96','DD-MON-YY')) 
       ) AS ORDSYS.ORDTNumTab)) t 
WHERE ts.ticker='ACME';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96       1000
04-NOV-96    1000000
05-NOV-96 1000000000
06-NOV-96 1.0000E+12
4 rows selected.

Csum

Format

ORDSYS.TimeSeries.Csum(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

startDate

Starting date within the time series for which the cumulative sum is to be computed. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the cumulative sum is to be computed. If endDate is specified, startDate must also be specified.

Usage

Only non-null values are considered in computing the cumulative sum.

An exception is returned if one or more of the following conditions are true:

If startDate and endDate are specified, the time series is trimmed to the date range before the cumulative sum is computed.

Example

Return the cumulative sum of the daily volume of stock ACME for November 1996:

SELECT to_char(tstamp) tstamp, value 
FROM tsquick ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Csum(ts.volume,to_date('01-NOV-96','DD-MON-YY'),
             to_date('30-NOV-96','DD-MON-YY')) 
       ) AS ORDSYS.ORDTNumTab)) t 
WHERE ts.ticker='ACME';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96       1000
04-NOV-96       2000
05-NOV-96       3000
06-NOV-96       4000
07-NOV-96       5000
08-NOV-96       6000
11-NOV-96       7000
12-NOV-96       8000
13-NOV-96       9000
14-NOV-96      10000
15-NOV-96      11000
18-NOV-96      12000
19-NOV-96      13000
20-NOV-96      14000
21-NOV-96      15000
22-NOV-96      16000
25-NOV-96      17000
26-NOV-96      18000
27-NOV-96      19000
29-NOV-96      20000
20 rows selected.

DeriveExceptions

Format

Approach 1:

ORDSYS.TimeSeries.DeriveExceptions(

inputTS ORDTNumSeriesIOTRef

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.TimeSeries.DeriveExceptions(

inputTS ORDTVarchar2SeriesIOTRef

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

Approach 1A:

ORDSYS.TimeSeries.DeriveExceptions(

inputCal IN ORDSYS.ORDTCalendar,

DateTab IN ORDSYS.ORDTDateTab

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

Approach 2:

ORDSYS.TimeSeries.DeriveExceptions(

series1 ORDTNumSeriesIOTRef,

series2 ORDTNumSeriesIOTRef

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.TimeSeries.DeriveExceptions(

series1 ORDTVarchar2SeriesIOTRef,

series2 ORDTVarchar2SeriesIOTRef

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

Description

Derives calendar exceptions from a time series (Approach 1), a calendar and a table of dates (Approach 1A), or two time series (Approach 2).

Parameters

inputTS

The time series whose calendar is to be used as the basis for the returned calendar and whose timestamps are to be used to populate the off- and on-exceptions lists of the returned calendar.

startDate

Starting date within the time series for which the exceptions are to be derived. If startDate is not specified, it is the minDate of the calendar and endDate is the maxDate of the calendar.

endDate

Ending date within the time series for which the exceptions are to be derived. If endDate is specified, startDate must also be specified. If startDate is specified and endDate is not specified, endDate is the maxDate of the calendar.

inputCal

The calendar that contains no exceptions and for which exceptions are to be derived.

DateTab

The table of dates that includes all dates in the time series (for example, all dates on which stock XYZ traded).

series1

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).

series2

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).

Usage

Approach 1 is the most convenient method. You specify a time series (for example, daily closing prices of stock XYZ) that has an associated calendar. A calendar is returned that is defined on the same pattern and frequency as the calendar for the input time series, and the exceptions lists of the returned calendar are populated to be consistent with the time series data.

Approach 1A is a variation of Approach 1 in which you specify a calendar and a table of the desired timestamps (for example, dates on which stock XYZ traded).

Approach 2 involves creating a time series (series1) that in effect functions as a calendar, and then using a second time series (series2) with desired timestamps to populate the exceptions lists. Approach 2 offers a performance advantage if you need to derive exceptions for many calendars based on many time series.

See Section 2.2.5 for a detailed explanation of the approaches to using this function.

Example

See Section 3.8 for examples of the approaches to using this function.


Display

Format

ORDSYS.TimeSeries.Display(

ts ORDSYS.[see parameter description]

[,mesg VARCHAR2]

) RETURN INTEGER;

Description

Displays various information (see the description of the ts parameter) using DBMS_OUTPUT routines.

Parameters

ts

The object to be displayed. Because the function is overloaded, this parameter can be any of the following data types:

mesg

Optional message text to be included in the display heading ("Timeseries dump for <mesg>").

Usage

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:

Example

Display the output for a query that returns the 10 highest closing prices for stock ACME for the month of November 1996:

SET SERVEROUTPUT ON
DECLARE
     tmp INTEGER;
BEGIN
SELECT ORDSYS.TimeSeries.Display( 
         ORDSYS.TimeSeries.TSMaxN(close,10, 
            to_date('11011996','MMDDYYYY'), 
            to_date('11301996','MMDDYYYY'))) 
         INTO tmp
FROM TSDEV.stockdemo_ts 
WHERE ticker ='ACME';
END;
/

This example might produce the following output:

Tab Data:
 -----------------------------
 Date                    Value
 29-NOV-96      78
 27-NOV-96      77
 26-NOV-96      76
 25-NOV-96      75
 22-NOV-96      74
 21-NOV-96      73
 20-NOV-96      72
 19-NOV-96      71
 18-NOV-96      70
 15-NOV-96      69
 -----------------------------

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('11011996','MMDDYYYY'), 
            to_date('11301996','MMDDYYYY'))) 
FROM TSDEV.stockdemo_ts 
WHERE ticker ='ACME';

See the TSMaxN function for an example that returns the same information, but that uses a subquery instead of the Display function.


DisplayValTS Procedure

Format

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

);

Description

Displays the results returned by the ValidateTS function.


Note:

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


Parameters

validFlag

The return value from the ValidateTS function.

outMessage

The diagnostic returned by the ValidateTS function.

loDateTab

A table of dates before the starting date of the calendar associated with the time series.

hiDateTab

A table of dates after the starting date of the calendar associated with the time series.

impreciseDateTab

A table of the imprecise dates found in the time series.

duplicateDateTab

A table of the duplicate dates (dates that appear more than once in the time series).

extraDateTab

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 Monday-Friday calendar and that is not an on-exception).

missingDateTab

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 Monday-Friday calendar and for which there is no data). Such dates can be considered as "holes" in the time series.

mesg

Optional message.

Usage

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.

Example

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 tsdev.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 = OPEN ACME
Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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:

TS-SUC: the input time series is a valid time series



AN INVALID TIME SERIES :

Name = OPEN ACME
Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 10/28/1996 00:00:00
 MaxDate = 01/05/1997 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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:

TS-WRN: the input time series has errors. See the message for details

message output by validateTS:

TS-ERR: the input time series is unsorted
TS-ERR: the time series has null timestamps
TS-ERR: the time series has timestamps < calendar minDate (refer LoDateTab)
TS-ERR: the time series has timestamps > calendar maxDate (refer HiDateTab)
TS-ERR: the time series has imprecise timestamps (refer impreciseDateTab)
TS-ERR: 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

ExtractCal

Format

ORDSYS.TimeSeries.ExtractCal(

ts ORDSYS.ORDTNumSeriesIOTRef

) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.TimeSeries.ExtractCal(

ts ORDSYS.ORDTVarchar2SeriesIOTRef

) RETURN ORDSYS.ORDTCalendar;

Description

Given a time series, returns a calendar that is the same as the calendar on which the time series is based.

Parameters

ts

The input time series.

Usage

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.

Example

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 TSDEV.stockdemo_ts ts
 WHERE ts.ticker='ACME';

END;
/

This example might produce the following output:

ExtractCal Results :

Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
 offExceptions :
     11/28/1996 00:00:00     12/25/1996 00:00:00

ExtractDate

Format

ORDSYS.TimeSeries.ExtractDate(

cell ORDSYS.ORDTNumCell

) RETURN DATE;

or

ORDSYS.TimeSeries.ExtractDate(

cell ORDSYS.ORDTVarchar2Cell

) RETURN DATE;

Description

Given an element in a time series, returns the date.

Parameters

cell

The time series element for which you want the date.

Usage

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.

Example

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 TSDEV.stockdemo_ts
       WHERE ticker = 'ACME';

This example might produce the following output:

TO_CHAR(ORDSYS.TIME
-------------------
11/14/1996 00:00:00
1 row selected.

ExtractTable

Format

ORDSYS.TimeSeries.ExtractTable(

ts ORDSYS.ORDTNumSeriesIOTRef

) RETURN ORDSYS.ORDTNumTab;

or

ORDSYS.TimeSeries.ExtractTable(

ts ORDSYS.ORDTVarchar2SeriesIOTRef

) RETURN ORDSYS.ORDTVarchar2Tab;

Description

Given a time series, returns the time series table (ORDTNumTab or ORDTVarchar2Tab) associated with the time series.

Parameters

ts

The input time series.

Usage

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.

Example

Return the closing prices for stock ACME:

SELECT * FROM the
  (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(ts.close) 
               as ORDSYS.ORDTNumTab)
       FROM TSDEV.stockdemo_ts ts
       WHERE ts.ticker='ACME');

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96         59
04-NOV-96         60
05-NOV-96         61
   ...                     ...
31-DEC-96         99
41 rows selected.

ExtractValue

Format

ORDSYS.TimeSeries.ExtractValue(

cell ORDSYS.ORDTNumCell

) RETURN NUMBER;

or

ORDSYS.TimeSeries.ExtractValue(

cell ORDSYS.ORDTVarchar2Cell

) RETURN VARCHAR2;

Description

Given an element in a time series, returns the value stored in it.

Parameters

cell

The time series element for which you want the value.

Usage

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.

Example

Return the value of the tenth opening price in the stockdemo_ts table:

SELECT ORDSYS.TimeSeries.ExtractValue(
         ORDSYS.TimeSeries.GetNthElement(open, 10))
  FROM TSDEV.stockdemo_ts
  WHERE ticker = 'ACME';

This example might produce the following output:

ORDSYS.TIM
----------
        68
1 row selected.

Fill

Format

ORDSYS.TimeSeries.Fill(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef

[, fill_type INTEGER]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

fill_type

One of the following integers indicating how missing values are to be filled:

If fill_type is omitted, 0 is assumed.

Usage

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 30-Jan-1997 (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 30-Jan-1997 with each of the fill_type parameter values:

fill_type   Closing Price for 30-Jan-1997  

0  

null  

1  

49  

2  

50  

Some potential uses for this function include:

An exception is returned if the specified fill_type value is not 0, 1, or 2.

Example

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 time series with missing dates.
-- In the following example, the time series 'FOO' has some missing dates
-- (07-DEC-1996 and 08-DEC-1996). 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 time series by from XCORP's high (repeat forward).
 SELECT ORDSYS.TimeSeries.Display(
           ORDSYS.TimeSeries.Fill(ts, 1),
           'Fill Forward') INTO dummyval
 FROM dual;

 -- Generate a time series by from XCORP's high (repeat backward).
 SELECT ORDSYS.TimeSeries.Display(
           ORDSYS.TimeSeries.Fill(ts, 2),
           'Fill Backward') INTO dummyval
 FROM dual;

 -- Generate a time series 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 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 01/01/1997 00:00:00
 patBits:
          1,1,1,1,1,1,1
 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 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 01/01/1997 00:00:00
 patBits:
          1,1,1,1,1,1,1
 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/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 (day)
 MinDate = 01/01/1996 00:00:00
 MaxDate = 01/01/1997 00:00:00
 patBits:
          1,1,1,1,1,1,1
 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
 -----------------------------

First

Format

ORDSYS.TimeSeries.First(

ts ORDSYS.ORDTNumSeriesIOTRef

) RETURN ORDSYS.ORDTNumCell;

Description

Given a time series, returns the first element in it.

Parameters

ts

The input time series.

Usage

A null is returned if the time series (ts) is empty.

An exception is returned if the time series (ts) is null.

Example

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 TSDEV.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

FirstN

Format

ORDSYS.TimeSeries.FirstN(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

NumValues NUMBER

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

Given a time series and a number of elements (NumValues) to return, returns the first NumValues elements in the time series.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

NumValues

Number of elements from the beginning of the time series to be returned.

startDate

Starting date within the time series for which NumValues elements are to be returned. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which NumValues elements are to be returned. If endDate is specified, startDate must also be specified.

Usage

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, if NumValues is zero (0) or negative, or if endDate is earlier than startDate.

If startDate and endDate are specified, the time series is trimmed to the date range before the first NumValues cells are returned.

Example

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 TSDEV.stockdemo_ts ts
 WHERE ts.ticker='ACME';

END;
/

This example might produce the following output:

FirstN Results :

Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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
 -----------------------------

GetDatedElement

Format

ORDSYS.TimeSeries.GetDatedElement (

ts ORDSYS.ORDTNumSeriesIOTRef,

target_date date

) RETURN ORDSYS.ORDTNumCell;

Description

Given a time series and a date, returns the time series element for that date.

Parameters

ts

The input time series.

target_date

Positive integer specifying the date of the element to be returned.

Usage

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.

Example

Return the timestamp and opening price for 26-Nov-1996 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 26-NOV-1996 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 TSDEV.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

GetNthElement

Format

ORDSYS.TimeSeries.GetNthElement

(ts ORDSYS.ORDTNumSeriesIOTRef,

target_index INTEGER

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumCell;

Description

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.

Parameters

ts

The input time series.

target_index

Positive integer specifying the position of the element to be returned.

startDate

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.

endDate

Ending date within the time series to which target_index is to be applied. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the tenth opening price for stock ACME:

SELECT ORDSYS.TimeSeries.ExtractValue(
         ORDSYS.TimeSeries.GetNthElement(open, 10))
  FROM TSDEV.stockdemo_ts
  WHERE ticker = 'ACME';

This example might produce the following output:

ORDSYS.TIM
----------
        68
1 row selected.

GetSeries

Format

ORDSYS.TimeSeries.GetSeries(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.GetSeries(

[tsname VARCHAR2,]

ts ORDSYS.ORDTVarchar2SeriesIOTRef

) RETURN ORDSYS.ORDTVarchar2Series;

Description

Given a reference to a time series of references (ORDTNumSeriesIOTRef or ORDTVarchar2SeriesIOTRef), returns a time series instance (ORDTNumSeries or ORDTVarchar2Series).

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

Usage

The function materializes the input time series.

An exception is returned if the time series (ts) is null.

Example

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 TSDEV.stockdemo_ts ts
 WHERE ts.ticker='ACME';

END;
/

This example might produce the following output:

GetSeries Results :

Name = OPEN ACME
Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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
 -----------------------------

IsValidTS

Format

ORDSYS.TimeSeries.IsValidTS(

ts ORDSYS.ORDTNumSeriesIOTRef

) RETURN INTEGER;

or

ORDSYS.TimeSeries.IsValidTS(

ts ORDSYS.ORDTVarchar2SeriesIOTRef

) RETURN INTEGER;

Description

Returns 1 if the time series is valid and 0 if the time series is invalid.

Parameters

ts

The input time series.

Usage

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, generates a diagnostic message and tables with timestamps that are causing the time series to be invalid.

Example

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 tsdev.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 = OPEN ACME
Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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:

TS-SUC: the input time series is a valid time series



AN INVALID TIME SERIES :

Name = OPEN ACME
Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 10/28/1996 00:00:00
 MaxDate = 01/05/1997 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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:

TS-WRN: the input time series has errors. See the message for details

message output by validateTS:

TS-ERR: the input time series is unsorted
TS-ERR: the time series has null timestamps
TS-ERR: the time series has timestamps < calendar minDate (refer LoDateTab)
TS-ERR: the time series has timestamps > calendar maxDate (refer HiDateTab)
TS-ERR: the time series has imprecise timestamps (refer impreciseDateTab)
TS-ERR: 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

Lag

Format

ORDSYS.TimeSeries.Lag (

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

units INTEGER

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.Lag (

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

lead_date DATE

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

units

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.

lead_date

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.)

startDate

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.

endDate

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.

Usage

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 06-Jan-1997 (Monday) and the units value is 2, the first timestamp in the output time series is 02-Jan-1997 (Thursday) and its associated value (such as closing price) is the same as that for 06-Jan-1997 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 5-1 shows some time series data with a two-day lag period.

Table 5-1 Lagging a Time Series by Two Days
Input Time Series:   Output Time Series:  
Timestamp  Closing Price  Timestamp  Closing Price 

06-Jan-1997  

49.50  

02-Jan-1997  

49.50  

07-Jan-1997  

49.25  

03-Jan-1997  

49.25  

08-Jan-1997  

50.00  

06-Jan-1997  

50.00  

...  

...  

...  

...  

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.

The Lead and Lag functions do not operate on irregular time series. For an explanation of irregular time series, see Section 2.1.1.

Example

Return a time series starting with 03-Mar-1997 using closing prices from the time series from 01-Nov-1996 through 30-Nov-1996 for stock ACME. The returned time series has the same number of timestamps as are in the specified date range (startDate through endDate).

SELECT to_char(tstamp) tstamp, value
FROM stockdemo_ts ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Lag(ts.close,
               to_date('03-MAR-97','DD-MON-YY'),
               to_date('01-NOV-96','DD-MON-YY'),
               to_date('30-NOV-96','DD-MON-YY'))
           ) AS ORDSYS.ORDTNumTab)) t 
WHERE ts.ticker='ACME';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
03-MAR-97         59
04-MAR-97         60
05-MAR-97         61
06-MAR-97         62
07-MAR-97         63
10-MAR-97         64
  ...            ...
27-MAR-97         77
28-MAR-97         78
20 rows selected.

Last

Format

ORDSYS.TimeSeries.Last(

ts ORDSYS.ORDTNumSeriesIOTRef

) RETURN ORDSYS.ORDTNumCell;

Description

Given a time series, returns the last element in it.

Parameters

ts

The input time series.

Usage

A null is returned if the time series (ts) is empty.

An exception is returned if the time series (ts) is null.

Example

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 TSDEV.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

LastN

Format

ORDSYS.TimeSeries.LastN(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

NumValues NUMBER

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

Given a time series and a number of elements (NumValues) to return, returns the last NumValues elements in the time series.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

NumValues

Number of elements from the end of the time series to be returned.

startDate

Starting date within the time series for which NumValues elements are to be returned. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which NumValues elements are to be returned. If endDate is specified, startDate must also be specified.

Usage

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, if NumValues is zero (0) or negative, or if endDate is earlier than startDate.

If startDate and endDate are specified, the time series is trimmed to the date range before the last NumValues cells are returned.

Example

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 TSDEV.stockdemo_ts ts
 WHERE ts.ticker='ACME';

END;
/

This example might produce the following output:

LastN Results :

Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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
 -----------------------------

Lead

Format

ORDSYS.TimeSeries.Lead (

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

units INTEGER

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.Lead (

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

lead_date DATE

[, startDate DATE

, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

units

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.

lead_date

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.)

startDate

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.

endDate

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.

Usage

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 02-Jan-1997 (Thursday) and the units value is 2, the first timestamp in the output time series is 06-Jan-1997 (Monday) and its associated value (such as closing price) is the same as that for 02-Jan-1997 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 5-2 shows some time series data with a two-day lead period.

Table 5-2 Leading a Time Series by Two Days
Input Time Series:   Output Time Series:  
Timestamp  Closing Price  Timestamp  Closing Price 

02-Jan-1997  

49.00  

06-Jan-1997  

49.00  

03-Jan-1997  

50.00  

07-Jan-1997  

50.00  

06-Jan-1997  

49.50  

08-Jan-1997  

49.50  

...  

...  

...  

...  

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.

The Lead and Lag functions do not operate on irregular time series. For an explanation of irregular time series, see Section 2.1.1.

Example

Return a time series starting with 03-Mar-1997 using closing prices from the time series from 01-Nov-1996 through 30-Nov-1996 for stock ACME. The returned time series has the same number of timestamps as are in the specified date range (startDate through endDate).

SELECT to_char(tstamp) tstamp, value
FROM stockdemo_ts ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Lead(ts.close,
               to_date('03-MAR-97','DD-MON-YY'),
               to_date('01-NOV-96','DD-MON-YY'),
               to_date('30-NOV-96','DD-MON-YY'))
           ) AS ORDSYS.ORDTNumTab)) t 
WHERE ts.ticker='ACME';

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
03-MAR-97         59
04-MAR-97         60
05-MAR-97         61
06-MAR-97         62
07-MAR-97         63
10-MAR-97         64
  ...            ...
27-MAR-97         77
28-MAR-97         78
20 rows selected.


Mavg

Format

ORDSYS.TimeSeries.Mavg(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

k INTEGER

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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 30-day 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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

k

Positive integer specifying the lookback window (number of timestamps, including the current one, over which to compute the moving average).

startDate

Starting date within the time series for which to return moving averages. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which to return moving averages. If endDate is specified, startDate must also be specified.

Usage

The returned time series has nulls for any entry where there are not k-1 timestamps preceding it in the calendar. For example, if a stock trading calendar for 1997 starts on 02-Jan-1997, the series of 5-day moving averages of the closing price for a stock for the year has nulls for the closing price for the first four timestamps (02-Jan, 03-Jan, 06-Jan, and 07-Jan), 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.4.1.

An exception is returned if one or more of the following conditions are true:

Example

Return a table of 10-day moving average values of the closing price for stock ACME for the month of December 1996:

SELECT to_char(tstamp) tstamp, value 
FROM tsquick ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Mavg(ts.close,to_date('01-DEC-96','DD-MON-YY'),
             to_date('31-DEC-96','DD-MON-YY'),10) 
       ) AS ORDSYS.ORDTNumTab)) t 
WHERE ts.ticker='ACME'; 

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
02-DEC-96       74.5
03-DEC-96       75.5
04-DEC-96       76.5
05-DEC-96       77.5
06-DEC-96       78.5
09-DEC-96       79.5
10-DEC-96       80.5
11-DEC-96       81.5
12-DEC-96       82.5
13-DEC-96       83.5
16-DEC-96       84.5
17-DEC-96       85.5
18-DEC-96       86.5
19-DEC-96       87.5
20-DEC-96       88.5
23-DEC-96       89.5
24-DEC-96       90.5
26-DEC-96       91.5
27-DEC-96       92.5
30-DEC-96       93.5
31-DEC-96       94.5
21 rows selected.

Msum

Format

ORDSYS.TimeSeries.Msum(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

k INTEGER

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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 30-day 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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

k

Positive integer specifying the lookback window (number of timestamps, including the current one, over which to compute the moving sum).

startDate

Starting date within the time series for which to return moving sums. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which to return moving sums. If endDate is specified, startDate must also be specified.

Usage

The returned time series has nulls for any entry where there are not k-1 timestamps preceding it in the calendar. For example, if a stock trading calendar for 1997 starts on 02-Jan-1997, the series of 5-day moving sums of the trading volume for a stock for the year has nulls for the volume for the first four timestamps (02-Jan, 03-Jan, 06-Jan, and 07-Jan), 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.4.1.

An exception is returned if one or more of the following conditions are true:

Example

Return a table of 30-day moving sum values of trading volume for stock ACME for December 1996:

SELECT to_char(tstamp) tstamp, value 
FROM tsquick ts, 
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable( 
             ORDSYS.TimeSeries.Msum(ts.volume,to_date('01-DEC-96','DD-MON-YY'),
             to_date('31-DEC-96','DD-MON-YY'),30) 
       ) AS ORDSYS.ORDTNumTab)) t 
WHERE ts.ticker='ACME'; 

With the simplified data in the demo database (where all ACME daily volumes are 1000 and there are no ACME timestamps before November 1996), this example might produce the following output:

TSTAMP    VALUE
--------- ----------
02-DEC-96
03-DEC-96
04-DEC-96
05-DEC-96
06-DEC-96
09-DEC-96
10-DEC-96
11-DEC-96
12-DEC-96
13-DEC-96      30000
16-DEC-96      30000
17-DEC-96      30000
18-DEC-96      30000
19-DEC-96      30000
20-DEC-96      30000
23-DEC-96      30000
24-DEC-96      30000
26-DEC-96      30000
27-DEC-96      30000
30-DEC-96      30000
31-DEC-96      30000
21 rows selected.

TrimSeries

Format

ORDSYS.TimeSeries.TrimSeries(

[tsname VARCHAR2,]

ts ORDSYS.ORDTNumSeriesIOTRef,

startDate DATE,

endDate DATE

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TrimSeries(

[tsname VARCHAR2,]

ts ORDSYS.ORDTVarchar2SeriesIOTRef,

startDate DATE,

endDate DATE

) RETURN ORDSYS.ORDTVarchar2Series;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts

The input time series.

startDate

Starting date within the time series. You must specify a value, either null or not null. If you specify a null value, the starting date (minDate) of the calendar (if any) associated with ts is used.

endDate

Ending date within the time series. You must specify a value, either null or not null. If you specify a null value, the ending date (maxDate) of the calendar (if any) associated with ts is used.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the opening prices for stock ACME for dates in the calendar from 01-Dec-1996 through 31-Dec-1996:

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 TSDEV.stockdemo_ts
    WHERE ticker = 'ACME';
END;
/

This statement might produce the following output:

Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 01-JAN-90 00:00:00
 MaxDate = 01-JAN-01 00:00:00
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 07-JAN-96 00:00:00
 onExceptions  :
 offExceptions :
     28-NOV-96 00:00:00     25-DEC-96 00:00:00
Series Data:
 -----------------------------
 Date                    Value
 02-DEC-96 00:00:00      79
 03-DEC-96 00:00:00      80
 04-DEC-96 00:00:00      81
 05-DEC-96 00:00:00      82
 06-DEC-96 00:00:00      83
 09-DEC-96 00:00:00      84
 10-DEC-96 00:00:00      85
 11-DEC-96 00:00:00      86
 12-DEC-96 00:00:00      87
 13-DEC-96 00:00:00      88
 16-DEC-96 00:00:00      89
 17-DEC-96 00:00:00      90
 18-DEC-96 00:00:00      91
 19-DEC-96 00:00:00      92
 20-DEC-96 00:00:00      93
 23-DEC-96 00:00:00      94
 24-DEC-96 00:00:00      95
 26-DEC-96 00:00:00      96
 27-DEC-96 00:00:00      97
 30-DEC-96 00:00:00      98
 31-DEC-96 00:00:00      99
 -----------------------------

TSAdd

Format

ORDSYS.TimeSeries.TSAdd (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

ts2 ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSAdd (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

k NUMBER

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts1

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.

ts2

The time series whose elements are to be added to corresponding elements in the first time series.

k

A constant to be added to corresponding elements in the first time series.

startDate

Starting date within the time series for which the addition is to be performed. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the addition is to be performed. If endDate is specified, startDate must also be specified.

Usage

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 one or more of the following conditions are true:

Example

Add the high price for stock ACME and the low price for stock FUNCO for each trading day from 14-Nov-1996 through 14-Dec-1996:

CONNECT TSUSER/TSUSER
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 TSDEV.stockdemo_ts ts1, TSDEV.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 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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
 -----------------------------

TSAvg

Format

ORDSYS.TimeSeries.TSAvg (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the average of all non-null time series entries.

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the average is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the average is to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

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 TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

ORDSYS.TIM ORDSYS.TIM ORDSYS.TIM
---------- ---------- ----------
        79      143.5 11.9791486
1 row selected.

TSCount

Format

ORDSYS.TimeSeries.TSCount (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the count of all non-null time series entries.

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the count is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the count is to be calculated. If endDate is specified, startDate must also be specified.

Usage

Nulls are ignored in computing the count.

An exception is returned if one or more of the following conditions are true:

Example

Return the total number of daily closing prices for stock ACME for the month of November 1996:

SELECT ORDSYS.TimeSeries.TSCount(close,
                        to_date('11/01/1996 00:00:00',
                                'MM/DD/YYYY HH24:MI:SS'),
                        to_date('11/30/1996 23:59:59',
                                'MM/DD/YYYY HH24:MI:SS')) TSCount
  FROM TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

TSCOUNT
----------
        20
1 row selected.

TSDivide

Format

ORDSYS.TimeSeries.TSDivide (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

ts2 ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSDivide (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

k NUMBER

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts1

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.

ts2

The time series whose elements are to be divided into corresponding elements in the first time series.

k

A constant to be divided into corresponding elements in the first time series.

startDate

Starting date within the time series for which the division is to be performed. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the division is to be performed. If endDate is specified, startDate must also be specified.

Usage

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 one or more of the following conditions are true:

Example

Divide the high price for stock ACME by the low price for stock FUNCO for each trading day from 14-Nov-1996 through 14-Dec-1996:

CONNECT TSUSER/TSUSER
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 TSDEV.stockdemo_ts ts1, TSDEV.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 (day)
 MinDate = 11/01/1996 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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
 -----------------------------

TSMax

Format

ORDSYS.TimeSeries.TSMax (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the highest (maximum) of all non-null time series entries.

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the maximum is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the maximum is to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the highest closing price for stock ACME for the month of November 1996:

SELECT ORDSYS.TimeSeries.TSMax(close,
                        to_date('11/01/1996 00:00:00',
                                'MM/DD/YYYY HH24:MI:SS'),
                        to_date('11/30/1996 23:59:59',
                                'MM/DD/YYYY HH24:MI:SS')) TSMax
  FROM TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

TSMAX
----------
        78
1 row selected.

TSMaxN

Format

ORDSYS.TimeSeries.TSMaxN (

ts ORDSYS.ORDTNumSeriesIOTRef,

NumValues INTEGER,

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumTab;

Description

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.

Parameters

ts

The input time series.

NumValues

Number of values to return.

startDate

Starting date within the time series for which the top values are to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the top values are to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the 10 highest closing prices for stock ACME for the month of November 1996:

SELECT * FROM THE( SELECT CAST( 
                  ORDSYS.TimeSeries.TSMaxN(close, 10,
                         to_date('11011996','MMDDYYYY'),
                         to_date('11301996','MMDDYYYY'))
                  as ORDSYS.ORDTNumTab)  
            FROM TSDEV.stockdemo_ts 
            WHERE ticker ='ACME');

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
29-NOV-96         78
27-NOV-96         77
26-NOV-96         76
25-NOV-96         75
22-NOV-96         74
21-NOV-96         73
20-NOV-96         72
19-NOV-96         71
18-NOV-96         70
15-NOV-96         69
10 rows selected.

TSMedian

Format

ORDSYS.TimeSeries.TSMedian (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the median of all non-null time series entries.

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the median is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the median is to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the median closing price for stock ACME for the month of November 1996:

SELECT ORDSYS.TimeSeries.TSMedian(close,
                        to_date('11/01/1996 00:00:00',
                                'MM/DD/YYYY HH24:MI:SS'),
                        to_date('11/30/1996 23:59:59',
                                'MM/DD/YYYY HH24:MI:SS')) TSMedian
  FROM TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

TSMEDIAN
----------
      68.5
1 row selected.

TSMin

Format

ORDSYS.TimeSeries.TSMin (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the lowest (minimum) of all non-null time series entries.

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the minimum is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the minimum is to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the lowest closing price for stock ACME for the month of November 1996:

SELECT ORDSYS.TimeSeries.TSMin(close,
                        to_date('11/01/1996 00:00:00',
                                'MM/DD/YYYY HH24:MI:SS'),
                        to_date('11/30/1996 23:59:59',
                                'MM/DD/YYYY HH24:MI:SS')) TSMin
  FROM TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

TSMIN
----------
        59
1 row selected.

TSMinN

Format

ORDSYS.TimeSeries.TSMinN (

ts ORDSYS.ORDTNumSeriesIOTRef,

NumValues INTEGER,

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumTab;

Description

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.

Parameters

ts

The input time series.

NumValues

Number of values to return.

startDate

Starting date within the time series for which the bottom values are to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the bottom values are to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the 10 lowest closing prices for stock ACME for the month of November 1996:

SELECT * FROM THE( SELECT CAST( 
                  ORDSYS.TimeSeries.TSMinN(close, 10,
                         to_date('11011996','MMDDYYYY'),
                         to_date('11301996','MMDDYYYY'))
                  as ORDSYS.ORDTNumTab)  
            FROM TSDEV.stockdemo_ts 
            WHERE ticker ='ACME');

This example might produce the following output:

TSTAMP    VALUE
--------- ----------
01-NOV-96         59
04-NOV-96         60
05-NOV-96         61
06-NOV-96         62
07-NOV-96         63
08-NOV-96         64
11-NOV-96         65
12-NOV-96         66
13-NOV-96         67
14-NOV-96         68
10 rows selected.

TSMultiply

Format

ORDSYS.TimeSeries.TSMultiply (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

ts2 ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSMultiply (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

k NUMBER

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts1

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.

ts2

The time series whose elements are to be multiplied by corresponding elements in the first time series.

k

A constant to be multiplied by corresponding elements in the first time series.

startDate

Starting date within the time series for which the multiplication is to be performed. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the multiplication is to be performed. If endDate is specified, startDate must also be specified.

Usage

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 one or more of the following conditions are true:

Example

Multiply the high price for stock ACME by the low price for stock FUNCO for each trading day from 14-Nov-1996 through 14-Dec-1996:

CONNECT TSUSER/TSUSER
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 TSDEV.stockdemo_ts ts1, TSDEV.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 (day)
 MinDate = 01/01/1990 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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
 -----------------------------

TSProd

Format

ORDSYS.TimeSeries.TSProd (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the product (result of multiplication) of all non-null time series entries.

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the product is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the product is to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the product resulting from multiplying the daily closing prices for stock ACME for the month of November 1996. (This example is not very plausible, but is presented merely to illustrate the syntax.)

SELECT ORDSYS.TimeSeries.TSProd(close,
                        to_date('11/01/1996 00:00:00',
                                'MM/DD/YYYY HH24:MI:SS'),
                        to_date('11/30/1996 23:59:59',
                                'MM/DD/YYYY HH24:MI:SS')) TSProd
  FROM TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

TSPROD
----------
4.8177E+36
1 row selected.

TSStdDev

Format

ORDSYS.TimeSeries.TSStdDev (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the standard deviation of all non-null time series entries. (This function returns a value that is the square root of the value returned by the TSVar function.)

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the standard deviation is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the standard deviation is to be calculated. If endDate is specified, startDate must also be specified.

Usage

If the date range refers to a time series with fewer than two timestamps, a null is returned.

An exception is returned if one or more of the following conditions are true:

Example

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 TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

ORDSYS.TIM ORDSYS.TIM ORDSYS.TIM
---------- ---------- ----------
        79      143.5 11.9791486
1 row selected.

TSSubtract

Format

ORDSYS.TimeSeries.TSSubtract (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

ts2 ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSSubtract (

[tsname VARCHAR2,]

ts1 ORDSYS.ORDTNumSeriesIOTRef,

k NUMBER

[,startDate DATE, endDate DATE]

) RETURN ORDSYS.ORDTNumSeries;

Description

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.

Parameters

tsname

Name of the returned time series. Specify this parameter if you need to assign a name to the resulting time series, for example, to insert it into an object table.

ts1

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.

ts2

The time series whose elements are to be subtracted from corresponding elements in the first time series.

k

A constant to be subtracted from corresponding elements in the first time series.

startDate

Starting date within the time series for which the subtraction is to be performed. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the subtraction is to be performed. If endDate is specified, startDate must also be specified.

Usage

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 one or more of the following conditions are true:

Example

Subtract the low price for stock FUNCO from the high price for stock ACME for each trading day from 14-Nov-1996 through 14-Dec-1996:

CONNECT TSUSER/TSUSER
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 TSDEV.stockdemo_ts ts1, TSDEV.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 (day)
 MinDate = 01/01/1990 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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
 -----------------------------

TSSum

Format

ORDSYS.TimeSeries.TSSum (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the sum of all non-null time series entries.

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the sum is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the sum is to be calculated. If endDate is specified, startDate must also be specified.

Usage

An exception is returned if one or more of the following conditions are true:

Example

Return the sum of the daily trading volumes for stock ACME for the month of November 1996 (that is, the total ACME volume for the month):

SELECT ORDSYS.TimeSeries.TSSum(volume,
                        to_date('11/01/1996 00:00:00',
                                'MM/DD/YYYY HH24:MI:SS'),
                        to_date('11/30/1996 23:59:59',
                                'MM/DD/YYYY HH24:MI:SS')) TSSum
  FROM TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

TSSUM
----------
     20000
1 row selected.

TSVariance

Format

ORDSYS.TimeSeries.TSVariance (

ts ORDSYS.ORDTNumSeriesIOTRef

[,startDate DATE, endDate DATE]

) RETURN NUMBER;

Description

Given an input ORDTNumSeries and optionally starting and ending dates, returns a number corresponding to the variance of all non-null time series entries. (This function is analogous to the SQL group function VAR.)

Parameters

ts

The input time series.

startDate

Starting date within the time series for which the variance is to be calculated. If startDate is specified, endDate must also be specified.

endDate

Ending date within the time series for which the variance is to be calculated. If endDate is specified, startDate must also be specified.

Usage

If the date range refers to a time series with fewer than two timestamps, a null is returned.

An exception is returned if one or more of the following conditions are true:

Example

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 TSDEV.stockdemo_ts
  WHERE ticker='ACME';

This example might produce the following output:

ORDSYS.TIM ORDSYS.TIM ORDSYS.TIM
---------- ---------- ----------
        79      143.5 11.9791486
1 row selected.

ValidateTS

Format

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;

Description

Checks whether a time series is valid, and if the time series is not valid, generates a diagnostic message and tables with timestamps that are causing the time series to be invalid.

Parameters

ts

The time series to be checked for validity.

outMesg

If the time series is invalid (if the return value = 0), contains a diagnostic message describing any problems.

loDateTab

A table of dates before the starting date of the calendar associated with the time series.

hiDateTab

A table of dates after the ending date of the calendar associated with the calendar.

impreciseDateTab

A table of the imprecise timestamps found in the time series.

duplicateDateTab

A table of the duplicate timestamps found in the time series.

extraDateTab

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 Monday-Friday calendar and that is not an on-exception).

missingDateTab

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 Monday-Friday calendar and for which there is no data). Such dates can be considered as "holes" in the time series.

Usage

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 to determine if 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.

Example

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 tsdev.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 = OPEN ACME
Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 01/01/1990 00:00:00
 MaxDate = 01/01/2001 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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:

TS-SUC: the input time series is a valid time series


AN INVALID TIME SERIES :

Name = OPEN ACME
Calendar Data:
Calendar Name = BUSINESS-96
 Frequency = 4 (day)
 MinDate = 10/28/1996 00:00:00
 MaxDate = 01/05/1997 00:00:00
 patBits:
          0,1,1,1,1,1,0
 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:

TS-WRN: the input time series has errors. See the message for details

message output by validateTS:

TS-ERR: the input time series is unsorted
TS-ERR: the time series has null timestamps
TS-ERR: the time series has timestamps < calendar minDate (refer LoDateTab)
TS-ERR: the time series has timestamps > calendar maxDate (refer HiDateTab)
TS-ERR: the time series has imprecise timestamps (refer impreciseDateTab)
TS-ERR: 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



Go to previous page
Prev
Go to next page
Next
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index