Time series SQL routines sorted by task
Time series SQL routines are sorted into logical areas that are based on the type of task.
| Task type | Description |
|---|---|
| Get information from a time series | Get space usage and other information: TSInfo function Get the origin: GetOrigin function Get the interval: GetInterval function Get the calendar: GetCalendar function Get the calendar name: GetCalendarName function Get the container name: GetContainerName function Get user-defined metadata: GetMetaData function Get the metadata type: GetMetaTypeName function Determine whether a time series is regular: IsRegular function Get the instance ID if the time series is stored in a container: InstanceId function Determine whether a time series contains packed data: GetPacked function Get the frequency of hertz data: GetHertz function Get the compression type of compressed data: GetCompression function |
| Convert between a time stamp and an offset | Return the offset for the specified timestamp: GetIndex function Return the time stamp for the specified offset: GetStamp function |
| Count the number of elements | Return the number of elements: GetNelems function Get the number of elements between two time stamps: ClipGetCount function Get the number of elements that match the criteria of an arithmetic expression: CountIf function An extension of the CountIf function. Get a timeseries that describes the locations of the elements that match the criteria of an arithmetic expression: GetMatchingIf function An extension of the CountIf function. Get a timeseries with the same type as the input timeseries, of the elements that match the expression to be able to see the actual data at the matching time points: GetElementsIf function An extension of the CountIf function. Get a timeseries that has NULL value. With additional clauses in the expression, complete matching element set will be returned: GetNullElementsIf function |
| Select individual elements | Get the element associated with the specified time
stampGetElem function Get the element at or before a time stamp: GetLastValid function Get the element after a time stamp: GetNextValid function Get the element before a time stamp: GetPreviousValid function Get the element at a specified position: GetNthElem function Get the first element: GetFirstElem function Get the timestamp of the first element: GetFirstElementStamp function Get the last element: GetLastElem function Get the timestamp of the last element: GetLastElementStamp function Get the last non-null element: GetLastNonNull function Get the next non-null element: GetNextNonNull function |
| Modify elements or a set of elements | Add or update a single element: PutElem function Add or update a single element: PutElemNoDups function Add or update a single element at a specified offset (regular only): PutNthElem function Add or update an entire set: PutSet function Insert an element: InsElem function Insert a set: InsSet function Update an element: UpdElem function Update a set: UpdSet function Put every element of one time series into another time series: PutTimeSeries function |
| Delete elements | Delete an element at the specified timepoint: DelElem function Delete all elements in a time series instance for a specified time range: DelClip function Delete all elements and free space in a time series instance for a specified time range in any part of a time series: DelRange function Delete all elements and free space in a time series instance for a specified time range at the end of a time series: DelTrim function Free empty pages in a specified time range or throughout the time series instance: NullCleanup function Delete elements through a specified timestamp from one or more containers for one or more time series instances: TSContainerPurge function |
| Modify metadata | Update user-defined metadata: UpdMetaData function |
| Make elements visible or invisible to a scan | Make an element invisible HideElem function Make a range of elements invisible: HideRange function Make an element visible: RevealElem function Make a range of elements visible: RevealRange function |
| Check for null or hidden elements | Determine whether an element is hidden: ElemIsHidden function Determine whether an element is null: ElemIsNull function |
| Extract and use part of a time series | Extract a period between two time stamps or corresponding
to a set of values and run an expression or function on every entry: Apply function Clip data between two timepoints: Clip function Clip data from specific columns: ProjectedClip function Clip a specific number of elements: ClipCount function Output values in XML format: TSToXML function Extract a period that includes the specified time or starts or ends at the specified time: WithinC and WithinR functions |
| Apply a new calendar to a time series | Apply a calendar: ApplyCalendar function |
| Create and load a time series | Load data from a client file: BulkLoad function Create a regular empty time series, a regular populated time series, or a regular time series with metadata: TSCreate function Create an irregular empty time series, an irregular populated time series, or an irregular time series with metadata: TSCreateIrr function |
| Load time series data through a loader program | Initialize a loader session: TSL_Init function Open a database session: TSL_Attach function Specify the definition for new time series: TSL_SetNewTS function Load data into the database server: TSL_Put function Load JSON documents into the database server: TSL_PutJson function Load data from a table into the database server: TSL_PutSQL function Load a row of data into the database server: TSL_PutRow function Flush loaded data to disk in a single transaction: TSL_FlushAll function Flush loaded data to disk in multiple transactions: TSL_Commit function View information about the last data flush operation: TSL_FlushInfo function View handles for active loader sessions: TSL_ActiveHandles function Reset the logging mode: TSL_SetLogMode function Monitor loading and saving data: TSL_GetLogMessage function, TSL_GetFmtMessage function, and TSL_MessageSet function. Get the container name for a specific primary key: TSL_GetKeyContainer function Close a database session: TSL_SessionClose function Shut down the loader session: TSL_Shutdown procedure |
| Find the intersection or union of time series | Build the intersection of multiple time series
and optionally clip the result: Intersect function Build the union of multiple time series and optionally clip the result: Union function |
| Iterator functions | Convert time series data to tabular form: Transpose function |
| Aggregate functions | Return a list (collection of rows) containing all
elements in a time series: TSSetToList function Return a list of values from the specified column name in the time series: TSColNameToList function Return a list of values from the specified column number in the time series: TSColNumToList function Return a list of values that contains the columns of the time series plus non-time-series columns: TSRowToList function Return a list of values from the specified column name of the time series plus non-time-series columns: TSRowNameToList function Return a list of values from the specified column number of the time series plus non-time-series columns: TSRowNumToList function |
| Used within the Apply function to perform statistical calculations on a time series | Sum SMALLFLOAT or DOUBLE PRECISION values: TSAddPrevious function Compute the decay function: TSDecay function Compute a running average over a specified number of values: TSRunningAvg function Compute a running correlation between two time series over a specified number of values: TSRunningCor function Compute a running median over a specified number of values: TSRunningMed function Compute a running sum over a specified number of values: TSRunningSum function Compute a running variance over a specified number of values: TSRunningVar function Compare SMALLFLOAT or DOUBLE PRECISION values: TSCmp function Return a previously saved value: TSPrevious function |
| Perform an arithmetic operation on one or two time series | Add two time series together: Plus function Subtract one time series from another: Minus function Multiply one time series by another: Times function Divide one time series by another: Divide function Raise the first argument to the power of the second: Pow function Get the absolute value: Abs function Exponentiate the time series: Exp function Get the natural logarithm of a time series: Logn function Get the modulus or remainder of a division of one time series by another: Mod function Negate a time series: Negate function Return the argument and the argument is bound to the unary + operator: ids_tms_199.html Round the time series to the nearest whole number: Round function Get the square root of the time series: Sqrt function Get the cosine of the time series: Cos function Get the sine of the time series: Sin function Get the tangent of the time series: Tan function Get the arc cosine of the time series: Acos function Get the arc sine of the time series: Asin function Get the arc tangent of the time series: Atan function Get the arc tangent for two time series: Atan2 function |
| Apply an arithmetic operation on one or more time series | Apply a binary function to a pair of time series,
or to a time series and a compatible row type or number: ApplyBinaryTsOp function Apply a unary function to a time series: ApplyUnaryTsOp function Apply another function to a set of time series: ApplyOpToTsSet function |
| Aggregate time series values | Aggregate values in a time series from a single
row: AggregateBy function Aggregate values in a time series from a single row over a specified time range: AggregateRange function Aggregate time series values across multiple rows: TSRollup function |
| Create a time series that lags | Create a time series that lags the source time series by a specified offset (regular only): Lag function |
| Reset the origin | Reset the origin: SetOrigin function |
| Manage containers | Create a container: TSContainerCreate procedure Delete a container: TSContainerDestroy procedure Set the container name: SetContainerName function Specify the container pool for inserting data into a time series: TSContainerPoolRoundRobin function Add a container into a container pool or remove a container from a container pool: TSContainerSetPool procedure Delete elements through a specified timestamp from one or more containers: TSContainerPurge function Control whether multiple sessions write to the container at the same time: TSContainerLock procedure Change the properties of rolling windows containers: TSContainerManage function Change the extent sizes of containers: TSContainerManage function |
| Monitor containers | Return the number
of elements in one or all containers: TSContainerNElems function Return the percentage of space that is used in one or all containers: TSContainerPctUsed function Return the total number of pages that are allocated to one or all containers: TSContainerTotalPages function Return the number of pages that are used by one or all containers: TSContainerTotalUsed function Return the number of elements, the number of pages that are used, and the total number of pages that are allocated for one or all containers: TSContainerUsage function |