ProjectedClip function

The ProjectedClip function extracts data from specified columns 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 store or operate on them separately from the large time series.

Syntax

ProjectedClip(ts           TimeSeries, 
              begin_stamp  DATETIME YEAR TO FRACTION(5), 
              end_stamp    DATETIME YEAR TO FRACTION(5), 
              flag         INTEGER DEFAULT 0) 
returns TimeSeries;

ProjectedClip(ts           TimeSeries, 
              begin_stamp  DATETIME YEAR TO FRACTION(5), 
              end_stamp    DATETIME YEAR TO FRACTION(5), 
              flag         INTEGER DEFAULT 0,
              column_list  LVARCHAR) 
returns TimeSeries;
ts
The time series to clip.
begin_stamp
The beginning point of the range. Can be NULL, which indicates the origin of the time series.
end_stamp
The end point of the range. Can be NULL, which indicates the last value of the time series.
flag (optional)
The configuration of the resulting time series. For details, see the Clip function.
column_list (optional)
A list of column names from the input time series to include in the output time series. Separate column names with a comma.
Append (lf) to the column name to designate a low frequency column.
Append (nn) to the column name to designate a column with no null return values.

Description

The ProjectedClip function is similar to the Clip function, except that the ProjectedClip function can return a subset of the columns in the input time series instead of all the columns.

When you run the ProjectedClip function, cast the results to an existing output time series. The rules for defining the output time series depend on whether you include a column list:

  • If you do not include a column list, the names and corresponding data types of the columns in the output time series must be the same as in the input time series. The output time series can have fewer columns than the input time series. Columns from the input time series are mapped by name to the corresponding columns in the output time series.
  • If you include a column list, the following rules apply:
    • The column names and corresponding data types in the column list must match the input time series. The order and number of columns can differ.
    • The number of columns and corresponding data types in the output time series must match the column list. The names of the columns can differ.

    Column values from the input time series are written to the output time series in the order that is specified by the column list.

The data that you load into your time series might be configured to store a null value when a value does not differ from the previous value. If you have a column that has a low frequency of non-null values, you can specify to return the previous non-null value, if one exists, instead of NULL:

  • Replace only the first value for a column, if that value is null. Append (lf) to the column name in the column list to designate a low frequency column.
  • Replace all null values with the previous non-null values. Append (nn) to the column name in the column list to designate a column with no null return values.

If no previous non-null value exists for a low frequency or no nulls column, NULL is returned. For example, if you have rolling window containers, NULL is returned when the last non-null value is no longer in an active window. Also, the last non-null value is not returned if the first returned value is a null element, which does not have a time stamp or values for any columns.

Returns

The output time series to which the function is cast. The output time series contains data from only the requested range and columns. The output time series has the same calendar as the input time series, but it can have a different origin and number of entries.

Examples

In the following examples, the input time series has the following TimeSeries data type definition:

CREATE ROW TYPE ts (
  tstamp DATETIME YEAR TO FRACTION(5),
  i01  INTEGER,
  i02  INTEGER,
  i03  INTEGER
  i04  INTEGER,
  f01  FLOAT,
  f02  FLOAT);

The input time series has the following time series data:

tstamp               i01  i02   i03   f01   f02
2014-01-01 00:00:00  100  200   null  1     null
2014-01-01 01:00:00  101  null  null  1.01  null
2014-01-01 02:00:00  102  null  null  1.02  null
2014-01-01 03:00:00  103  null  null  1.03  null
2014-01-01 04:00:00  104  204   304   1.04  2.04
2014-01-01 05:00:00  105  null  305   1.05  null
2014-01-01 06:00:00  106  null  null  1.06  2.06
2014-01-01 07:00:00  107  207   307   1.07  null

Example: Include a column list

The following query specifies to clip values from three columns in the ts time series and return the results in the pc_row time series:

ProjectedClip(ts, 
	'2014-01-01 02:00:00', 
	'2014-01-01 06:00:00', 
	0, 
	'i01,i02,f02')::TIMESTAMP(pc_row)

The pc_row time series has the following structure:

CREATE ROW TYPE pc_row (
  tstamp DATETIME YEAR TO FRACTION(5),
  value1  INTEGER,
  value2  INTEGER,
  value3  FLOAT);

The names of the columns in the column list and the pc_row times series differ, but the data types and order of the columns are the same:

  • The value1 column, an INTEGER, maps to the i01 column.
  • The value2 column, an INTEGER, maps to the i02 column.
  • The value3 column, a FLOAT, maps to the f02 column.

The ProjectedClip function returns the following data in the pc_row time series:

tstamp               value1  value2   value3
2014-01-01 02:00:00  102     null     null
2014-01-01 03:00:00  103     null     null
2014-01-01 04:00:00  104     204      2.04
2014-01-01 05:00:00  105     null     null
2014-01-01 06:00:00  106     null     2.06

Example: Omit a column list

The following query specifies to clip values from the ts time series and return the results in the pc_row time series:

ProjectedClip(ts,
             '2014-01-01 02:00:00', 
             '2014-01-01 06:00:00',
             0)::TIMESTAMP(pc_row)'

When you do not include a column list, the names, data types, and order of the columns in the pc_row time series must be the same as in the ts time series. For this example, the pc_row time series has the following structure:

CREATE ROW TYPE pc_row (
  tstamp DATETIME YEAR TO FRACTION(5),
  i01  INTEGER,
  i02  INTEGER,
  f01  FLOAT);

The difference between the pc_row time series in the previous example and this pc_row time series is that the column names in this pc_row time series are the same as the column names in the ts time series.

The ProjectedClip function returns the following data in the pc_row time series:

tstamp               i01  i02   f02
2014-01-01 02:00:00  102  null  null
2014-01-01 03:00:00  103  null  null
2014-01-01 04:00:00  104  204   2.04
2014-01-01 05:00:00  105  null  null
2014-01-01 06:00:00  106  null  2.06

Example: Specify a low frequency column

The following query specifies to clip values from the ts time series, treat the i02 column as a low-frequency column, and return the results in the pc_row time series:

ProjectedClip(ts,
             '2014-01-01 02:00:00', 
             '2014-01-01 06:00:00',
              0,
             'i01,i02(lf),f02')::TIMESTAMP(pc_row)'

The ProjectedClip function returns the following data in the pc_row time series:

tstamp               i01  i02   f02
2014-01-01 02:00:00  102  200   null
2014-01-01 03:00:00  103  null  null
2014-01-01 04:00:00  104  204   2.04
2014-01-01 05:00:00  105  null  null
2014-01-01 06:00:00  106  null  2.06

The first returned value for the i02 column is 200. The actual value for the i02 column for the timestamp 2014-01-01 02:00:00 is NULL. The value 200 is the value for the i02 column for the time stamp 2014-01-01 00:00:00, which is the last non-null value.

Example: Specify no null columns

The following query specifies to clip values from the ts time series, treat the i02 and the f02 columns as a no null columns, and return the results in the pc_row time series:

ProjectedClip(ts,
             '2014-01-01 02:00:00', 
             '2014-01-01 06:00:00',
              0,
             'i01,i02(nn),f02(nn)')::TIMESTAMP(pc_row)'

The ProjectedClip function returns the following data in the pc_row time series:

tstamp               i01  i02   f02
2014-01-01 02:00:00  102  200   null
2014-01-01 03:00:00  103  200   null
2014-01-01 04:00:00  104  204   2.04
2014-01-01 05:00:00  105  204   2.04
2014-01-01 06:00:00  106  204   2.06

For the i02 column, the first two values are 200 instead of NULL and the last two values are 204 instead of NULL. For the f02 column, the first values remain NULL because no previous non-null value exists. The fourth value for the f02 column is 2.04 instead of NULL.