- 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.