Intersect function

Syntax

Intersect(ts TimeSeries, 
         ts TimeSeries,...) 
returns TimeSeries;

Intersect(set_ts set(TimeSeries)) 
returns TimeSeries;

Intersect(begin_stamp  datetime year to fraction(5), 
         end_stamp    datetime year to fraction(5), 
         ts          TimeSeries, 
         ts          TimeSeries,...) 
returns TimeSeries;

Intersect(begin_stamp  datetime year to fraction(5), 
         end_stamp   datetime year to fraction(5), 
         set_ts      set(TimeSeries)) 
returns TimeSeries;

ts

The time series that form the intersection. Intersect can take from two to eight time series arguments.

set_ts

Indicates the intersection of a set of time series.

begin_stamp

The begin point of the clip.

end_stamp

The end point of the clip.

Description

The second and fourth forms of the function intersect a set of time series. The resulting time series has one DATETIME YEAR TO FRACTION(5) column followed by each column in each time series in order, not including the other time stamps. When using the second or fourth form, it is important to ensure that the order of the time series in the set is deterministic so that elements remain in the correct order.

Since the resulting time series is a different type from the input time series, the result of the intersection must be cast.

Intersect can be thought of as a join on the time stamp columns.

If any of the input time series is irregular, the resulting time series is irregular.

For the purposes of Intersect, the value at a specified timepoint is that of the most recent valid element. For regular time series, this is the value corresponding to the current interval, which can be NULL; it is not necessarily the most recent non-null value. For irregular time series, this condition never occurs, because irregular time series do not have null intervals.

For example, consider the intersection of two irregular time series, one containing bid prices for a certain stock, and one containing asking prices. The intersection of the two time series contains bid and ask values for each timepoint at which a price was either bid or asked. Now consider a timepoint at which a bid was made but no price was asked. The intersection at that timepoint contains the bid price offered at that timepoint, along with the most recent asking price.

If an intersection involves one or more regular time series, the resulting time series starts at the latest of the start points of the input time series and ends at the earliest of the end points of the regular input time series. If all the input time series are irregular, the resulting irregular time series starts at the latest of the start points of the input time series and ends at the latest of the end points. If a union involves one or more time series, the resulting time series starts at the first of the start points of the input time series and ends at the latest of the end points of the input time series. Other than this difference in start and end points, and of the resulting calendar, there is no difference between union and intersection involving time series.

In an intersection, the resulting time series has a calendar that is the combination of the calendars of the input time series with the AND operator. The resulting calendar is stored in the CalendarTable table. The name of the resulting calendar is a string containing the names of the calendars of the input time series joined by an ampersand (&). For example, if two time series are intersected, and mycal and yourcal are the names of their corresponding calendars, the resulting calendar is named mycal&yourcal.

To be certain of the order of the columns in the resultant time series when using Intersect with the set_ts argument, use the ORDER BY clause.

Apply also combines multiple time series into a single time series. Therefore, using Intersect within Apply is often unnecessary.

Returns

The time series that results from the intersection.

Example

select Intersect(d1.stock_data,
      d2.stock_data)::TimeSeries(stock_bar_union)
    from daily_stocks d1, daily_stocks d2
    where d1.stock_name='HCLTECH' and d2.stock_name='HWP';

select Intersect('2011-01-03 00:00:00.00000'
            ::datetime year to fraction(5),
            '2011-01-05 00:00:00.00000'
            ::datetime year to fraction(5),
            d1.stock_data,
            d2.stock_data
          )::TimeSeries(stock_bar_union)
   from daily_stocks d1, daily_stocks d2
   where d1.stock_name = 'HCLTECH' and d2.stock_name = 'HWP';