Home
Extending Informix®
Beyond standard relational database objects, HCL Informix® can be extended to handle specialized data types, access methods, routines, and other objects. Informix includes many built-in extensions that are fully integrated in the database server. Informix also provides DataBlade® modules, which are packages of extended database objects for a particular purpose and that are installed separately from the database server. Alternatively, you can create your own user-defined objects for Informix.
Informix® extensions and DataBlade® modules
These topics describe how to use built-in database extensions and separately installed DataBlade® modules.
TimeSeries Data User's Guide
The Informix® TimeSeries Data User's Guide contains information to assist you in using the TimeSeries extension with HCL Informix.
Time series SQL routines
GetLastValid function
The GetLastValid function extracts the element for the given time stamp in a time series.

Extending Informix®
Beyond standard relational database objects, HCL Informix® can be extended to handle specialized data types, access methods, routines, and other objects. Informix includes many built-in extensions that are fully integrated in the database server. Informix also provides DataBlade® modules, which are packages of extended database objects for a particular purpose and that are installed separately from the database server. Alternatively, you can create your own user-defined objects for Informix.
- Informix® extensions and DataBlade® modules
  These topics describe how to use built-in database extensions and separately installed DataBlade® modules.
  - Database Extensions User's Guide
    The Informix® Database Extensions User's Guide explains how to use the database extensions that come with HCL Informix: Large Object Locator, MQ messaging, binary data types, basic text search, node data type, and SQL packages.
  - Performing XML Publishing
    The Informix® XML User's Guide includes information about using built-in functions for XML publishing with Informix.
  - Spatiotemporal Search for Moving Objects User's Guide
    The describes how to program applications to search data from moving objects in HCL Informix® databases using the spatiotemporal search extension.
  - TimeSeries Data User's Guide
    The Informix® TimeSeries Data User's Guide contains information to assist you in using the TimeSeries extension with HCL Informix.
    - Informix® TimeSeries solution
      Database administrators and applications developers use the Informix® TimeSeries solution to store and analyze time series data.
    - Data types and system tables
      Specialized data types and system tables handle time series data.
    - Create and manage a time series through SQL
      Before you can load time series data into the database, you must configure database objects specific to your time series. You can manage data storage and remove data as necessary. You can run SQL statements to create and manage time series.
    - Virtual tables for time series data
      A virtual table provides a relational view of your time series data.
    - Pattern matching searches
      You can search time series data for matches to a particular pattern of values. For example, after you identify a pattern of abnormal electricity usage that indicates an outage, you can search for matches to that pattern to find other outages. Pattern matching searches find matches to a pattern that you supply. Pattern matching searches do not discover patterns in time series data.
    - Advanced analytics
      Advanced analytics functions provide specialized methods of analyzing time series data for patterns or abnormalities.
    - Calendar pattern routines
      You can use calendar pattern routines to manipulate calendar patterns.
    - Calendar routines
      You can use calendar routines to manipulate calendars.
    - Time series SQL routines
      - Time series SQL routines sorted by task
        Time series SQL routines are sorted into logical areas that are based on the type of task.
      - Time series routines that run in parallel
        Some time series routines can run in parallel. Running in parallel is faster than running serially.
      - The flags argument values
        The time series SQL functions that insert data have a flags argument to determine how elements are inserted.
      - Abs function
        The Abs function returns the absolute value of its argument.
      - Acos function
        The Acos function returns the arc cosine of its argument.
      - AggregateBy function
        The AggregateBy function aggregates the values in a time series using a new time interval that you specify by providing a calendar.
      - AggregateRange function
        The AggregateRange function produces an aggregate over each element for a time range that is specified by start and end DATETIME parameters.
      - Apply function
        The Apply function queries one or more time series and applies a user-specified SQL expression or function to the selected time series elements.
      - ApplyBinaryTsOp function
        The ApplyBinaryTsOp function applies a binary arithmetic function to a pair of time series or to a time series and a compatible row type or number.
      - ApplyCalendar function
        The ApplyCalendar function applies a new calendar to a time series.
      - ApplyOpToTsSet function
        The ApplyOpToTsSet function applies a binary arithmetic function to a set of time series.
      - ApplyUnaryTsOp function
        The ApplyUnaryTsOp function applies a unary arithmetic function to a time series.
      - Asin function
        The Asin function returns the arc sine of its argument.
      - Atan function
        The Atan function returns the arc tangent of its argument.
      - Atan2 function
        The Atan2 function returns the arc tangent of corresponding elements from two time series.
      - Binary arithmetic functions
        The standard binary arithmetic functions Atan2, Plus, Minus, Times, Divide, Mod, and Pow can operate on time series data. The Plus, Minus, Times, and Divide functions can also be denoted by their standard operators +, -, *, and /.
      - BulkLoad function
        The BulkLoad function loads data from a client file into an existing time series.
      - 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.
      - ClipCount function
        The ClipCount function is a variation of Clip in which the first integer argument is interpreted as a count of entries to clip. If the count is positive, ClipCount begins with the first element at or after the time stamp and clips the next count entries. If the count is negative, ClipCount begins with the first element at or before the time stamp and clips the previous count entries.
      - ClipGetCount function
        The ClipGetCount function returns the number of elements in the current time series that occur in the period delimited by the time stamps.
      - Cos function
        The Cos function returns the cosine of its argument.
      - CountIf function
        The CountIf function counts the number of elements that match the criteria of a simple arithmetic expression.
      - DelClip function
        The DelClip function deletes all elements in the specified time range, including the delimiting timepoints, for the specified time series instance. The DelClip function differs from the DelTrim function in its handling of deletions from the end of a regular time series. DelTrim shortens the time series and reclaims space, whereas DelClip replaces elements with null values.
      - DelElem function
        The DelElem function deletes the element at the specified timepoint in the specified time series instance.
      - DelRange function
        The DelRange function deletes all elements in the specified time range in the specified time series instance, including the delimiting timepoints. The DelRange function is similar to the DelTrim function except that the DelRange function deletes elements and reclaims space from any part of a regular time series.
      - DelTrim function
        The DelTrim function deletes all elements in the specified time range in a time series instance, including the delimiting timepoints. The DelTrim function is similar to the DelClip function except that the DelTrim function deletes elements and reclaims space from the end of a regular time series instance, whereas the DelClip function replaces elements with null values. The DelTrim function is also similar to the DelRange function except that the DelRange function deletes elements and reclaims space from any part of a regular time series instance.
      - Divide function
        The Divide function divides one time series by another.
      - ElemIsHidden function
        The ElemIsHidden function determines if an element is hidden.
      - ElemIsNull function
        The ElemIsNull function determines if an element contains no data.
      - Exp function
        The Exp function exponentiates the time series.
      - FindHidden function
        The FindHidden function scans a time series and returns all elements that are hidden.
      - GetCalendar function
        The GetCalendar function returns the calendar associated with the given time series.
      - GetCalendarName function
        The GetCalendarName function returns the name of the calendar used by the given time series.
      - GetClosestElem function
      - GetCompression function
        The GetCompression function returns the compression string if the time series data is compressed.
      - GetContainerName function
        The GetContainerName function returns the name of the container for the given time series.
      - GetElem function
        The GetElem function extracts the element for the given time stamp.
      - GetElementsIf function
        The GetElementsIf function shows the actual data that match the criteria of a simple arithmetic expression.
      - GetFirstElem function
        The GetFirstElem function returns the first element in a time series.
      - GetFirstElementStamp function
        The GetFirstElementStamp function returns the timestamp of the first element in the time series.
      - GetHertz function
        The GetHertz function returns the frequency for packed hertz data.
      - GetIndex function
        The GetIndex function returns the index (offset) of the time series entry associated with the supplied time stamp.
      - GetInterval function
        The GetInterval function returns the interval used by a time series.
      - GetLastElem function
        The GetLastElem function returns the final entry stored in a time series.
      - GetLastElementStamp function
        The GetLastElementStamp function returns the timestamp of the last element in the time series.
      - GetLastNonNull function
        The GetLastNonNull function returns the last non-null element on or before the date you specify.
      - GetLastValid function
        The GetLastValid function extracts the element for the given time stamp in a time series.
      - GetMatchingIf function
        The GetMatchingIf function returns the timeseries that describes the locations of the elements that match the criteria of a simple arithmetic expression.
      - GetMetaData function
        The GetMetaData function returns the user-defined metadata from the given time series.
      - GetMetaTypeName function
        The GetMetaTypeName function returns the type name of the user-defined metadata type stored in the given time series.
      - GetNelems function
        The GetNelems function returns the number of elements stored in a time series.
      - GetNextNonNull function
        The GetNextNonNull function returns the next non-null element on or after the date you specify.
      - GetNextValid function
        The GetNextValid function returns the nearest entry after a given time stamp.
      - GetNthElem function
        The GetNthElem function extracts the entry at a particular offset or position in a time series.
      - GetNullElementsIf function
        The GetNullElementsIf function returns a timeseries that includes information on where NULL elements existed in regular timeseries.
      - GetOrigin function
        The GetOrigin function returns the origin of the time series.
      - GetPacked function
        The GetPacked function returns whether the specified time series contains packed data.
      - GetPreviousValid function
        The GetPreviousValid function returns the last element before the given time stamp.
      - GetStamp function
        The GetStamp function returns the time stamp associated with the supplied offset in a time series. Offsets can be positive or negative integers.
      - GetThreshold function
        The GetThreshold function returns the threshold associated with the specified time series.
      - HideElem function
        The HideElem function marks an element, or a set of elements, at a given time stamp as invisible.
      - HideRange function
        The HideRange function marks as invisible a range of elements between a starting time stamp and an ending time stamp.
      - InsElem function
        The InsElem function inserts an element into a time series.
      - InsSet function
        The InsSet function inserts every element of a specified set into a time series.
      - InstanceId function
        The InstanceId function determines if the time series is stored in a container and, if it is, returns the instance ID of that time series.
      - Intersect function
        The Intersect function performs an intersection of the specified time series over the entire length of each time series or over a clipped portion of each time series.
      - IsRegular function
        The IsRegular function tells whether a specified time series is regular.
      - Lag function
        The Lag function creates a new regular time series in which the data values lag the source time series by a fixed offset.
      - Logn function
        The Logn function returns the natural logarithm of a time series.
      - Minus function
        The Minus function subtracts one time series from another.
      - Mod function
        The Mod function computes the modulus or remainder of a division of one time series by another.
      - Negate function
        The Negate function negates a time series.
      - NullCleanup function
        The NullCleanup function frees any pages in a time series instance that contain only null elements in a range or for the whole time series instance.
      - Plus function
        The Plus function adds two time series together.
      - Positive function
      - Pow function
        The Pow function raises the first argument to the power of the second.
      - 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.
      - PutElem function
        The PutElem function adds an element to a time series at the timepoint indicated in the supplied row type.
      - PutElemNoDups function
        The PutElemNoDups function inserts a single element into a time series. If there is already an element at the specified timepoint, it is replaced by the new element.
      - PutNthElem function
        The PutNthElem function puts the supplied row at the supplied offset in a regular time series.
      - PutSet function
        The PutSet function updates a time series with the supplied multiset of row type values.
      - PutTimeSeries function
        The PutTimeSeries function puts every element of the first time series into the second time series.
      - RevealElem function
        The RevealElem function makes an element at a specified time stamp available for a scan. It reverses the effect of HideElem.
      - RevealRange function
        The RevealRange function makes hidden elements in a specified date range visible. It reverses the effect of HideRange.
      - Round function
        The Round function rounds a time series to the nearest whole number.
      - SetContainerName function
        The SetContainerName function sets the container name for a time series, even if the time series already has a container name.
      - SetOrigin function
        The SetOrigin function moves the origin of a time series back in time.
      - Sin function
        The Sin function returns the sine of its argument.
      - Sqrt function
        The Sqrt function returns the square root of its argument.
      - Tan function
        The Tan function returns the tangent of its argument.
      - Times function
        The Times function multiplies one time series by another.
      - TimeSeriesRelease function
        The TimeSeriesRelease function returns an LVARCHAR string containing theTimeSeries extension version number and build date.
      - Transpose function
        The Transpose function converts time series data for processing in a tabular format.
      - TSAddPrevious function
        The TSAddPrevious function sums all the values it is called with and returns the current sum every time it is called. The current argument is not included in the sum.
      - TSCmp function
        The TSCmp function compares two values.
      - TSColNameToList function
        The TSColNameToList function takes a TimeSeries column and returns a list (collection of rows) containing the values of one of the columns in the elements of the time series. Null elements are not added to the list.
      - TSColNumToList function
        The TSColNumToList function takes a TimeSeries column and returns a list (collection of rows) containing the values of one of the columns in the elements of the time series. Null elements are not added to the list.
      - TSContainerCreate procedure
        The TSContainerCreate procedure creates a container to store the time series data for the specified TimeSeries subtype. You can create a container in one dbspace, a container that spans multiple partitions, or a rolling window container, which controls the amount of data that is stored.
      - TSContainerDestroy procedure
        The TSContainerDestroy procedure deletes the container row from the TSContainerTable table and removes the container and its corresponding system catalog rows.
      - TSContainerLock procedure
        The TSContainerLock procedure controls whether multiple sessions can write to a container at one time.
      - TSContainerManage function
        The TSContainerManage function changes the properties of containers.
      - TSContainerNElems function
        The TSContainerNElems function returns the number of time series data elements stored in the specified container or in all containers.
      - TSContainerPctUsed function
        The TSContainerPctUsed function returns the percentage of space that is used in the specified container or in all containers.
      - TSContainerPoolRoundRobin function
        The TSContainerPoolRoundRobin function provides a round-robin policy for inserting time series data into containers in the specified container pool.
      - TSContainerPurge function
        The TSContainerPurge function deletes time series data through a specified timestamp from one or more containers.
      - TSContainerSetPool procedure
        The TSContainerSetPool procedure moves the specified container into the specified container pool.
      - TSContainerTotalPages function
        The TSContainerTotalPages function returns the total number of pages that are allocated to the specified container or in all containers.
      - TSContainerTotalUsed function
        The TSContainerTotalUsed function returns the total number of pages that contain time series data in the specified container or in all containers.
      - TSContainerUsage function
        The TSContainerUsage function returns information about the size and capacity of the specified container or of all containers.
      - TSCreate function
        The TSCreate function creates an empty regular time series or a regular time series populated with the given set of data. The new time series can also have user-defined metadata attached to it.
      - TSCreateIrr function
        The TSCreateIrr function creates an empty irregular time series or an irregular time series that is populated with the specified multiset of data. The new time series can also have user-defined metadata that is attached to it.
      - TSDecay function
        The TSDecay function computes a decay function over its arguments.
      - TSInfo function
        The TSInfo function returns information about a time series and the storage that is used by the time series.
      - TSL_ActiveHandles function
        The TSL_ActiveHandles function returns the handles for active loader sessions.
      - TSL_Attach function
        The TSL_Attach function opens a database session for loading data.
      - TSL_Commit function
        The TSL_Commit function flushes data for all containers to disk in multiple transactions.
      - TSL_Flush function
        The TSL_Flush function flushes data for one or all containers to disk in a single transaction.
      - TSL_FlushAll function
        The TSL_FlushAll function flushes data for all containers to disk in a single transaction.
      - TSL_FlushInfo function
        The TSL_FlushInfo function returns information about the last flush operation that saved data to disk.
      - TSL_FlushStatus function
        The TSL_FlushStatus function presents the return value of the TSL_Flush function in a format that is easier to read.
      - TSL_GetFmtMessage function
        The TSL_GetFmtMessage function retrieves the specified number of messages from the loader message queue. The formatted messages include message text in English.
      - TSL_GetKeyContainer function
        The TSL_GetKeyContainer function returns the name of the container that stores the time series values for the specified primary key value.
      - TSL_GetLogMessage function
        The TSL_GetLogMessage function retrieves the specified number of messages from the loader message queue. The messages do not include message text.
      - TSL_Init function
        The TSL_Init function initializes a session for loading data.
      - TSL_MessageSet function
        The TSL_MessageSet function retrieves loader message text.
      - TSL_Put function
        The TSL_Put function loads time series data.
      - TSL_PutJson function
        The TSL_PutJson function loads JSON documents as time series data.
      - TSL_PutRow function
        The TSL_PutRow function loads a row of time series data.
      - TSL_PutSQL function
        The TSL_PutSQL function loads time series data from a table.
      - TSL_SessionClose function
        The TSL_SessionClose function closes the current database session.
      - TSL_SetLogMode function
        The TSL_SetLogMode function specifies how messages about the loader are logged.
      - TSL_SetNewTS function
        The TSL_SetNewTS function controls the creation of new time series by a loader program.
      - TSL_Shutdown procedure
        The TSL_Shutdown procedure shuts down the loader application and releases all resources.
      - TSPrevious function
        The TSPrevious function records the supplied argument and returns the last argument it was passed.
      - TSRollup function
        The TSRollup function aggregates time series values by time for multiple rows in the table.
      - TSRowNameToList function
        The TSRowNameToList function returns a list (collection of rows) containing one individual column from a time series column plus the non-time-series columns of a table. Null elements are not added to the list.
      - TSRowNumToList function
        The TSRowNumToList function returns a list (collection of rows) containing one individual column from a time series column plus the non-time-series columns of a table. Null elements are not added to the list.
      - TSRowToList function
        The TSRowToList function returns a list (collection of rows) containing the individual columns from a time series column plus the non-time-series columns of a table. Null elements are not added to the list.
      - TSRunningAvg function
        The TSRunningAvg function computes a running average over SMALLFLOAT or DOUBLE PRECISION values.
      - TSRunningCor function
        The TSRunningCor function computes the running correlation of two time series over a running window. The TSRunningCor function returns NULL if the variance of either input is zero or NULL over the window.
      - TSRunningMed function
        The TSRunningMed function computes the median of a time series over a running window. This function is useful only when used within the Apply function.
      - TSRunningSum function
      - TSRunningVar function
        The TSRunningVar function computes the variance of a time series over a running window.
      - TSSetToList function
        The TSSetToList function takes a TimeSeries column and returns a list (collection of rows) containing all the elements in the time series. Null elements are not added to the list.
      - TSToXML function
        The TSToXML function returns an XML representation of a time series.
      - Unary arithmetic functions
        The standard unary functions Abs, Acos, Asin, Atan, Cos, Exp, Logn, Negate, Positive, Round, Sin, Sqrt, and Tan are extended to operate on time series.
      - Union function
        The Union function performs a union of multiple time series, either over the entire length of each time series or over a clipped portion of each time series.
      - UpdElem function
        The UpdElem function updates an existing element in a time series.
      - UpdMetaData function
        The UpdMetaData function updates the user-defined metadata in the specified time series.
      - UpdSet function
        The UpdSet function updates a set of existing elements in a time series.
      - WithinC and WithinR functions
        The WithinC and WithinR functions perform calendar-based queries, converting among time units and doing the calendar math to extract periods of interest from a time series value.
    - Time series Java™ class library
      You can use the time series Java™ class library to create and manage a time series from within Java applications or applets.
    - Time series API routines
      The time series application programming interface routines allow application programmers to directly access a time series datum.
    - Appendixes
  - Web DataBlade Module
    Use the Web DataBlade® module to create web applications that incorporate data that is retrieved dynamically from the HCL Informix® database.
- Creating extensions
  You can create user-defined data types, routines, access methods, and other database objects to suit your needs. You can use application programming interfaces to write user-defined routines and applications that access data in Informix® databases.

GetLastValid function

The GetLastValid function extracts the element for the given time stamp in a time series.

Syntax

GetLastValid(ts     TimeSeries, 
            tstamp datetime year to fraction(5),
            flags integer default 0) 
returns row;

ts: The source time series.
tstamp: The time stamp for the element.
flags: Valid values for the flags argument are described in The flags argument values. The default is 0.

Description

For regular time series, this function returns the element at the calendar's latest valid timepoint at or before the given time stamp. For irregular time series, it returns the latest element at or preceding the given time stamp.

The equivalent API function is ts_last_valid().

Returns

A row type containing the nearest element at or before the given time stamp. The type of the row is the same as the time series subtype.

If the time stamp is earlier than the origin of the time series, NULL is returned.

Example

The following query returns the last valid entry in a time series at or before a given time stamp:

select GetLastValid(stock_data, '2011-01-08 00:00:00.00000')
from daily_stocks
where stock_name = 'IBM';