Manage packed data

You can insert, delete, and select packed data.

You must follow these rules when you insert or delete packed data:

  • For compressed data, records must be inserted in chronological order. Existing records cannot be replaced or updated. Missing records are allowed but cannot be updated.
  • For hertz data, subsecond elements within a second must be entered in chronological order. Whole-second blocks of elements can be inserted out of chronological order.
  • Functions that insert or delete compressed data must be run in an explicit transaction.

You can determine whether a time series contains packed data by running the GetPacked function. You can determine the frequency of hertz data by running the GetHertz function. You can determine the compression definition of compressed data by running the GetCompression function.

Insert packed data

You can insert data into an existing hertz or compressed time series by running the following functions:

  • InsElem
  • InsSet
  • TSL_Put
  • TSL_PutRow
  • TSL_PutSQL

You can also insert data through a virtual table.

The data is saved to disk when an element reaches maximum size or the transaction is committed.

Delete packed data

You can delete elements of hertz data by running the following functions:

  • DelClip
  • DelItem
  • DelRange
  • DelTrim

You cannot delete individual records from an element. Regardless of the time range you specify, whole elements are deleted.

You cannot delete elements from a compressed time series. To delete compressed data, you must delete the time series instance. For example, you can delete a row in the table that contains packed data.

Select packed data

You can run any time series function that selects data on packed data. You do not need to know that the data is packed. When packed data is returned by a time series function, every packed record appears as an individual element.

The following example shows a returned value from a hertz time series:

SELECT  GetElem(ts_2k, '2014-01-01 00:00:02.50000') FROM tstable50

(expression)  ROW('2014-01-01 00:00:02.50000',2     ,50         ,5050

Similarly, when you create a virtual table that is based on packed data, each packed record is a row in the virtual table. The following example shows four values in a virtual table that are from the same element in a hertz time series:

SELECT * FROM vt_tstable_2k WHERE tstamp < '2014-01-01 00:00:00.08000'

id          50
tstamp      2014-01-01 00:00:00.00000
tssmallint  2
tsint       50
tsbigint    5050

id          50
tstamp      2014-01-01 00:00:00.02000
tssmallint  2
tsint       50
tsbigint    5050

id          50
tstamp      2014-01-01 00:00:00.04000
tssmallint  2
tsint       50
tsbigint    5050

id          50
tstamp      2014-01-01 00:00:00.06000
tssmallint  2
tsint       50
tsbigint    5050

4 row(s) retrieved.