Oracle8 Time Series Cartridge User's Guide Release 8.0.4 A57501-01 |
Library | Product | Contents | Index

# 5
Time Series and Time Scaling Functions: Reference

The Oracle8 Time Series Cartridge library consists of:

- datatypes (described in Section 2.2.2)
- calendar functions (described in Chapter 4)
- time series and time scaling functions (described in this chapter)

Two separate reference chapters are provided for the functions because the functions described in each are typically done at different times in the application development cycle and by people performing different job roles:

- 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 are used mainly by application developers and some end users after the associated calendar or calendars have been defined.

Syntax notes:

- The ORDSYS schema name and the package name must be used with the function name, although public synonyms can be created to eliminate the need for specifying the schema name (see Section 1.4). Each function is included in a PL/SQL package, such as Calendar or TimeSeries. The ORDSYS schema name and the package name are included in the Format and in any examples.
- Function calls are not case sensitive, except for any quoted literal values. For example, the following code line excerpts are valid and semantically identical:

      select CAST(TimeSeries.ExtractTable(close) AS ORDTNumTab)
      select cast(TIMESERIES.extracttable(close) as ordtnumtab)
      select cast(TiMeSeRiEs.eXtRaCtTaBlE(ClosE) As ordtNUMtab)

- The syntax and examples show the reference-based interface (types ORDTNumSeriesIOTRef and ORDTVarchar2SeriesIOTRef).

Usage note:

All time series functions accept both references and instances as parameters. (For example, an ORDTNumSeriesIOTRef parameter could also be ORDTNumSeries.) All time series functions return instances. Thus, if you nest functions, such as Cmax(Cmax(...), ...), the innermost nesting accepts a reference and outputs an instance, and any other functions in the nesting accept an instance and output an instance.

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

## Cavg

### Format

ORDSYS.TimeSeries.Cavg(

  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

##### 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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 * FROM the
      (SELECT 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)
           FROM ORDTDEV.stockdemo_ts ts
           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(

  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

##### 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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 * FROM the
      (SELECT 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)
           FROM ORDTDEV.stockdemo_ts ts
           WHERE ts.ticker='ACME');

This example might produce the following output. (Note that this output reflects the simplified artificial data in the usage demo database, where the closing price rises one point each day.)

    TSTAMP    VALUE
    --------- ----------
    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(

  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

##### 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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 * FROM the
      (SELECT 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)
           FROM ORDTDEV.stockdemo_ts ts
           WHERE ts.ticker='ACME');

This example might produce the following output. (Note that this output reflects the simplified artificial data in the usage demo database, where the closing price rises one point each day.)

    TSTAMP    VALUE
    --------- ----------
    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(

  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

##### 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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 * FROM the
      (SELECT 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)
           FROM ORDTDEV.stockdemo_ts ts
           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(

  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

##### 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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 * FROM the
      (SELECT 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)
           FROM ORDTDEV.stockdemo_ts ts
           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(

  inputCal IN ORDSYS.ORDTCalendar,

  DateTab IN ORDSYS.ORDTDateTab

  ) RETURN ORDSYS.ORDTCalendar;

Approach 2:

ORDSYS.TimeSeries.DeriveExceptions(

  series1 ORDTNumSeriesIOTRef,

  series2 ORDTNumSeriesIOTRef

  ) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.TimeSeries.DeriveExceptions(

  series1 ORDTVarchar2SeriesIOTRef,

  series2 ORDTVarchar2SeriesIOTRef

  ) RETURN ORDSYS.ORDTCalendar;

### Description

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

### Parameters

##### 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

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

### Example

See Sections 3.3.1 and 3.3.2 for examples of the two 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 datatypes:

- ORDTNumSeriesIOTRef or ORDTNumSeries
- ORDTVarchar2SeriesIOTRef or ORDTVarcharSeries
- ORDTNumTab
- ORDTVarchar2Tab
- ORDTNumCell
- ORDTVarchar2Cell
- ORDTDateTab
- ORDTCalendar
- ORDTExceptions
- ORDTPattern 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:

- Output cannot exceed 1 megabyte.
- The Display function cannot be used with the OCI.
- SQL*Plus does not support DBMS_OUTPUT in the context of a SELECT statement, but it does support DBMS_OUTPUT for anonymous PL/SQL blocks.

### Example

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

    SET SERVEROUTPUT ON
    DECLARE
         tmp INTEGER;
    BEGIN
    SELECT ORDSYS.TimeSeries.Display(
             ORDSYS.TimeSeries.TSMaxN(close,10,
                to_date('01011996','MMDDYYYY'),
                to_date('01311996','MMDDYYYY')))
             INTO tmp
    FROM ORDTDEV.stocks_ts
    WHERE ticker ='AONE';
    END;
    /

This example might produce the following output:

    Tab Data:
     -----------------------------
     Date                    Value
     01/24/1996 00:00:00      43.9138
     01/25/1996 00:00:00      42.9925
     01/31/1996 00:00:00      42.9925
     01/26/1996 00:00:00      42.7413
     01/30/1996 00:00:00      42.7413
     01/29/1996 00:00:00      42.5738
     01/23/1996 00:00:00      41.9875
     01/22/1996 00:00:00      41.82
     01/19/1996 00:00:00      41.485
     01/18/1996 00:00:00      40.815
     -----------------------------

The preceding example works from both SQL*Plus and the Server Manager (svrmgrl) prompt. The following version of the example works from the Server Manager prompt but not from SQL*Plus:

    SET SERVEROUTPUT ON
    SELECT ORDSYS.TimeSeries.Display(
             ORDSYS.TimeSeries.TSMaxN(close,10,
                to_date('01011996','MMDDYYYY'),
                to_date('01311996','MMDDYYYY')))
    FROM ORDTDEV.stocks_ts
    WHERE ticker ='AONE';

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

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

       -- Example of validating a valid time series.
       SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal
       FROM dual;
       retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS);
       retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab,
                                 hiDateTab, impDateTab, dupDateTab,
                                 extraDateTab, missingDateTab);
       DBMS_OUTPUT.PUT_LINE('Value returned by IsValid  = ' || retIsValid);
       DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS  = ' || retValTS);
       ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab,
                              impDateTab, dupDateTab, extraDateTab, missingDateTab,
                              'Testing DisplayValTS');
       DBMS_OUTPUT.NEW_LINE;

       -- For illustration let us first create an invalid timeseries.
       --
       -- Here we are adjusting the calendar's minDate and maxDate to avoid
       -- getting a huge list of missing dates.
       --
       numTS.cal.minDate := TO_DATE('10/28/1996');
       numTS.cal.maxDate := TO_DATE('01/05/1997');

       -- Add Dates Before numTS.cal.minDate
       numTS.series(10).tstamp := numTS.cal.minDate - 1;
       numTS.series(11).tstamp := numTS.cal.minDate - 2;

       -- Add Dates Beyond numTS.cal.maxDate
       numTS.series(12).tstamp := numTS.cal.maxDate + 1;
       numTS.series(13).tstamp := numTS.cal.maxDate + 2;

       -- Add some null timestamps
       numTS.series(14).tstamp := NULL;
       numTS.series(15).tstamp := NULL;

       -- Add some imprecise dates (some are duplicated)
       numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24;
       numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24;

       -- Add some duplicate timestamps
       numTS.series(19).tstamp := numTS.series(18).tstamp;
       numTS.series(21).tstamp := numTS.series(20).tstamp;

       -- Add some extra dates in the middle
       numTS.series(37).tstamp := TO_DATE('12/28/1996');
       numTS.series(36).tstamp := TO_DATE('12/29/1996');

       -- Add some holes at the end
       numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997');

       -- Example of validating an invalid time series.
       SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES')
       INTO tempVal FROM dual;
       retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS);
       retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg,
                             loDateTab, hiDateTab, impDateTab,
                             dupDateTab, extraDateTab, missingDateTab);
       DBMS_OUTPUT.PUT_LINE('Value returned by IsValid  = ' || retIsValid);
       DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS  = ' || retValTS);
       ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab,
                          impDateTab, dupDateTab, extraDateTab, missingDateTab,
                          'Testing DisplayValTS');
    END;
    /

This example might produce the following output:

    A VALID TIME SERIES :

    Name = ACME open NumSeries
    Calendar Data:
    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     11/14/1996 00:00:00      68
     11/15/1996 00:00:00      69
     11/18/1996 00:00:00      70
     11/19/1996 00:00:00      71
     11/20/1996 00:00:00      72
     11/21/1996 00:00:00      73
     11/22/1996 00:00:00      74
     11/25/1996 00:00:00      75
     11/26/1996 00:00:00      76
     11/27/1996 00:00:00      77
     11/29/1996 00:00:00      78
     12/02/1996 00:00:00      79
     12/03/1996 00:00:00      80
     12/04/1996 00:00:00      81
     12/05/1996 00:00:00      82
     12/06/1996 00:00:00      83
     12/09/1996 00:00:00      84
     12/10/1996 00:00:00      85
     12/11/1996 00:00:00      86
     12/12/1996 00:00:00      87
     12/13/1996 00:00:00      88
     12/16/1996 00:00:00      89
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/23/1996 00:00:00      94
     12/24/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     12/31/1996 00:00:00      99
     -----------------------------

    Value returned by IsValid  = 1
    Value returned by ValidateTS  = 1

    DisplayValTS: Testing DisplayValTS:

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

    AN INVALID TIME SERIES :

    Name = ACME open NumSeries
    Calendar Data:
    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 10/28/1996 00:00:00
     MaxDate = 01/05/1997 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     10/27/1996 00:00:00      68
     10/26/1996 00:00:00      69
     01/06/1997 00:00:00      70
     01/07/1997 00:00:00      71
           72
           73
     11/22/1996 00:00:00      74
     11/22/1996 01:00:00      75
     11/22/1996 15:00:00      76
     11/22/1996 15:00:00      77
     11/29/1996 00:00:00      78
     11/29/1996 00:00:00      79
     12/03/1996 00:00:00      80
     12/04/1996 00:00:00      81
     12/05/1996 00:00:00      82
     12/06/1996 00:00:00      83
     12/09/1996 00:00:00      84
     12/10/1996 00:00:00      85
     12/11/1996 00:00:00      86
     12/12/1996 00:00:00      87
     12/13/1996 00:00:00      88
     12/16/1996 00:00:00      89
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/29/1996 00:00:00      94
     12/28/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     01/04/1997 00:00:00      99
     -----------------------------

    Value returned by IsValid  = 0
    Value returned by ValidateTS  = 0

    DisplayValTS: Testing DisplayValTS:

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

    END;
    /

This example might produce the following output:

    ExtractCal Results :

    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00

## 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 ORDTDEV.stocks_ts
           WHERE ticker = 'AONE';

This example might produce the following output:

    TO_CHAR(ORDSYS.TIME
    -------------------
    01/15/1990 00:00:00
    1 row selected.

## 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 ORDTDEV.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.ORDTVarchar2Celf

  ) 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 stocks_ts table:

    SELECT ORDSYS.TimeSeries.ExtractValue(
             ORDSYS.TimeSeries.GetNthElement(open, 10))
      FROM ORDTDEV.stocks_ts
      WHERE ticker = 'AONE';

This example might produce the following output:

    ORDSYS.TIM
    ----------
       15.1875
    1 row selected.

## Fill

### Format

ORDSYS.TimeSeries.Fill(

  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

##### ts

The input time series.

##### fill_type

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

- 0 = null: Insert nulls.
- 1 = forward repeat: Use the values from the preceding (most recent) timestamp.
- 2 = backward repeat: Use the values from the following (next in the future) timestamp.

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:

- 49 on 29-Jan-1997
- 50 on 31-Jan-1997

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:

- deriving the price of a stock for a nontrading day

  For example, you may want to compare prices for a stock that trades on several stock exchanges, where the exchanges have different trading days.

- converting a quarterly time series to a daily time series

  For example, earnings per share (EPS) is computed quarterly, and stocks trade daily. To compute a price-earnings (PE) ratio, earnings per share is first converted to a daily time series using forward repeat. Then, the daily PE ratio is calculated by dividing the daily price time series value by the corresponding daily EPS time series value.

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

### 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 timeseries with missing dates.
    -- In the following example, the timeseries '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 timeseries by from XCORP's high (repeat forward).
     SELECT ORDSYS.TimeSeries.Display(
               ORDSYS.TimeSeries.Fill(ts, 1),
               'Fill Forward') INTO dummyval
     FROM dual;

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

     -- Generate a timeseries by from XCORP's high (null fill).
     SELECT ORDSYS.TimeSeries.Display(
               ORDSYS.TimeSeries.Fill(ts, 0),
               'Null Fill') INTO dummyval
     FROM dual;

    END;
    /

This example might produce the following output:

    Fill Forward :

    Calendar Data:
    Calendar Name = FOO CALENDAR
     Frequency = 4
     MinDate = 01/01/1996 00:00:00
     MaxDate = 01/01/1997 00:00:00
     patBits:
              1111111
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
    Series Data:
     -----------------------------
     Date                    Value
     12/02/1996 00:00:00      1
     12/03/1996 00:00:00      2
     12/04/1996 00:00:00      3
     12/05/1996 00:00:00      4
     12/06/1996 00:00:00      5
     12/07/1996 00:00:00      5
     12/08/1996 00:00:00      5
     12/09/1996 00:00:00      6
     12/10/1996 00:00:00      7
     12/11/1996 00:00:00      8
     12/12/1996 00:00:00      9
     12/13/1996 00:00:00      10
     -----------------------------

    Fill Backward :

    Calendar Data:
    Calendar Name = FOO CALENDAR
     Frequency = 4
     MinDate = 01/01/1996 00:00:00
     MaxDate = 01/01/1997 00:00:00
     patBits:
              1111111
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
    Series Data:
     -----------------------------
     Date                    Value
     12/02/1996 00:00:00      1
     12/03/1996 00:00:00      2
     12/04/1996 00:00:00      3
     12/05/1996 00:00:00      4
     12/06/1996 00:00:00      5
     12/07/1996 00:00:00      6
     12/08/1996 00:00:00      6
     12/09/1996 00:00:00      6
     12/10/1996 00:00:00      7
     12/11/1996 00:00:00      8
     12/12/1996 00:00:00      9
     12/13/1996 00:00:00      10
     -----------------------------

    Null Fill :

    Calendar Data:
    Calendar Name = FOO CALENDAR
     Frequency = 4
     MinDate = 01/01/1996 00:00:00
     MaxDate = 01/01/1997 00:00:00
     patBits:
              1111111
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
    Series Data:
     -----------------------------
     Date                    Value
     12/02/1996 00:00:00      1
     12/03/1996 00:00:00      2
     12/04/1996 00:00:00      3
     12/05/1996 00:00:00      4
     12/06/1996 00:00:00      5
     12/07/1996 00:00:00
     12/08/1996 00:00:00
     12/09/1996 00:00:00      6
     12/10/1996 00:00:00      7
     12/11/1996 00:00:00      8
     12/12/1996 00:00:00      9
     12/13/1996 00:00:00      10
     -----------------------------

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

    END;
    /

This example might produce the following output:

    First Results :

       Timestamp : 11/01/1996 00:00:00
           Value : 59

## FirstN

### Format

ORDSYS.TimeSeries.FirstN(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  NumValues NUMBER

  ) 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

##### ts

The input time series.

##### NumValues

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

### 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 or if NumValues is zero (0) or negative.

### 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 ORDTDEV.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
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     11/14/1996 00:00:00      68
     -----------------------------

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

    END;
    /

This example might produce the following output:

    GetDatedElement Results :

       Timestamp : 11/26/1996 00:00:00
           Value : 76

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- target_index is not an integer, or is zero (0) or a negative number.
- endDate is earlier than startDate.

### Example

Return the tenth opening price for stock AONE:

    SELECT ORDSYS.TimeSeries.ExtractValue(
             ORDSYS.TimeSeries.GetNthElement(open, 10))
      FROM ORDTDEV.stocks_ts
      WHERE ticker = 'AONE';

This example might produce the following output:

    ORDSYS.TIM
    ----------
       15.1875
    1 row selected.

## GetSeries

### Format

ORDSYS.TimeSeries.GetSeries(

  ts ORDSYS.ORDTNumSeriesIOTRef

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.GetSeries(

  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

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

    END;
    /

This example might produce the following output:

    GetSeries Results :

    Name = ACME open NumSeries
    Calendar Data:
    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     11/14/1996 00:00:00      68
     11/15/1996 00:00:00      69
     11/18/1996 00:00:00      70
     11/19/1996 00:00:00      71
     11/20/1996 00:00:00      72
     11/21/1996 00:00:00      73
     11/22/1996 00:00:00      74
     11/25/1996 00:00:00      75
     11/26/1996 00:00:00      76
     11/27/1996 00:00:00      77
     11/29/1996 00:00:00      78
     12/02/1996 00:00:00      79
     12/03/1996 00:00:00      80
     12/04/1996 00:00:00      81
     12/05/1996 00:00:00      82
     12/06/1996 00:00:00      83
     12/09/1996 00:00:00      84
     12/10/1996 00:00:00      85
     12/11/1996 00:00:00      86
     12/12/1996 00:00:00      87
     12/13/1996 00:00:00      88
     12/16/1996 00:00:00      89
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/23/1996 00:00:00      94
     12/24/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     12/31/1996 00:00:00      99
     -----------------------------

## IsValidTS

### Format

ORDSYS.TimeSeries.IsValidTS(

  ts IN ORDSYS.ORDTNumSeriesIOTRef

  ) RETURN INTEGER

or

ORDSYS.TimeSeries.IsValidTS(

  ts IN 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:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- The calendar associated with the time series is invalid.
- The timestamps are not sorted.
- One or more timestamps are null, imprecise, or outside the date range of the calendar.
- One or more timestamps are included in the time series but 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).
- One or more timestamps are excluded from the time series but 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.

Contrast this function with ValidateTS, which checks whether a time series is valid, and if the time series is not valid, outputs a diagnostic message and tables with timestamps that are causing the time series to be invalid.

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

       -- Example of validating a valid time series.
       SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal
       FROM dual;
       retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS);
       retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab,
                                 hiDateTab, impDateTab, dupDateTab,
                                 extraDateTab, missingDateTab);
       DBMS_OUTPUT.PUT_LINE('Value returned by IsValid  = ' || retIsValid);
       DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS  = ' || retValTS);
       ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab,
                              impDateTab, dupDateTab, extraDateTab, missingDateTab,
                              'Testing DisplayValTS');
       DBMS_OUTPUT.NEW_LINE;

       -- For illustration let us first create an invalid timeseries.
       --
       -- Here we are adjusting the calendar's minDate and maxDate to avoid
       -- getting a huge list of missing dates.
       --
       numTS.cal.minDate := TO_DATE('10/28/1996');
       numTS.cal.maxDate := TO_DATE('01/05/1997');

       -- Add Dates Before numTS.cal.minDate
       numTS.series(10).tstamp := numTS.cal.minDate - 1;
       numTS.series(11).tstamp := numTS.cal.minDate - 2;

       -- Add Dates Beyond numTS.cal.maxDate
       numTS.series(12).tstamp := numTS.cal.maxDate + 1;
       numTS.series(13).tstamp := numTS.cal.maxDate + 2;

       -- Add some null timestamps
       numTS.series(14).tstamp := NULL;
       numTS.series(15).tstamp := NULL;

       -- Add some imprecise dates (some are duplicated)
       numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24;
       numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24;

       -- Add some duplicate timestamps
       numTS.series(19).tstamp := numTS.series(18).tstamp;
       numTS.series(21).tstamp := numTS.series(20).tstamp;

       -- Add some extra dates in the middle
       numTS.series(37).tstamp := TO_DATE('12/28/1996');
       numTS.series(36).tstamp := TO_DATE('12/29/1996');

       -- Add some holes at the end
       numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997');

       -- Example of validating an invalid time series.
       SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES')
       INTO tempVal FROM dual;
       retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS);
       retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg,
                             loDateTab, hiDateTab, impDateTab,
                             dupDateTab, extraDateTab, missingDateTab);
       DBMS_OUTPUT.PUT_LINE('Value returned by IsValid  = ' || retIsValid);
       DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS  = ' || retValTS);
       ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab,
                          impDateTab, dupDateTab, extraDateTab, missingDateTab,
                          'Testing DisplayValTS');
    END;
    /

This example might produce the following output:

    A VALID TIME SERIES :

    Name = ACME open NumSeries
    Calendar Data:
    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     11/14/1996 00:00:00      68
     11/15/1996 00:00:00      69
     11/18/1996 00:00:00      70
     11/19/1996 00:00:00      71
     11/20/1996 00:00:00      72
     11/21/1996 00:00:00      73
     11/22/1996 00:00:00      74
     11/25/1996 00:00:00      75
     11/26/1996 00:00:00      76
     11/27/1996 00:00:00      77
     11/29/1996 00:00:00      78
     12/02/1996 00:00:00      79
     12/03/1996 00:00:00      80
     12/04/1996 00:00:00      81
     12/05/1996 00:00:00      82
     12/06/1996 00:00:00      83
     12/09/1996 00:00:00      84
     12/10/1996 00:00:00      85
     12/11/1996 00:00:00      86
     12/12/1996 00:00:00      87
     12/13/1996 00:00:00      88
     12/16/1996 00:00:00      89
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/23/1996 00:00:00      94
     12/24/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     12/31/1996 00:00:00      99
     -----------------------------

    Value returned by IsValid  = 1
    Value returned by ValidateTS  = 1

    DisplayValTS: Testing DisplayValTS:

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

    AN INVALID TIME SERIES :

    Name = ACME open NumSeries
    Calendar Data:
    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 10/28/1996 00:00:00
     MaxDate = 01/05/1997 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     10/27/1996 00:00:00      68
     10/26/1996 00:00:00      69
     01/06/1997 00:00:00      70
     01/07/1997 00:00:00      71
           72
           73
     11/22/1996 00:00:00      74
     11/22/1996 01:00:00      75
     11/22/1996 15:00:00      76
     11/22/1996 15:00:00      77
     11/29/1996 00:00:00      78
     11/29/1996 00:00:00      79
     12/03/1996 00:00:00      80
     12/04/1996 00:00:00      81
     12/05/1996 00:00:00      82
     12/06/1996 00:00:00      83
     12/09/1996 00:00:00      84
     12/10/1996 00:00:00      85
     12/11/1996 00:00:00      86
     12/12/1996 00:00:00      87
     12/13/1996 00:00:00      88
     12/16/1996 00:00:00      89
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/29/1996 00:00:00      94
     12/28/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     01/04/1997 00:00:00      99
     -----------------------------

    Value returned by IsValid  = 0
    Value returned by ValidateTS  = 0

    DisplayValTS: Testing DisplayValTS:

    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 (

  ts ORDSYS.ORDTNumSeriesIOTRef,

  units INTEGER

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.Lag (

  ts ORDSYS.ORDTNumSeriesIOTRef,

  lead_date DATE

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### 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

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

### 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 * FROM the
      (SELECT 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)
           FROM ORDTDEV.stockdemo_ts ts
           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 ORDTDEV.stockdemo_ts ts
     WHERE ts.ticker='ACME';

    END;
    /

This example might produce the following output:

    Last Results :

       Timestamp : 12/31/1996 00:00:00
           Value : 99

## LastN

### Format

ORDSYS.TimeSeries.LastN(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  NumValues NUMBER

  ) 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

##### ts

The input time series.

##### NumValues

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

### 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 or if NumValues is zero (0) or negative.

### 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 ORDTDEV.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
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/23/1996 00:00:00      94
     12/24/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     12/31/1996 00:00:00      99
     -----------------------------

## Lead

### Format

ORDSYS.TimeSeries.Lead (

  ts ORDSYS.ORDTNumSeriesIOTRef,

  units INTEGER

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.Lead (

  ts ORDSYS.ORDTNumSeriesIOTRef,

  lead_date DATE

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### 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

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

### 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 * FROM the
      (SELECT 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)
           FROM ORDTDEV.stockdemo_ts ts
           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(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  [startDate DATE, endDate DATE,]

  k INTEGER

  ) 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

##### ts

The input time series.

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

##### k

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

### 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.8.2.1.

An exception is returned if any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

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

    SELECT * FROM the
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                 ORDSYS.TimeSeries.Mavg(ts.close,to_date('02-DEC-96','DD-MON-YY'),
                       to_date('31-DEC-96','DD-MON-YY'),10)
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stockdemo_ts ts
           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.
    SVRMGR>

## Msum

### Format

ORDSYS.TimeSeries.Msum(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  [startDate DATE, endDate DATE,]

  k INTEGER

  ) 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

##### ts

The input time series.

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

##### k

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

### 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.8.2.1.

An exception is returned if any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

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

    SELECT * FROM THE(
      SELECT CAST(ORDTS.Extract(ORDTS.MSUM(volume,
         to_date(`01-01-96','MM-DD-YY'),
         to_date(`12-31-96','MM-DD-YY'),
         30)) AS ORDTNumTab)
      FROM StockTabView
      WHERE ticker = `AONE');

## Scaleup

### Format

ORDSYS.TimeSeries.Scaleup(

  inDate DATE,

  calendar ORDSYS.ORDTCalendar

  ) RETURN DATE;

### Description

Given an input ORDTCalendar and a date, returns a scaled date.

### Parameters

##### inDate

The date to be used for scaling.

##### calendar

The calendar to be used for scaling the date.

### Usage

For an explanation of concepts related to time scaling, see Section 2.9.

This function is used in a SQL GROUP BY clause for scaling of dates.

An exception is returned if inDate or calendar is null.

### Example

For all tickers accessible through the stockdemo_sv view (ACME, FUNCO, SAMCO, and XCORP), scale daily data to monthly summary data for the summed volume and average closing price.

    --
    -- Scaleup - Group By interface
    -- For all tickers in stockdemo, scale daily data to monthly
    -- summary data, reporting summed volumes and average closes.
    --
    SELECT ticker, ORDSYS.TimeSeries.Scaleup(sv.tstamp,
    value(cal)),sum(volume),avg(close)
      FROM ORDTDEV.stockdemo_sv sv, ordtdev.stockdemo_calendars cal
      WHERE cal.name = 'MONTHLY'
      GROUP BY ticker,ORDSYS.TimeSeries.Scaleup(sv.tstamp, value(cal));

This example might produce the following output:

    TICKE ORDSYS.OR SUM(VOLUME AVG(CLOSE)
    ----- --------- ---------- ----------
    ACME  01-NOV-96      20000       68.5
    ACME  01-DEC-96      21000         89
    FUNCO 01-NOV-96      20000     23.823
    FUNCO 01-DEC-96      21000 23.8257143
    SAMCO 01-NOV-96   10207000   39.83125
    SAMCO 01-DEC-96    3719450 38.2738095
    XCORP 01-OCT-96   10270250 79.1458333
    XCORP 01-NOV-96  100243350 84.6973684
    XCORP 01-DEC-96  141838350 91.9572368
    9 rows selected.

## ScaleupAvg

### Format

ORDSYS.TimeSeries.ScaleupAvg(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  calendar ORDSYS.ORDTCalendar

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the average value of each scaled group of non-null values.

### Parameters

##### ts

The input time series.

##### calendar

The calendar to be used for the scaling.

##### startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

##### endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

### Usage

An exception is returned for any of the following conditions:

- The input time series, the calendar on which the input time series in based, or the specified calendar is null.
- The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

Nulls are ignored in computing the average for each group of values.

For an explanation of concepts related to time scaling, see Section 2.9.

### Example

Return the average closing prices for stock SAMCO for each month for the entire time series:

    SELECT * FROM THE
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                     ORDSYS.TimeSeries.ScaleupAvg(
                                    ts.close,
                                    sc.calendar
                                                )
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc
           WHERE ts.ticker='SAMCO' and
                 sc.name  ='MONTHLY');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    01-JAN-90 29.7074045
    01-FEB-90 29.0477211
    01-MAR-90 30.7003091
      .
      .
      .
    01-OCT-96 42.7717391
    01-NOV-96   39.83125
    01-DEC-96 38.2738095
    84 rows selected.

## ScaleupCount

### Format

ORDSYS.TimeSeries.ScaleupCount(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  calendar ORDSYS.ORDTCalendar

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the count of non-null timestamps in each scaled group.

### Parameters

##### ts

The input time series.

##### calendar

The calendar to be used for the scaling.

##### startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

##### endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

### Usage

An exception is returned for any of the following conditions:

- The input time series, the calendar on which the input time series in based, or the specified calendar is null.
- The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

Nulls are ignored in computing the count for each group of values.

For an explanation of concepts related to time scaling, see Section 2.9.

### Example

Return the quarterly count of daily closing prices for stock SAMCO for the period 01-June-1996 through 31-December 1996:

    SELECT * FROM THE
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                ORDSYS.TimeSeries.ScaleupCount(
                       ts.close,
                       sc.calendar,
                       to_date('01-JUL-1996','DD-MON-YYYY'),
                       to_date('31-DEC-1996','DD-MON-YYYY')
                       )
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc
           WHERE ts.ticker='SAMCO' and
                 sc.name  ='QUARTERLY');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    01-JUL-96         64
    01-OCT-96         64
    2 rows selected.

## ScaleupFirst

### Format

ORDSYS.TimeSeries.ScaleupFirst(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  calendar ORDSYS.ORDTCalendar

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the first non-null value of each scaled group of values.

### Parameters

##### ts

The input time series.

##### calendar

The calendar to be used for the scaling.

##### startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

##### endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

### Usage

An exception is returned for any of the following conditions:

- The input time series, the calendar on which the input time series in based, or the specified calendar is null.
- The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

For an explanation of concepts related to time scaling, see Section 2.9.

### Example

Return the first closing prices for stock SAMCO for the months of October, November, and December of 1996:

    SELECT * FROM THE
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                     ORDSYS.TimeSeries.ScaleupFirst(
                            ts.close,
                            sc.calendar,
                            to_date('01-OCT-1996','DD-MON-YYYY'),
                            to_date('01-JAN-1997','DD-MON-YYYY')
                            )
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc
           WHERE ts.ticker='SAMCO' and
                 sc.name  ='MONTHLY');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    01-OCT-96      42.75
    01-NOV-96     41.875
    01-DEC-96     38.125
    3 rows selected.

## ScaleupLast

### Format

ORDSYS.TimeSeries.ScaleupLast(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  calendar ORDSYS.ORDTCalendar

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the last non-null value of each scaled group of values.

### Parameters

##### ts

The input time series.

##### calendar

The calendar to be used for the scaling.

##### startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

##### endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

### Usage

An exception is returned for any of the following conditions:

- The input time series, the calendar on which the input time series in based, or the specified calendar is null.
- The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

For an explanation of concepts related to time scaling, see Section 2.9.

### Example

Return the last closing prices for stock SAMCO for the months of October, November, and December of 1996:

    SELECT * FROM THE
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                     ORDSYS.TimeSeries.ScaleupLast(
                            ts.close,
                            sc.calendar,
                            to_date('01-OCT-1996','DD-MON-YYYY'),
                            to_date('01-JAN-1997','DD-MON-YYYY')
                            )
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc
           WHERE ts.ticker='SAMCO' and
                 sc.name  ='MONTHLY');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    01-OCT-96     42.375
    01-NOV-96      38.25
    01-DEC-96      39.75
    3 rows selected.

Note that each timestamp reflects the first date of the month in the calendar (following the rules explained in Section 2.2.1), and each value in this case reflects the closing price on the last date for that month in the calendar.

## ScaleupMax

### Format

ORDSYS.TimeSeries.ScaleupMax(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  calendar ORDSYS.ORDTCalendar

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the maximum value of each scaled group of values.

### Parameters

##### ts

The input time series.

##### calendar

The calendar to be used for the scaling.

##### startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

##### endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

### Usage

An exception is returned for any of the following conditions:

- The input time series, the calendar on which the input time series in based, or the specified calendar is null.
- The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

For an explanation of concepts related to time scaling, see Section 2.9.

### Example

Return the highest (maximum) closing prices for stock SAMCO for each month in the entire time series:

    SELECT * FROM THE
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                     ORDSYS.TimeSeries.ScaleupMax(
                                    ts.close,
                                    sc.calendar
                                                )
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc
           WHERE ts.ticker='SAMCO' and
                 sc.name  ='MONTHLY');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    01-JAN-90    31.2813
    01-FEB-90    29.7813
    01-MAR-90    31.1875
    01-APR-90    31.5938
    01-MAY-90     32.875
    01-JUN-90    33.7813
    01-JUL-90    34.6875
    01-AUG-90     31.875
      ...         ...
    01-OCT-96     43.375
    01-NOV-96      43.75
    01-DEC-96      39.75
    84 rows selected.

## ScaleupMin

### Format

ORDSYS.TimeSeries.ScaleupMin(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  calendar ORDSYS.ORDTCalendar

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the minimum value of each scaled group of values.

### Parameters

##### ts

The input time series.

##### calendar

The calendar to be used for the scaling.

##### startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

##### endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

### Usage

An exception is returned for any of the following conditions:

- The input time series, the calendar on which the input time series in based, or the specified calendar is null.
- The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

For an explanation of concepts related to time scaling, see Section 2.9.

### Example

Return the lowest (minimum) closing prices for stock SAMCO for each month in the entire time series:

    SELECT * FROM THE
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                     ORDSYS.TimeSeries.ScaleupMin(
                                    ts.close,
                                    sc.calendar
                                                )
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc
           WHERE ts.ticker='SAMCO' and
                 sc.name  ='MONTHLY');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    01-JAN-90    27.6875
    01-FEB-90    28.2813
    01-MAR-90    30.0938
    01-APR-90    30.1875
    01-MAY-90    30.7813
    01-JUN-90    32.0938
    01-JUL-90    32.2813
    01-AUG-90    28.5938
      ...         ...
    01-OCT-96         42
    01-NOV-96     37.375
    01-DEC-96     37.875
    84 rows selected.

## ScaleupSum

### Format

ORDSYS.TimeSeries.ScaleupSum(

  ts ORDSYS.ORDTNumSeriesIOTRef,

  calendar ORDSYS.ORDTCalendar

  [, startDate DATE

  , endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### Description

Given a time series, a calendar to be used for scaling, and optionally starting and ending dates, returns a time series reflecting the sum of each scaled group of values.

### Parameters

##### ts

The input time series.

##### calendar

The calendar to be used for the scaling.

##### startDate

The starting date to be used. If startDate is specified, endDate must also be specified.

##### endDate

The ending date to be used. If endDate is specified, startDate must also be specified.

### Usage

An exception is returned for any of the following conditions:

- The input time series, the calendar on which the input time series in based, or the specified calendar is null.
- The frequency of the calendar on which the time series is based is greater than the frequency of the specified calendar (for example, the time series calendar's frequency is month and the specified calendar's frequency is day).

For an explanation of concepts related to time scaling, see Section 2.9.

### Example

Return the sum of the daily trade volume for stock SAMCO for each month in the entire time series:

    SELECT * FROM THE
      (SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
                     ORDSYS.TimeSeries.ScaleupSum(
                                    ts.volume,
                                    sc.calendar
                                                )
                  ) AS ORDSYS.ORDTNumTab)
           FROM ORDTDEV.stocks_ts ts, ordtdev.scale sc
           WHERE ts.ticker='SAMCO' and
                 sc.name  ='MONTHLY');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    01-JAN-90    3117750
    01-FEB-90    2036500
    01-MAR-90    1424375
    01-APR-90     981500
    01-MAY-90    1348875
    01-JUN-90    1395875
    01-JUL-90    1088125
    01-AUG-90    1503000
      ...          ...
    01-OCT-96    1615350
    01-NOV-96   10207000
    01-DEC-96    3719450
    84 rows selected.

## TrimSeries

### Format

ORDSYS.TimeSeries.TrimSeries(ts ORDSYS.ORDTNumSeriesIOTRef

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TrimSeries(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

##### ts

The input time series.

##### startDate

Starting date within the time series. If startDate is specified, endDate must also be specified.

##### endDate

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

### Usage

An exception is returned if any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

Return the opening prices for stock AONE 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 ORDTDEV.stocks_ts
        WHERE ticker = 'AONE';
    END;
    /

This statement might produce the following output:

    Calendar Data:
    Calendar Name = AONE
     Frequency = 4
     MinDate = 01-JAN-80
     MaxDate = 01-JAN-01
     patBits:
              0111110
     patAnchor = 06-APR-97
     onExceptions  :
     offExceptions :
         19-FEB-90     13-APR-90     28-MAY-90
         04-JUL-90     03-SEP-90     22-NOV-90
         25-DEC-90     01-JAN-91     18-FEB-91
         29-MAR-91     27-MAY-91     04-JUL-91
         02-SEP-91     28-NOV-91     25-DEC-91
         01-JAN-92     17-FEB-92     17-APR-92
         25-MAY-92     03-JUL-92     07-SEP-92
         26-NOV-92     25-DEC-92     01-JAN-93
         15-FEB-93     09-APR-93     31-MAY-93
         05-JUL-93     06-SEP-93     25-NOV-93
         24-DEC-93     21-FEB-94     01-APR-94
         27-APR-94     30-MAY-94     04-JUL-94
         05-SEP-94     24-NOV-94     26-DEC-94
         02-JAN-95     20-FEB-95     14-APR-95
         29-MAY-95     04-JUL-95     04-SEP-95
         23-NOV-95     25-DEC-95     01-JAN-96
         19-FEB-96     05-APR-96     27-MAY-96
         04-JUL-96     02-SEP-96     17-OCT-96
         28-NOV-96     25-DEC-96     27-DEC-96
    Series Data:
     -----------------------------
     Date                    Value
     02-DEC-96      59.875
     03-DEC-96      60.875
     04-DEC-96      60.625
     05-DEC-96      57.75
     06-DEC-96      56.5
     09-DEC-96      57
     10-DEC-96      60.875
     11-DEC-96      59.625
     12-DEC-96      59.75
     13-DEC-96      54.875
     16-DEC-96      55.625
     17-DEC-96      53.25
     18-DEC-96      54.375
     19-DEC-96      53.875
     20-DEC-96      53.375
     23-DEC-96      54.375
     24-DEC-96      53.5
     26-DEC-96      54.375
     30-DEC-96      54.125
     31-DEC-96      52.875
     -----------------------------

## TSAdd

### Format

ORDSYS.TimeSeries.TSAdd (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  ts2 ORDSYS.ORDTNumSeriesIOTRef

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSAdd (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  k NUMBER

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### 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

##### 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 two time series contain daily trade volumes for two stocks, each element of the returned time series contains the sum of the trade volumes for the two stocks for that day.
- If a time series (ts1) contains closing prices for a stock and if a constant (k) of 1 is specified, each element of the returned time series contains the closing price of ts1 incremented by 1.

If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.

An exception is returned if any of the following conditions is true:

- An input time series is null.
- An input time series does not have an associated calendar.
- The calendars associated with ts1 and ts2 do not have the same frequency and aligned pattern.
- endDate is earlier than startDate.

### 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 ORDTUSER/ORDTUSER
    SET SERVEROUTPUT ON
    ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

    DECLARE
    tstCal ORDSYS.ORDTCalendar;
    startDate date;
    endDate   date;
    dummyval INTEGER;

    BEGIN

     startDate := TO_DATE('11/14/1996');
     endDate   := TO_DATE('12/14/1996');
     SELECT ORDSYS.TimeSeries.Display(
               ORDSYS.TimeSeries.TSAdd(ts1.high, ts2.low, startDate, endDate),
               'TSAdd Results') INTO dummyval
     FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2
     WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO';

    END;
    /

This example might produce the following output:

    TSAdd Results :

    Calendar Data:
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/14/1996 00:00:00      92.87
     11/15/1996 00:00:00      93.84
     11/18/1996 00:00:00      94.87
     11/19/1996 00:00:00      95.85
     11/20/1996 00:00:00      96.82
     11/21/1996 00:00:00      97.84
     11/22/1996 00:00:00      98.85
     11/25/1996 00:00:00      99.81
     11/26/1996 00:00:00      100.78
     11/27/1996 00:00:00      101.71
     11/29/1996 00:00:00      102.75
     12/02/1996 00:00:00      103.88
     12/03/1996 00:00:00      105.03
     12/04/1996 00:00:00      106.02
     12/05/1996 00:00:00      107.13
     12/06/1996 00:00:00      107.75
     12/09/1996 00:00:00      108.77
     12/10/1996 00:00:00      109.8
     12/11/1996 00:00:00      110.5
     12/12/1996 00:00:00      111.41
     12/13/1996 00:00:00      112.4
     -----------------------------

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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

This example might produce the following output:

    ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD
    ---------- ---------- ----------
            79      143.5 11.9791486

1 row selected.

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

Return the total number of daily closing prices for stock AONE for the month of January 1990:

    SELECT ORDSYS.TimeSeries.TSCount(close,
                            to_date('01/01/1990 00:00:00',
                                    'MM/DD/YYYY HH24:MI:SS'),
                            to_date('01/31/1990 23:59:59',
                                    'MM/DD/YYYY HH24:MI:SS')) TSCount
      FROM ORDTDEV.Stocks_TS
      WHERE ticker='AONE';

This example might produce the following output:

    TSCOUNT
    ----------
            22
    1 row selected.

## TSDivide

### Format

ORDSYS.TimeSeries.TSDivide (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  ts2 ORDSYS.ORDTNumSeriesIOTRef

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSDivide (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  k NUMBER

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### 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

##### 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 two time series contain daily trade volumes for two stocks, each element of the returned time series contains the result of dividing the volume in the first time series by the volume in the second time series for that day.
- If a time series (ts1) contains closing prices for a stock and if a constant (k) of 2 is specified, each element of the returned time series contains the closing price of ts1 divided by 2.

If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.

An exception is returned if any of the following conditions is true:

- An input time series is null.
- An input time series does not have an associated calendar.
- The calendars associated with ts1 and ts2 do not have the same frequency and aligned pattern.
- endDate is earlier than startDate.

### 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 ORDTUSER/ORDTUSER
    SET SERVEROUTPUT ON
    ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

    DECLARE
    tstCal ORDSYS.ORDTCalendar;
    startDate date;
    endDate   date;
    dummyval INTEGER;

    BEGIN

     startDate := TO_DATE('11/14/1996');
     endDate   := TO_DATE('12/14/1996');
     SELECT ORDSYS.TimeSeries.Display(
          ORDSYS.TimeSeries.TSDivide(ts1.high, ts2.low, startDate, endDate),
         'TSDivide Results') INTO dummyval
     FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2
     WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO';

    END;
    /

This example might produce the following output:

    TSDivide Results :

    Calendar Data:
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/14/1996 00:00:00      2.89065772936740678676162547130289065773
     11/15/1996 00:00:00      2.93624161073825503355704697986577181208
     11/18/1996 00:00:00      2.97444490992878089652283200670297444491
     11/19/1996 00:00:00      3.01886792452830188679245283018867924528
     11/20/1996 00:00:00      3.0646515533165407220822837951301427372
     11/21/1996 00:00:00      3.10402684563758389261744966442953020134
     11/22/1996 00:00:00      3.1446540880503144654088050314465408805
     11/25/1996 00:00:00      3.19193616127677446451070978580428391432
     11/26/1996 00:00:00      3.23801513877207737594617325483599663583
     11/27/1996 00:00:00      3.28975115984816533108393083087304934627
     11/29/1996 00:00:00      3.32631578947368421052631578947368421053
     12/02/1996 00:00:00      3.35008375209380234505862646566164154104
     12/03/1996 00:00:00      3.37078651685393258426966292134831460674
     12/04/1996 00:00:00      3.41382181515403830141548709408825978351
     12/05/1996 00:00:00      3.43970161624533775383340240364691255698
     12/06/1996 00:00:00      3.53684210526315789473684210526315789474
     12/09/1996 00:00:00      3.57593605384938998737904922170803533866
     12/10/1996 00:00:00      3.61344537815126050420168067226890756303
     12/11/1996 00:00:00      3.70212765957446808510638297872340425532
     12/12/1996 00:00:00      3.75907731738573259290901324220418624519
     12/13/1996 00:00:00      3.8034188034188034188034188034188034188
     -----------------------------

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

Return the highest closing price for stock AONE for the month of January 1990:

    SELECT ORDSYS.TimeSeries.TSMax(close,
                            to_date('01/01/1990 00:00:00',
                                    'MM/DD/YYYY HH24:MI:SS'),
                            to_date('01/31/1990 23:59:59',
                                    'MM/DD/YYYY HH24:MI:SS')) TSMax
      FROM ORDTDEV.Stocks_TS
      WHERE ticker='AONE';

This example might produce the following output:

    TSMAX
    ----------
       16.3914
    1 row selected.

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.
- NumValues is zero (0) or negative.

### Example

Return the 10 highest closing prices for stock AONE for the month of January 1996:

    SELECT * FROM THE( SELECT CAST(
                      ORDSYS.TimeSeries.TSMaxN(close, 10,
                             to_date('01011996','MMDDYYYY'),
                             to_date('01311996','MMDDYYYY'))
                      as ORDSYS.ORDTNumTab)
                FROM ORDTDEV.stocks_ts
                WHERE ticker ='AONE');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    24-JAN-96    43.9138
    25-JAN-96    42.9925
    31-JAN-96    42.9925
    26-JAN-96    42.7413
    30-JAN-96    42.7413
    29-JAN-96    42.5738
    23-JAN-96    41.9875
    22-JAN-96      41.82
    19-JAN-96     41.485
    18-JAN-96     40.815
    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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

Return the median closing price for stock AONE for the month of January 1990:

    SELECT ORDSYS.TimeSeries.TSMedian(close,
                            to_date('01/01/1990 00:00:00',
                                    'MM/DD/YYYY HH24:MI:SS'),
                            to_date('01/31/1990 23:59:59',
                                    'MM/DD/YYYY HH24:MI:SS')) TSMedian
      FROM ORDTDEV.Stocks_TS
      WHERE ticker='AONE';

This example might produce the following output:

    TSMEDIAN
    ----------
       15.4649
    1 row selected.

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

Return the lowest closing price for stock AONE for the month of January 1990:

    SELECT ORDSYS.TimeSeries.TSMin(close,
                            to_date('01/01/1990 00:00:00',
                                    'MM/DD/YYYY HH24:MI:SS'),
                            to_date('01/31/1990 23:59:59',
                                    'MM/DD/YYYY HH24:MI:SS')) TSMin
      FROM ORDTDEV.Stocks_TS
      WHERE ticker='AONE';

This example might produce the following output:

    TSMIN
    ----------
       15.1038
    1 row selected.

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.
- NumValues is zero (0) or negative.

### Example

Return the 10 lowest closing prices for stock AONE for the month of January 1996:

    SELECT * FROM THE( SELECT CAST(
                      ORDSYS.TimeSeries.TSMinN(close, 10,
                             to_date('01011996','MMDDYYYY'),
                             to_date('01311996','MMDDYYYY'))
                      as ORDSYS.ORDTNumTab)
                FROM ORDTDEV.stocks_ts
                WHERE ticker ='AONE');

This example might produce the following output:

    TSTAMP    VALUE
    --------- ----------
    15-JAN-96       37.8
    09-JAN-96    37.9675
    04-JAN-96    38.3025
    10-JAN-96      38.47
    03-JAN-96    38.6375
    16-JAN-96    38.9725
    11-JAN-96    39.0563
    08-JAN-96    39.3075
    12-JAN-96    39.5588
    17-JAN-96    39.6425
    10 rows selected.

## TSMultiply

### Format

ORDSYS.TimeSeries.TSMultiply (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  ts2 ORDSYS.ORDTNumSeriesIOTRef

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSMultiply (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  k NUMBER

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### 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

##### 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 two time series contain daily trade volumes for two stocks, each element of the returned time series contains the result of multiplying the volume in the first time series by the volume in the second time series for that day.
- If a time series (ts1) contains closing prices for a stock and if a constant (k) of 2 is specified, each element of the returned time series contains the closing price of ts1 multiplied by 2.

If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.

An exception is returned if any of the following conditions is true:

- An input time series is null.
- An input time series does not have an associated calendar.
- The calendars associated with ts1 and ts2 do not have the same frequency and aligned pattern.
- endDate is earlier than startDate.

### 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 ORDTUSER/ORDTUSER
    SET SERVEROUTPUT ON
    ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

    DECLARE
    tstCal ORDSYS.ORDTCalendar;
    startDate date;
    endDate   date;
    dummyval INTEGER;

    BEGIN

     startDate := TO_DATE('11/14/1996');
     endDate   := TO_DATE('12/14/1996');
     SELECT ORDSYS.TimeSeries.Display(
          ORDSYS.TimeSeries.TSMultiply(ts1.high, ts2.low, startDate, endDate),
         'TSMultiply Results') INTO dummyval
     FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2
     WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO';

    END;
    /

This example might produce the following output:

    TSMultiply Results :

    Calendar Data:
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/14/1996 00:00:00      1647.03
     11/15/1996 00:00:00      1668.8
     11/18/1996 00:00:00      1694.77
     11/19/1996 00:00:00      1717.2
     11/20/1996 00:00:00      1738.86
     11/21/1996 00:00:00      1764.16
     11/22/1996 00:00:00      1788.75
     11/25/1996 00:00:00      1809.56
     11/26/1996 00:00:00      1831.06
     11/27/1996 00:00:00      1849.38
     11/29/1996 00:00:00      1876.25
     12/02/1996 00:00:00      1910.4
     12/03/1996 00:00:00      1946.43
     12/04/1996 00:00:00      1969.64
     12/05/1996 00:00:00      2002.79
     12/06/1996 00:00:00      1995
     12/09/1996 00:00:00      2020.45
     12/10/1996 00:00:00      2046.8
     12/11/1996 00:00:00      2044.5
     12/12/1996 00:00:00      2060.08
     12/13/1996 00:00:00      2082.6
     -----------------------------

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

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

    SELECT ORDSYS.TimeSeries.TSProd(close,
                            to_date('01/01/1990 00:00:00',
                                    'MM/DD/YYYY HH24:MI:SS'),
                            to_date('01/31/1990 23:59:59',
                                    'MM/DD/YYYY HH24:MI:SS')) TSProd
      FROM ORDTDEV.Stocks_TS
      WHERE ticker='AONE';

This example might produce the following output:

    TSPROD
    ----------
    1.7126E+26
    1 row selected.

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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

This example might produce the following output:

    ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD
    ---------- ---------- ----------
            79      143.5 11.9791486

1 row selected.

## TSSubtract

### Format

ORDSYS.TimeSeries.TSSubtract (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  ts2 ORDSYS.ORDTNumSeriesIOTRef

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

or

ORDSYS.TimeSeries.TSSubtract (

  ts1 ORDSYS.ORDTNumSeriesIOTRef,

  k NUMBER

  [,startDate DATE, endDate DATE]

  ) RETURN ORDSYS.ORDTNumSeries;

### 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

##### 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 two time series contain daily trade volumes for two stocks, each element of the returned time series contains the result of subtracting the ts2 volume from the ts1 volume for that day.
- If a time series (ts1) contains closing prices for a stock and if a constant (k) of 1 is specified, each element of the returned time series contains the closing price of ts1 decreased by 1.

If ts1 and ts2 are specified, the function returns a time series whose calendar is the result of using the CombineCals function on the calendars associated with these two time series.

An exception is returned if any of the following conditions is true:

- An input time series is null.
- An input time series does not have an associated calendar.
- The calendars associated with ts1 and ts2 do not have the same frequency and aligned pattern.
- endDate is earlier than startDate.

### 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 ORDTUSER/ORDTUSER
    SET SERVEROUTPUT ON
    ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

    DECLARE
    tstCal ORDSYS.ORDTCalendar;
    startDate date;
    endDate   date;
    dummyval INTEGER;

    BEGIN

     startDate := TO_DATE('11/14/1996');
     endDate   := TO_DATE('12/14/1996');
     SELECT ORDSYS.TimeSeries.Display(
          ORDSYS.TimeSeries.TSSubtract(ts1.high, ts2.low, startDate, endDate),
         'TSSubtract Results') INTO dummyval
     FROM ORDTDEV.stockdemo_ts ts1, ORDTDEV.stockdemo_ts ts2
     WHERE ts1.ticker='ACME' and ts2.ticker='FUNCO';

    END;
    /

This example might produce the following output:

    TSSubtract Results :

    Calendar Data:
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/14/1996 00:00:00      45.13
     11/15/1996 00:00:00      46.16
     11/18/1996 00:00:00      47.13
     11/19/1996 00:00:00      48.15
     11/20/1996 00:00:00      49.18
     11/21/1996 00:00:00      50.16
     11/22/1996 00:00:00      51.15
     11/25/1996 00:00:00      52.19
     11/26/1996 00:00:00      53.22
     11/27/1996 00:00:00      54.29
     11/29/1996 00:00:00      55.25
     12/02/1996 00:00:00      56.12
     12/03/1996 00:00:00      56.97
     12/04/1996 00:00:00      57.98
     12/05/1996 00:00:00      58.87
     12/06/1996 00:00:00      60.25
     12/09/1996 00:00:00      61.23
     12/10/1996 00:00:00      62.2
     12/11/1996 00:00:00      63.5
     12/12/1996 00:00:00      64.59
     12/13/1996 00:00:00      65.6
     -----------------------------

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

### Example

Return the sum of the daily trading volumes for stock AONE for the month of January 1990 (that is, the total AONE volume for the month):

    SELECT ORDSYS.TimeSeries.TSSum(volume,
                            to_date('01/01/1990 00:00:00',
                                    'MM/DD/YYYY HH24:MI:SS'),
                            to_date('01/31/1990 23:59:59',
                                    'MM/DD/YYYY HH24:MI:SS')) TSSum
      FROM ORDTDEV.Stocks_TS
      WHERE ticker='AONE';

This example might produce the following output:

    TSSUM
    ----------
     104434900
    1 row selected.

## 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 any of the following conditions is true:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- endDate is earlier than startDate.

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

This example might produce the following output:

    ORDSYS.ORD ORDSYS.ORD ORDSYS.ORD
    ---------- ---------- ----------
            79      143.5 11.9791486

1 row selected.

## 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, outputs 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:

- The time series (ts) is null.
- The time series (ts) does not have an associated calendar.
- The calendar associated with the time series is invalid.
- The timestamps are not sorted.
- One or more timestamps are null, imprecise, or outside the date range of the calendar.
- One or more timestamps are included in the time series but 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).
- One or more timestamps are excluded from the time series but 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.

Contrast this function with IsValidTS, which simply checks whether a time series is valid.

You can use the DisplayValTS procedure (documented in this chapter) to display the information returned by the ValidateTS function.

The ValidateTS function cannot be called from SQL. It must be called from PL/SQL because of the OUT parameters.

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

       -- Example of validating a valid time series.
       SELECT ordsys.timeseries.display(numTS, 'A VALID TIME SERIES') INTO tempVal
       FROM dual;
       retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS);
       retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg, loDateTab,
                                 hiDateTab, impDateTab, dupDateTab,
                                 extraDateTab, missingDateTab);
       DBMS_OUTPUT.PUT_LINE('Value returned by IsValid  = ' || retIsValid);
       DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS  = ' || retValTS);
       ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab,
                              impDateTab, dupDateTab, extraDateTab, missingDateTab,
                              'Testing DisplayValTS');
       DBMS_OUTPUT.NEW_LINE;

       -- For illustration let us first create an invalid timeseries.
       --
       -- Here we are adjusting the calendar's minDate and maxDate to avoid
       -- getting a huge list of missing dates.
       --
       numTS.cal.minDate := TO_DATE('10/28/1996');
       numTS.cal.maxDate := TO_DATE('01/05/1997');

       -- Add Dates Before numTS.cal.minDate
       numTS.series(10).tstamp := numTS.cal.minDate - 1;
       numTS.series(11).tstamp := numTS.cal.minDate - 2;

       -- Add Dates Beyond numTS.cal.maxDate
       numTS.series(12).tstamp := numTS.cal.maxDate + 1;
       numTS.series(13).tstamp := numTS.cal.maxDate + 2;

       -- Add some null timestamps
       numTS.series(14).tstamp := NULL;
       numTS.series(15).tstamp := NULL;

       -- Add some imprecise dates (some are duplicated)
       numTS.series(17).tstamp := numTS.series(16).tstamp + 1/24;
       numTS.series(18).tstamp := numTS.series(16).tstamp + 15/24;

       -- Add some duplicate timestamps
       numTS.series(19).tstamp := numTS.series(18).tstamp;
       numTS.series(21).tstamp := numTS.series(20).tstamp;

       -- Add some extra dates in the middle
       numTS.series(37).tstamp := TO_DATE('12/28/1996');
       numTS.series(36).tstamp := TO_DATE('12/29/1996');

       -- Add some holes at the end
       numTS.series(numTS.series.count).tstamp := TO_DATE('01/04/1997');

       -- Example of validating an invalid time series.
       SELECT ordsys.timeseries.display(numTS, 'AN INVALID TIME SERIES')
       INTO tempVal FROM dual;
       retIsValid := ORDSYS.TIMESERIES.IsValidTS(numTS);
       retValTS := ORDSYS.TIMESERIES.ValidateTS(numTS, outMesg,
                             loDateTab, hiDateTab, impDateTab,
                             dupDateTab, extraDateTab, missingDateTab);
       DBMS_OUTPUT.PUT_LINE('Value returned by IsValid  = ' || retIsValid);
       DBMS_OUTPUT.PUT_LINE('Value returned by ValidateTS  = ' || retValTS);
       ORDSYS.TIMESERIES.DisplayValTS(retValTS, outMesg, loDateTab, hiDateTab,
                          impDateTab, dupDateTab, extraDateTab, missingDateTab,
                          'Testing DisplayValTS');
    END;
    /

This example might produce the following output:

    A VALID TIME SERIES :

    Name = ACME open NumSeries
    Calendar Data:
    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 01/01/1990 00:00:00
     MaxDate = 01/01/2001 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     11/14/1996 00:00:00      68
     11/15/1996 00:00:00      69
     11/18/1996 00:00:00      70
     11/19/1996 00:00:00      71
     11/20/1996 00:00:00      72
     11/21/1996 00:00:00      73
     11/22/1996 00:00:00      74
     11/25/1996 00:00:00      75
     11/26/1996 00:00:00      76
     11/27/1996 00:00:00      77
     11/29/1996 00:00:00      78
     12/02/1996 00:00:00      79
     12/03/1996 00:00:00      80
     12/04/1996 00:00:00      81
     12/05/1996 00:00:00      82
     12/06/1996 00:00:00      83
     12/09/1996 00:00:00      84
     12/10/1996 00:00:00      85
     12/11/1996 00:00:00      86
     12/12/1996 00:00:00      87
     12/13/1996 00:00:00      88
     12/16/1996 00:00:00      89
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/23/1996 00:00:00      94
     12/24/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     12/31/1996 00:00:00      99
     -----------------------------

    Value returned by IsValid  = 1
    Value returned by ValidateTS  = 1

    DisplayValTS: Testing DisplayValTS:

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

    AN INVALID TIME SERIES :

    Name = ACME open NumSeries
    Calendar Data:
    Calendar Name = BUSINESS-96
     Frequency = 4
     MinDate = 10/28/1996 00:00:00
     MaxDate = 01/05/1997 00:00:00
     patBits:
              0111110
     patAnchor = 01/07/1996 00:00:00
     onExceptions  :
     offExceptions :
         11/28/1996 00:00:00     12/25/1996 00:00:00
    Series Data:
     -----------------------------
     Date                    Value
     11/01/1996 00:00:00      59
     11/04/1996 00:00:00      60
     11/05/1996 00:00:00      61
     11/06/1996 00:00:00      62
     11/07/1996 00:00:00      63
     11/08/1996 00:00:00      64
     11/11/1996 00:00:00      65
     11/12/1996 00:00:00      66
     11/13/1996 00:00:00      67
     10/27/1996 00:00:00      68
     10/26/1996 00:00:00      69
     01/06/1997 00:00:00      70
     01/07/1997 00:00:00      71
           72
           73
     11/22/1996 00:00:00      74
     11/22/1996 01:00:00      75
     11/22/1996 15:00:00      76
     11/22/1996 15:00:00      77
     11/29/1996 00:00:00      78
     11/29/1996 00:00:00      79
     12/03/1996 00:00:00      80
     12/04/1996 00:00:00      81
     12/05/1996 00:00:00      82
     12/06/1996 00:00:00      83
     12/09/1996 00:00:00      84
     12/10/1996 00:00:00      85
     12/11/1996 00:00:00      86
     12/12/1996 00:00:00      87
     12/13/1996 00:00:00      88
     12/16/1996 00:00:00      89
     12/17/1996 00:00:00      90
     12/18/1996 00:00:00      91
     12/19/1996 00:00:00      92
     12/20/1996 00:00:00      93
     12/29/1996 00:00:00      94
     12/28/1996 00:00:00      95
     12/26/1996 00:00:00      96
     12/27/1996 00:00:00      97
     12/30/1996 00:00:00      98
     01/04/1997 00:00:00      99
     -----------------------------

    Value returned by IsValid  = 0
    Value returned by ValidateTS  = 0

    DisplayValTS: Testing DisplayValTS:

    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

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index