GetClosestElem function

The GetClosestElem function returns the first element that is non-null and closest to the given time stamp. Optionally, you can specify which column within the time series element must be non-null to satisfy the search.

Syntax

GetClosestElem(ts         TimeSeries, 
              tstamp       datetime year to fraction(5),
              cmp          lvarchar, 
              column_list  lvarchar default NULL,
              flags        integer default 0)
returns ROW

ts

The time series to act on.

tstamp

The time stamp to start searching from.

cmp

A comparison operator used with tstamp to determine where to start the search. Valid values for cmp are <, <=, =, ==, >=, and >.

column_list

To search for an element with one or more columns non-null, specify a list of column names separated by a vertical bar (|). An error is raised if any of the column names does not exist in the time series type

To search for a null element, set column_list to NULL.

flags

Determines whether hidden elements should be returned. Valid for the flags parameter values are defined in tseries.h. They are:

TS_CLOSEST_NO_FLAGS (no special flags)
TS_CLOSEST_RETNULLS_FLAGS (return hidden elements)

Description

The search algorithm ts_closest_elem is as follows:

If cmp is any of : <=, =, ==, or >=, the search starts at tstamp.
If cmp is <, the search starts at the first element before tstamp.
If cmp is >, the search starts at the first element after tstamp.

The tstamp and cmp parameters are used to determine where to start the search. The search continues in the direction indicated by cmp until an element is found that qualifies. If no element qualifies, the return value is NULL.

Important: For irregular time series, values in an irregular element persist until the next element. This means that any of the “equals” operations on an irregular time series look for <= first. If cmp is >= and the <= operation fails, the operation then looks forward for the next element; otherwise, NULL is returned.

Returns

An element meeting the described criteria that is non-null and closest to the given time stamp.