Union function

Syntax

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

Union(set_ts set(TimeSeries)) 
returns TimeSeries;

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

Union(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 union. Union can take from two to eight time series arguments.

set_ts

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 perform a union of 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. 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 the elements remain in the correct order.

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

Union can be thought of as an outer join on the time stamp.

In a union, the resulting time series has a calendar that is the combination of the calendars of the input time series with the OR 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, separated by a vertical bar ( | ). For example, if two time series are combined, and mycal and yourcal are the names of their corresponding calendars, the resulting calendar is named mycal|yourcal. If all the time series have the same calendar, then Union does not create a new calendar.

For a regular time series, if a time series does not have a valid element at a timepoint of the resulting calendar, the value for that time series element is NULL.

To be certain of the order of the columns in the resultant time series when using Union over a set, use the ORDER BY clause.

For the purposes of Union, the value at a given 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 since irregular time series do not have null intervals.

For example, consider the union of two irregular time series, one containing bid prices for a certain stock, and one containing asking prices. The union 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 union 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.

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

Returns

The time series that results from the union.

Example

select Union(s1.stock_data, 
   s2.stock_data)::TimeSeries(stock_bar_union)
from daily_stocks s1, daily_stocks s2
where s1.stock_name = 'HCLTECH' and s2.stock_name = 'HWP';

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