3
Time Series Usage

This chapter explains important procedures related to using Oracle8i Time Series. It contains the following major sections:

• Section 3.1, "Creating a Time Series Group"

• Section 3.2, "Creating a Calendar" (also validating the calendar)

• Section 3.3, "Maintaining a Map Table"

• Section 3.4, "Populating the Detail Table Using SQL*Loader" (loading time series data)

• Section 3.5, "Retrofitting Existing Tables" ("retrofitting" existing detail, calendar, and map tables by using administrative tools procedures to generate schema objects)

• Section 3.6, "Validating Time Series Consistency"

• Section 3.7, "Formulating Time Series Queries"

• Section 3.8, "Deriving Calendar Exceptions" (deriving exceptions from time series data)

• Section 3.9, "Using Product-Developer Functions"

For detailed explanations of Oracle8i Time Series concepts and terminology, see Chapter 2.

3.1 Creating a Time Series Group

You can use the administrative tools procedures to create a time series group (all the necessary time series schema objects), accepting default values for most object names. These procedures provide a convenient, simple way to create time series schema objects, and they are recommended for most users. These procedures are used in the quick-start demo (see Section 1.6.1) and the usage demo (see Section 1.6.2).

The following example shows the use of administrative tools procedures to create all the necessary schema objects:

DECLARE

BEGIN

--
-- Establish 'tsquick' as the time series group name for purposes of the
-- administrative tools procedures. Columns will automatically be created
-- for the time series name (which will be set to 'ticker') and a
-- timestamp. The columns for the opening, high, low, and closing prices
-- and the trading volume will be explicitly defined.
--

  ORDSYS.TSTools.Begin_Create_TS_Group('tsquick','flat');

-- Set 'ticker' as the name of the time series for functions.
-- Sample values for specific tickers include 'ACME', 'AONE', and 'XCORP'.

  ORDSYS.TSTools.Set_Flat_Attributes(tsname_colname => 'ticker');
  ORDSYS.TSTools.Set_Flat_Attributes(tsname_length => 10);

-- Define numeric columns for prices.

  ORDSYS.TSTools.Add_Number_Column('open');
  ORDSYS.TSTools.Add_Number_Column('high');
  ORDSYS.TSTools.Add_Number_Column('low');
  ORDSYS.TSTools.Add_Number_Column('close');

-- Define an integer column for trading volume (number of shares
-- traded on a given day).

  ORDSYS.TSTools.Add_Integer_Column('volume');

-- End the specification of schema objects and create the objects.
--

  ORDSYS.TSTools.End_Create_TS_Group;

  exception
    when others then
       begin
          ORDSYS.TSTools.Cancel_Create_TS_Group;
          raise;
       end;

END;
/

The preceding call to End_Create_TS_Group causes many schema objects to be created. Among them are:

• TSQUICK - object view. Use this when using Time Series functions.

  Example: SELECT TimeSeries.<function>(ts.OPEN) FROM TSQUICK ts;

• TSQUICK_RVW - relational view. Use this for protected insert, update, and delete operations. Uses an INSTEAD OF trigger.

• TSQUICK_TAB - flat table for detail data.

• TSQUICK_MAP - mapping (metadata) table. The quick-start demo associates a null calendar with each ticker.

• TSQUICK_CAL - table for calendar definitions. The quick-start demo defines a monthly calendar for use with scaleup operations.

3.2 Creating a Calendar

Calendars are needed if one or more of the following conditions apply:

• You are using any regular time series.

• You need to perform time scaling.

You have several options for creating a calendar, including:

• Create the calendar dynamically in the context of a query, using one of the calendar-creation functions. See the information on the Month function in Chapter 4 for an example.

• Create the calendar by inserting its definition in a table of calendars. If the table of calendars does not already exist, create it first.

Calendars for a regular time series are stored in the calendar table associated with that time series group. The calendar table typically has a name in the format groupname_CAL (for example, tsquick_cal for the quick-start demo). Calendars to be used for scaling can be stored in the group calendar table or in a calendar table that is separately created and managed.

Calendars are based on the system-defined data type ORDTCalendar, which is supplied with Oracle8i Time Series. ORDTCalendar has the following definition:

/* System-Defined Calendar Data Type */

CREATE TYPE ORDSYS.ORDTCalendar AS OBJECT (
  caltype INTEGER,
  name VARCHAR2(256),
  frequency INTEGER,
  pattern ORDSYS.ORDTPattern,
  minDate DATE,
  maxDate DATE,
  offExceptions ORDSYS.ORDTExceptions,
  onExceptions ORDSYS.ORDTExceptions);

Example 3-1 creates a table named my_calendars and defines a calendar named BusinessDays-97. The BusinessDays-97 calendar includes Mondays through Fridays in 1997, but excludes 04-Jul-1997 and 25-Dec-1997. Explanatory notes follow the example.

Example 3-1 Create a Calendar of Business Days

CREATE TABLE my_calendars of ORDSYS.ORDTCalendar;

INSERT INTO my_calendars 
	VALUES(
   ORDSYS.ORDTCalendar(
       0, 
       `BusinessDays-97', 
       4, 
       			ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(0,1,1,1,1,1,0), 
       						    (to_date(`01-05-97','MM-DD-YY'))), 
       				to_date(`01-01-97','MM-DD-YY'), 
       			to_date(`01-01-98','MM-DD-YY'),
       ORDSYS.			ORDTExceptions(to_date(`07-04-97','MM-DD-YY'), 
       							    to_date(`12-25-97','MM-DD-YY')),
       			NULL)); 

Notes on Example 3-1:

my_calendars is a table of ORDSYS.ORDTCalendar objects. The ORDTCalendar data type is described in Section 2.3.1.

0 (zero) for calendar type (caltype) indicates that this is an exception-based calendar. (This is the only calendar type currently supported.)

BusinessDays-97 is the name of this calendar.

4 is the frequency code for day.

The pattern is defined as an excluded occurrence followed by five included occurrences followed by an excluded occurrence (0,1,1,1,1,1,0). Because the frequency is daily and because the anchor date (05-Jan-1997) is a Sunday, Sundays are excluded, Mondays through Fridays are included, and Saturdays are excluded.

The calendar begins at the start of 01-Jan-1997 and ends at the start of 01-Jan-1998.

04-Jul-1997 and 25-Dec-1997 are off-exceptions (that is, excluded from the calendar).

NULL indicates that there are no on-exceptions (that is, no Saturday or Sunday dates to be included in the calendar).

After you create the calendar, you should validate it to ensure that you have not made any mistakes in the calendar definition. The following example validates the BusinessDays-97 calendar created by Example 3-1.

DECLARE
        tstCal ordsys.ordtcalendar;
        dummyVal integer;
        validFlag integer;
BEGIN

   -- Select a calendar into tstCal from my_calendars.
   select value(cal) into tstCal
   from my_calendars cal
   where cal.name = 'BusinessDays-97';

   -- Display the calendar
   select ordsys.timeseries.display(tstCal) into dummyVal from dual;
   dbms_output.new_line;

   validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal);

   if (validFlag = 1) then
      dbms_output.put_line('BusinessDays-97 calendar is valid.');
   else
      dbms_output.put_line('BusinessDays-97 calendar is NOT valid.');
      dbms_output.put_line('Use ValidateCal to determine inconsistency.');
   end if;

END;
/
Statement processed.

Calendar Name = BusinessDays-97
 Frequency = 4 (day)
 MinDate = 01-JAN-97
 MaxDate = 01-JAN-98
 patBits:
          0,1,1,1,1,1,0
 patAnchor = 05-JAN-97
 onExceptions  :     Atomic NULL

 offExceptions :
     04-JUL-97     25-DEC-97

BusinessDays-97 calendar is valid.

3.3 Maintaining a Map Table

A map table maintains the mapping, or coupling, between a time series (such as a specific stock ticker) and a calendar. When you create a time series group, the map table by default has a name in the form groupname_MAP. (In the quick-start demo, the map table is named tsquick_map; in the usage demo, the map table is named stockdemo_metadata.) The map table has two VARCHAR2 columns:

• Time series name that by default matches the tsname_colname value when the time series group was created (for example, ticker)

• Calendar name

The following example creates a map table named my_calendars_map:

CREATE TABLE my_calendars_map (
   ticker   VARCHAR2(5),
   calname VARCHAR2(256),
   CONSTRAINT  pk_my_calendars_map PRIMARY KEY (ticker));

For each row in the map table, the calendar name can be null or can contain the name of a calendar:

• If the calendar name column is null, no calendar will be used for Time Series functions (that is, the input time series will be treated as an irregular time series). The following example creates a row in the my_calendars_map table for each ticker in the tsquick_tab table and leaves the calname column null:

  INSERT INTO my_calendars_map (ticker)
       SELECT DISTINCT ticker FROM tsdev.tsquick_tab;
  
  

• If the calendar name column contains the name of a calendar, that calendar is used for Time Series functions specifying that time series. The following example creates two rows in the my_calendars_map table, associating two tickers with the BusinessDays-97 calendar:

  INSERT INTO my_calendars_map VALUES('ACME', 'BusinessDays-97');
  INSERT INTO my_calendars_map VALUES('SAMCO', 'BusinessDays-97');
  
  

For rows where a calendar name is specified, you can adopt one of the following strategies, depending on which calendars apply to which time series:

• Use the same calendar for all time series (the "shared calendar" approach). For example, map all tickers to a single calendar of stock trading days.

• Use a separate calendar for each time series. For example, create an acme calendar for the ACME ticker, a samco calendar for the SAMCO ticker, and so on.

• Use a combination of approaches: use some calendars for multiple time series, and perhaps some calendars for only one time series each. For example, some stocks might trade on exchanges with different holidays, or some stocks might have had trading suspended on certain days.

3.4 Populating the Detail Table Using SQL*Loader

To populate the underlying data storage table or tables, perform a bulk load of the time series data, preferably using the SQL*Loader utility if you have a large amount of data. For example, the tsquick.sql procedure uses SQL*Loader with hypothetical stock market data, as follows:

sqlldr userid=tsdev/tsdev control=tsquick.ctl log=tsquick.log bad=tsquick.bad 
skip=15 errors=1000
     


         

To update the time series data, perform incremental loads as needed.

To ensure the consistency of time series data during loading, you must choose one of the approaches described in Section 2.8.3:

• Adjust calendars to be consistent with the time series, if you are sure that all timestamps are correct.

  This strategy is normally appropriate when there is a unique calendar per time series.

• Validate that each time series is consistent with the calendar, if you expect time series data to adhere to a predefined calendar.

  This approach is particularly useful if the same calendar is used for all time series data being loaded.

This section describes how to perform bulk loading using these two approaches, and it also describes how to perform incremental loading.

The loading of time series data is usually performed under controlled circumstances, so it is safe to perform these loads directly to an underlying table instead of to a relational view.

3.4.1 Bulk Loading

After you create an index-organized table (IOT) to hold time series data (such as for the stockdemo demo database), you must populate the table with data. For a database of stock information, you may need to load millions of rows of daily summary information into the IOT.

SQL*Loader is recommended for loading large amounts of time series data. The following example shows a SQL*Loader script, with an excerpt from the sample data (stockdat.dat) and the SQL*Loader control file (stockdat.ctl). For complete information about SQL*Loader, see the Oracle8i Utilities manual.

The SQL*Loader script contains the following:

% sqlldr userid=tsdev/tsdev control=stockdat.ctl  
       log=stockdat.log bad=stockdat.bad errors=1000 
 

The stockdat.dat sample data file includes the following:

ACME  01-NOV-96  59.00  60.00  58.00  59.00  1000 
ACME  04-NOV-96  60.00  61.00  59.00  60.00  1000 
ACME  05-NOV-96  61.00  62.00  60.00  61.00  1000 
         ... 

The stockdat.ctl file contains the following

options (direct=true) 
unrecoverable 
load data 
infile 'stockdat.dat' 
replace 
into table stockdemo 
sorted indexes (StockTabx)
fields terminated by whitespace  
(ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume) 
 

SQL*Loader can handle many file formats and delimiters, as documented in the Oracle8i Utilities manual.

After the load has completed, you may want to choose one of the following approaches for ensuring calendar consistency:

• Adjust calendars to conform to time series data (see Section 3.4.1.1).

• Validate that the time series conforms to the calendar (see Section 3.4.1.2).

In either case, you may need to update the exception lists of your calendars.

3.4.1.1 Adjusting Calendars to Conform to Time Series Data

Often you will want to create calendars that conform to the time series data that you are receiving. In this case, you usually know the frequency and the pattern of a calendar, but not the specific on- or off-exceptions. You can extract these exceptions from the data by using the DeriveExceptions function.

3.4.1.2 Validating That the Time Series Conforms to the Calendar

Often you will want to ensure that the time series data extracted from the incoming data conforms to a predefined calendar. To do this, insert the exceptions either when you create the calendar or afterward with the InsertExceptions functions (or do both, creating the calendar with some exceptions and then adding others); then use the IsValidTimeSeries function to check that the time series is consistent with the calendar.

You can insert exceptions when you define the calendar. For example, the following statement specifies 28-Nov-1996 and 25-Dec-1996 as off-exceptions in the calendar named BUSINESS-96:

INSERT INTO stockdemo_calendars VALUES( 
       ORDSYS.ORDTCalendar( 
             0,
             'BUSINESS-96', 
             4, 
             ORDSYS.ORDTPattern( 
                      ORDSYS.ORDTPatternBits(0,1,1,1,1,1,0), 
                           TO_DATE('01-JAN-1995','DD-MON-YYYY')),  
             TO_DATE('01-JAN-1990','DD-MON-YYYY'), 
             TO_DATE('01-JAN-2001','DD-MON-YYYY'),  
             ORDSYS.ORDTExceptions( 
                             TO_DATE('28-NOV-1996','DD-MON-YYYY'), 
                             TO_DATE('25-DEC-1996','DD-MON-YYYY')),  
             ORDSYS.ORDTExceptions() 
             )); 

You can also add exceptions after the calendar is defined by using the InsertExceptions function. For example, the following statement adds 01-Jan-1997, 17-Feb-1997, and 26-May-1997 as off-exceptions:

UPDATE stockdemo_calendars cal 
   SET cal = (SELECT ORDSYS.Calendar.InsertExceptions( 
                      VALUE(cal), 
                      ORDSYS.ORDTDateTab( 
                            to_date('01-JAN-97','DD-MON-YY'), 
                            to_date('17-FEB-97','DD-MON-YY'), 
                            to_date('26-MAY-97','DD-MON-YY'))) 
          FROM dual) 
	WHERE cal.name = 'BUSINESS-96'; 

After you have defined the calendar and populated the exception lists, you can use the IsValidTimeSeries function to check that the time series is consistent with the calendar.

3.4.2 Incremental Loading

After you have performed the bulk load of time series data and have started using Oracle8i Time Series, you will probably want to add data periodically. For example, every trading day after the stock exchange closes, that day's data for each ticker becomes available.

As with bulk loading, incremental loading is typically done in a controlled environment. You know which timestamps will become off-exceptions, and you can explicitly update the exception lists of the appropriate calendars. The following example demonstrates such an update:

UPDATE stockdemo_calendars cal 
 SET cal = (SELECT ORDSYS.Calendar.InsertExceptions( 
                         VALUE(cal), 
                         to_date('01-JAN-97','DD-MON-YY')) 
             FROM dual) 
        WHERE cal.name = 'XCORP';

The SQL*Loader utility is recommended for performing an incremental load of such additional data. The following example shows a SQL*Loader script, with an excerpt from the sample daily data (stockinc.dat) and the SQL*Loader control file (stockinc.ctl).

The SQL*Loader script contains the following:

sqlldr userid=tsdev/tsdev control=stockinc.ctl
        log=stockinc.log bad=stockinc.bad errors=1000
 

The stockinc.dat sample data file includes the following:

ACME  02-JAN-97 100.00 101.00  99.00 100.00  1000 
FUNCO 02-JAN-97  25.00  25.00  25.00  25.00  2000 
SAMCO 02-JAN-97  39.00  40.00  38.00  39.50 30000 
        ... 

The stockinc.ctl file contains the following:

load data 
infile 'stockinc.dat' 
append 
into table stockdemo 
fields terminated by whitespace  
(ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume)

Note the following differences in the control file for incremental loading as opposed to bulk loading:

• The conventional path is used instead of the direct path. That is, the control file for incremental loading does not contain the line options (direct=true).

  The conventional path is better for incremental loading because the amount of new data (daily stock information) is small relative to the total amount of data. For an explanation of conventional and direct paths, including situations in which the conventional path is necessary or preferable, see the SQL*Loader documentation in the Oracle8i Utilities manual.

• The APPEND keyword is specified, so that the new data is appended to the existing tabular data.

3.5 Retrofitting Existing Tables

You can use the administrative tools procedures to "retrofit" existing tables (that is, generate schema objects using existing detail, calendar, and map tables). The retrofit demo uses this approach, and the statements and comments in the retrofit.sql file reflect the approach described in this section. (The existing tables that are retrofitted are created in the tables.sql procedure, which the usage demo invokes before the retrofit.sql procedure.)

To use the administrative tools procedures to retrofit existing tables:

1. Create the time series schema, specifying that the tables already exist and using the Add_Existing_Column procedure to identify each existing column to be included in the time series schema objects. For example:

   DECLARE
   
   BEGIN
   
   -- Establish 'stockdemo_ts' as the time series group name for purposes
   -- of the administrative tools procedures.
   
     ORDSYS.TSTools.Begin_Create_TS_Group('stockdemo_ts','flat');
   
     -- Assert that the detail, map, and calendar tables exist,
     -- and define the names for these tables.
     -- Explicitly set the name of the relational view.
     -- Explicitly set the names of the timestamp and time series name
     -- columns.
   
     ordsys.tstools.set_flat_attributes(
           detail_table_name    => 'stockdemo',
           detail_table_exists  => 1,
           map_table_name       => 'stockdemo_metadata',
           map_table_exists     => 1,
           cal_table_name       => 'stockdemo_calendars',
           cal_table_exists     => 1,
           tstamp_colname       => 'tstamp',
           tsname_colname       => 'ticker',
           rel_view_name        => 'stockdemo_sv');
   
     -- Tell TSTools the names of existing time series columns
     -- (as defined for the table stockdemo)
   
     ORDSYS.TSTools.Add_Existing_Column('open');
     ORDSYS.TSTools.Add_Existing_Column('high');
     ORDSYS.TSTools.Add_Existing_Column('low');
     ORDSYS.TSTools.Add_Existing_Column('close');
     ORDSYS.TSTools.Add_Existing_Column('volume');
   
   -- End the specification of schema objects and create the objects.
   
     ORDSYS.TSTools.End_Create_TS_Group;
   
     exception
       when others then
          begin
             ORDSYS.TSTools.Cancel_Create_TS_Group;
             raise;
          end;
   
   END;
   /
   
   

2. Grant specific privileges on the views to intended users. For example:

-- Grant SELECT privileges on the object view.GRANT SELECT ON stockdemo_ts TO tsuser;-- Grant SELECT, UPDATE, DELETE privileges on the relational view.GRANT SELECT,INSERT,UPDATE,DELETE on stockdemo_sv TO tsuser;GRANT RESOURCE TO tsuser;

3.6 Validating Time Series Consistency

Choose one of the following approaches to ensuring the consistency of time series data, using the guidelines in Section 2.8.3:

• Adjust calendars to be consistent with the time series.

  Use the DeriveExceptions function in adjusting a calendar to be consistent with the time series. See Section 2.2.5 for more information about this approach.

• Validate that each time series is consistent with the calendar.

  Use the IsValidTS function to check that the time series is consistent with the calendar. See the IsValidTS function reference information in Chapter 5.

3.7 Formulating Time Series Queries

Formulating time series queries involves invoking time series or time scaling functions, or both. Example 3-2 uses the Mavg time series function to obtain 10-day moving average of the closing price for stock ACME for December 1996, and it uses the ScaleupSum time scaling function to obtain monthly trading volumes for stock ACME. (The results shown in the example reflect sample data for the Oracle8i Time Series usage demo.)

Example 3-2 Formulate Time Series Queries

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

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.

SELECT to_char(tstamp) tstamp, value
  FROM tsdev.stockdemo_ts ts, tsdev.stockdemo_calendars cal,
     TABLE (CAST(ORDSYS.TimeSeries.ExtractTable(
         ORDSYS.TimeScale.ScaleupSum(ts.volume,
                   VALUE(cal))
       ) AS ORDSYS.ORDTNumTab)) t
   WHERE ts.ticker='ACME' and cal.name='Monthly';

TSTAMP    VALUE      
--------- ---------- 
01-NOV-96      20000 
01-DEC-96      21000 
2 rows selected.

3.8 Deriving Calendar Exceptions

This section explains in greater detail the approaches to deriving calendar exceptions from time series data. These approaches were introduced in Section 2.2.5; see that section for information on concepts related to exceptions and the reasons for choosing a particular approach.

3.8.1 Deriving Exceptions Using a Time Series (Approach 1)

This approach to deriving exceptions takes a time series and optionally a date range as input parameters, using the following form of the function:

DeriveExceptions(inputTS ORDTNumSeriesIOTRef

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

or

DeriveExceptions(inputTS ORDTVarchar2SeriesIOTRef

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

The input time series (inputTS) has an associated calendar and data for all the desired timestamps (for example, daily closing prices for stock XYZ for all trading days during the period for the time series or the date range bounded by startDate and endDate). A calendar is returned that is defined on the same pattern and frequency as the input calendar, and the exception lists of the returned calendar are populated to be consistent with the time series data. The exception lists are updated based on finding timestamps that are in the calendar pattern or in the time series, but not in both. (A timestamp is in the calendar pattern if it is within the date range of the calendar and maps to an on (1) bit in the pattern.)

The returned calendar's on- and off- exceptions are populated based on the calendar pattern and the time series, as follows:

• All timestamps that are in the calendar pattern but not in the time series become off-exceptions.

  For example, 04-Jul-1997 (Friday) is in the pattern of a stock trading calendar, but it is not a date on which U.S. stocks were traded.

• All timestamps that are in the time series but are not in the calendar pattern become on-exceptions.

The following example populates a calendar named Quarterly with exceptions based on the actual data in the unemployment_rate time series (in which data for some quarters is missing):

UPDATE myts_cal cal
  SET  cal =
  (SELECT ORDSYS.TimeSeries.DeriveExceptions(ts.unemployment_rate)
              FROM   myts ts
              WHERE  ts.region = '1')
  WHERE cal.name = 'Quarterly';

3.8.2 Deriving Exceptions Using a Calendar and Table of Dates (Approach 1A)

This approach to deriving exceptions takes a calendar and an ORDTDateTab (that is, a table of dates) as input parameters, using the following form of the function:

DeriveExceptions(cal ORDTCalendar, DateTab ORDTDateTab

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

The table of dates (DateTab parameter) includes all dates in the time series, for example, all dates on which stock XYZ traded. A calendar is returned that is defined on the same pattern and frequency as the input calendar, and the exception lists of the returned calendar are populated to be consistent with the time series data in DateTab. The exception lists are updated based on finding timestamps that are in the calendar pattern or in the table of dates, but not in both. (A timestamp is in the calendar pattern if it is within the date range of the calendar and maps to an on (1) bit in the pattern.)

The returned calendar's on- and off- exceptions are populated based on the calendar pattern and the table of dates, as follows:

• All timestamps that are in the calendar pattern but not in the table of dates become off-exceptions.

  For example, 04-Jul-1997 (Friday) is in the pattern of a stock trading calendar, but it is not a date on which U.S. stocks were traded.

• All timestamps that are in the table of dates but are not in the calendar pattern become on-exceptions.

The following example derives the exceptions for all time series in the stockdemo table and updates the corresponding calendars in the stockdemo_calendars table:

UPDATE stockdemo_calendars cal 
  SET cal = (SELECT ORDSYS.Calendar.DeriveExceptions( 
                VALUE(cal), 
                CAST(multiset( 
                  SELECT s.tstamp 
                  FROM stockdemo s 
                  WHERE cal.name = s.ticker) AS ORDSYS.ORDTDateTab)) 
            FROM dual);
 

This approach (Approach 1A) to deriving calendar exceptions has the following requirements:

• The input table of dates must be sorted in ascending timestamp order before the call to the DeriveExceptions function.

• The precision of the timestamps of the dates in the table must conform to the frequency of the input calendar.

3.8.3 Deriving Exceptions Using Two Time Series Parameters (Approach 2)

This approach to deriving exceptions takes two time series references as input parameters, using the following form of the function:

DeriveExceptions(series1 ORDTNumSeriesIOTRef,

series2 ORDTNumSeriesIOTRef)

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

or

DeriveExceptions(series1 ORDTVarchar2SeriesIOTRef,

series2 ORDTVarchar2SeriesIOTRef)

[, startDate DATE [, endDate DATE]]

) RETURN ORDSYS.ORDTCalendar;

This overloading of the DeriveExceptions function allows the input parameters to be time series REFs (either two ORDTNumSeriesIOTRef parameters or two ORDTVarchar2SeriesIOTRef parameters).

Before calling DeriveExceptions, you must construct a time series based on a reference calendar. This time series will contain all the timestamps within the date range (minDate through maxDate) of the calendar.

The following example builds a reference time series based on a calendar named PATTERN-ONLY. An INSERT statement populates the time series named PATTERN-ONLY with the valid timestamps between the starting and ending dates of the calendar.

INSERT INTO stocks(ticker,tstamp) 
SELECT 'PATTERN-ONLY', 
       t1.c1 
       FROM  
       (SELECT column_value c1 FROM the 
        (SELECT CAST(ORDSYS.Calendar.TimeStampsBetween(VALUE(cal), 
                                                						cal.mindate,
                                                						cal.maxdate)
               AS ORDSYS.ORDTDateTab)    
        FROM stock_calendars cal 
        WHERE cal.name = 'PATTERN-ONLY')) t1;

The insertion is made directly into the underlying table, not into the relational view. Using the underlying table is safe here because the time series is presumed to be correct, so the mechanisms for ensuring consistency between the time series and the calendar provided by the relational view are not needed in this case.

The PATTERN-ONLY calendar should have no exceptions. If this calendar has any exceptions, the resulting time series will have exception lists that are not null, which will cause the DeriveExceptions function to report an error.

After you create the reference time series, call the DeriveExceptions function with the reference time series as the first parameter (series1). DeriveExceptions compares the dates in series1 with the dates in series2, and it returns the calendar of series2 with the exceptions created as follows:

• All timestamps that are in series1 but not in series2 become off-exceptions.

  For example, if series2 contains dates on which stock XYZ traded and 04-Jul-1997 (Friday) is not in that time series, then 04-Jul-1997 is added to the calendar as an off-exception.

• All timestamps that are in series2 but not in series1 become on-exceptions.

The following example uses the reference time series created in the preceding statement to update the exception lists of every other calendar in the stockdemo_calendars table, with the exceptions for each calendar derived from the timestamps in the associated time series. (This example assumes that each calendar maps to a time series with the same name.)

UPDATE stockdemo_calendars cal 
 SET cal = (SELECT ORDSYS.TimeSeries.DeriveExceptions(ts1.open,ts2.open) 
           FROM stockdemo_ts ts1, stockdemo_ts ts2 
             WHERE ts1.ticker = 'PATTERN-ONLY' and ts2.ticker = cal.name) 
WHERE cal.name <> 'PATTERN-ONLY'; 

This approach (Approach 2) to deriving calendar exceptions has the following requirements:

• The input parameters to the DeriveExceptions function must be either two ORDTNumSeriesIOTRef parameters or two ORDTVarchar2SeriesIOTRef parameters. ORDTNumSeries and ORDTVarchar2Series variants are not supported for this function.

• Calendars of the time series input parameters must have the same frequency and pattern.

• The first time series parameter (PATTERN-ONLY time series) must have no exceptions.

• The starting date (minDate) of the calendar of the second time series must be greater (later) than or equal to the starting date of the calendar of the first time series.

• The ending date (maxDate) of the calendar of the second time series must be less (earlier) than or equal to the ending date of the calendar of the first time series.

3.9 Using Product-Developer Functions

Product-developer functions, described in Section 2.9.2, let you modify and expand the Oracle8i Time Series capabilities. For example, an ISV could develop additional time series analysis functions by calling product-developer functions.

The following example shows the use of the IsValidDate, NumTstampsBetween, and OffsetDate product-developer functions in a PL/SQL implementation of the Lead function. The Lead function accepts an input time series and a lead_date, and returns a time series where the starting timestamp is the lead_date. (Note that to simplify the presentation, some error checking has been omitted.)

create function Lead (ts ORDSYS.ORDTNumSeries, lead_date date)
     return ORDSYS.ORDTNumSeries is 
i integer;
outts ORDSYS.ORDTNumSeries; /* Temporary Storage for Result */ 
new_tstamp date;   /* Changeable version of lead_date */ 
last_lead_date date; /* Last timestamp of the output time series*/ 
first_tstamp date; /* First timestamp of
                      the input time series */ 
last_index integer; /* Last index of the input time series */ 
last_tstamp date;       /* Last timestamp of the input time series */ 
units integer;      /* Number of timestamps between input and 
                       output time series */ 
 
ERR_LEAD_TSTAMP_BOUNDS constant integer := 20540; 
ERR_LEAD_TSTAMP_BOUNDS_MSG constant varchar2(100) :=  
         'Projected lead timestamp beyond calendar bounds'; 
 
begin 
     first_tstamp :=ts.series(1).tstamp; 
     last_index :=ts.series.last; 
     last_tstamp :=ts.series(last_index).tstamp; 
 
     if ORDSYS.Calendar.IsValidDate(ts.cal, lead_date) = 0 then 
           Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS, 
                   ERR_LEAD_TSTAMP_BOUNDS_MSG); 
     end if; 
 	 
     /* units is the number of timestamps between the first timestamp of 
     the input time series and lead_date. */ 
     units := ORDSYS.Calendar.NumTimeStampsBetween(ts.cal, first_tstamp, 
              lead_date); 
 	 
     last_lead_date := ORDSYS.Calendar.OffsetDate(ts.cal, last_tstamp, 
                      units); 
     if last_lead_date is null then 
           Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS, 
                    ERR_LEAD_TSTAMP_BOUNDS_MSG); 
     end if; 
	 
     /* Instantiate output time series. */ 
     outts := ORDSYS.ORDTNumSeries('Lead Result', ts.cal, ORDSYS.ORDTNumTab());
     outts.series.extend(last_index); 
 
     /* Assign the first timestamp of the output time series to 
     first_lead_date. Copy value from input time series to output 
     time series. */ 
     new_tstamp := lead_date; 
     outts.series(1) := ORDSYS.ORDTNumCell(new_tstamp, ts.series(1).value);
	 
     /* Assign subsequent timestamps by calling OffsetDate with the 
     previous date and an offset of 1. */ 
     for i in 2..outts.series.last loop 
          	new_tstamp := ORDSYS.Calendar.OffsetDate(ts.cal, 
            outts.series(i-1).tstamp, 1); 
          	outts.series(i) := ORDSYS.ORDTNumCell(new_tstamp, 
            ts.series(i).value); 
     end loop; 
 
     return(outts); 
end; 

For other examples of using product-developer functions, see the files for the advanced-developer demo (described briefly in Table 1-1 in Section 1.6).

---