Clip function

The Clip function extracts data between two timepoints in a time series and returns a new time series that contains that data. You can extract periods of interest from a large time series and to store or operate on them separately from the large series.

Syntax

Clip(ts           TimeSeries, 
    begin_stamp  datetime year to fraction(5), 
    end_stamp    datetime year to fraction(5), 
    flag        integer default 0) 
returns TimeSeries;

Clip(ts           TimeSeries, 
    begin_stamp  datetime year to fraction(5), 
    end_offset   integer, 
    flag        integer default 0) 
returns TimeSeries;

Clip(ts           TimeSeries, 
    begin_offset integer, 
    end_stamp    datetime year to fraction(5), 
    flag        integer default 0) 
returns TimeSeries;

Clip(ts           TimeSeries, 
    begin_offset integer, 
    end_offset   integer, 
    flag        integer default 0) 
returns TimeSeries;

ts

The time series to clip.

begin_stamp

The begin point of the range. Can be NULL.

end_stamp

The end point of the range. Can be NULL.

begin_offset

The begin offset of the range (regular time series only).

end_offset

The end offset of the range (regular time series only).

flag (optional)

The configuration of the resulting time series. Each flag value other than 0 reverses one aspect of the default behavior. The value of the flag argument is the sum of the flag values that you want to use.

0 = Default behavior:

The origin of the resulting time series is the later of the begin point and the origin of the input time series.
Hidden elements are not included in the resulting time series.
The resulting time series has the same regularity as the input time series.
The first record in a resulting irregular time series has the timestamp of the begin point and the value of the first record from the input time series that is equal to or earlier than the begin point.

1 = The origin of the resulting time series is the earlier of the begin point and the origin of the input time series. For regular time series, timepoints that are before the origin of the time series are set to NULL. For irregular time series, has no effect.

2 = Hidden elements are included and kept hidden in the resulting time series.

4 = Hidden elements are included and revealed in the resulting time series.

8 = The resulting time series is irregular regardless of whether the input time series is irregular.

16 = For irregular time series, the resulting time series begins with the first record that is equal to or later than the begin point. For regular time series, has no effect.

Description

The Clip functions all take a time series, a begin point, and an end point for the range.

For regular time series, the begin and end points can be either integers or time stamps. If the begin point is an integer, it is the absolute offset of an entry in the time series. Data at the beginning and ending offsets is included in the resulting time series. If the begin point is a time stamp, the Clip function uses the calendar of the input time series to find the offset that corresponds to the time stamp. If there is no entry in the time series exactly at the requested time stamp, Clip uses the time stamp that immediately follows the specified time stamp as the begin point of the range.

The end point is used in the same way as the begin point, except that it specifies the end of the range, rather than its beginning. The begin and end points can be NULL, in which case the beginning or end of the time series is used.

For irregular time series, only time stamps are allowed for the begin and end points. The timestamp of the first record in a resulting irregular time series is later than or equal to the begin point. However, the value of a record in an irregular time series persists until the next record. Therefore, by default, the first record in the resulting time series can have a value that corresponds to an earlier timestamp than the begin point. You can specify that the first record in the resulting time series is the first record whose timestamp is equal to or after the begin point by including the flag argument value of 16.

You can specify that the resulting time series is irregular by including the flag argument value of 8.

You can choose whether the origin of the resulting time series can be earlier than the origin of the input time series by setting the flag argument. By default, the origin of the resulting time series cannot be earlier than the origin of the input time series. You can also control how hidden elements are handled with the flag argument. By default, hidden elements from the input time series are not included in the resulting time series. You can include hidden element in the resulting time series and specify whether those elements remain hidden or are revealed in the resulting time series.

Returns

A new time series that contains only data from the requested range. The new series has the same calendar as the original, but it can have a different origin and number of entries.

Examples

The results of the Clip function are slightly different for regular and irregular time series.

Example 1: Regular time series

The following query extracts data from a time series and creates a table that contains the specified stock data for a single week:

create table week_1_analysis (stock_id int, stock_data 
   TimeSeries(stock_bar));
insert into week_1_analysis
    select stock_id,
   Clip(stock_data,
      '2011-01-03 00:00:00.00000'
       ::datetime year to fraction(5),
      '2011-01-07 00:00:00.00000'
       ::datetime year to fraction(5))
from daily_stocks
where stock_name = 'IBM';

The following query returns the first six entries for a specified stock in a time series:

select Clip(stock_data, 0, 5)
from daily_stocks
where stock_name = 'IBM';

Example 2: Irregular time series

An irregular time series has the following values:

2005-12-17 10:23:00.00000  26.46
2006-01-03 13:19:00.00000  27.30
2006-01-04 13:19:00.00000  28.67
2006-01-09 13:19:00.00000  30.56

The following statement extracts data from a time series over a five day period:

EXECUTE FUNCTION Transpose ((
  select Clip(
      tsdata,
      "2006-01-01 00:00:00.00000"::datetime year to fraction (5),
      "2006-01-05 00:00:00.00000"::datetime year to fraction (5),
      0)
  from ts_tab
  where station_id = 228820)) ;

The resulting irregular time series is as follows:

2006-01-01 00:00:00.00000  26.46
2006-01-03 13:19:00.00000  27.30
2006-01-04 13:19:00.00000  28.67

The first record has a time stamp equal to the begin point of the clip and the value of the first original value. Because the time series is irregular, a record persists until the next record. Therefore, the value of 26.46 is still valid on 2006-01-01.

However, if the Clip function includes the flag argument value of 16, the first value of the resulting time series is later than the begin point of the clip. The following statement extracts data that is after the begin point:

EXECUTE FUNCTION Transpose ((
  select Clip(
      tsdata,
      "2006-01-01 00:00:00.00000"::datetime year to fraction (5),
      "2006-01-05 00:00:00.00000"::datetime year to fraction (5),
      16)
  from ts_tab
  where station_id = 228820)) ;

The resulting irregular time series is as follows:

2006-01-03 13:19:00.00000  27.30
2006-01-04 13:19:00.00000  28.67