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.

Syntax

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        threshold       integer, 
        hertz           integer, 
        nelems          integer, 
        container_name  lvarchar) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        threshold       integer, 
        hertz           integer, 
        container_name  lvarchar, 
        multiset_rows   multiset) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        threshold       integer, 
        hertz           integer, 
        container_name  lvarchar, 
        metadata        TimeSeriesMeta) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        threshold       integer, 
        hertz           integer, 
        container_name  lvarchar, 
        metadata        TimeSeriesMeta, 
        multiset_rows   multiset) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        container_name  lvarchar,
        compression     lvarchar) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        container_name  lvarchar, 
        compression     lvarchar,
        multiset_rows   multiset) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        container_name  lvarchar, 
        compression     lvarchar,
        metadata        TimeSeriesMeta
) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin          datetime year to fraction(5), 
        container_name  lvarchar, 
        compression     lvarchar,
        metadata        TimeSeriesMeta, 
        multiset_rows   multiset
) 
returns TimeSeries with (handlesnulls);
TSCreateIrr(cal_name    lvarchar, 
        origin         datetime year to fraction(5), 
        threshold      integer, 
        zero           integer, 
        nelems         integer, 
        container_name lvarchar) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin         datetime year to fraction(5), 
        threshold      integer, 
        zero           integer, 
        container_name lvarchar, 
        multiset_rows   multiset) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin         datetime year to fraction(5), 
        threshold      integer, 
        zero           integer, 
        nelems         integer, 
        container_name lvarchar, 
        metadata       TimeSeriesMeta) 
returns TimeSeries with (handlesnulls);

TSCreateIrr(cal_name    lvarchar, 
        origin         datetime year to fraction(5), 
        threshold      integer, 
        zero           integer, 
        container_name lvarchar, 
        metadata       TimeSeriesMeta, 
        multiset_rows   multiset) 
returns TimeSeries with (handlesnulls);
cal_name
The name of the calendar for the time series.
origin
The origin of the time series, which is the first valid date from the calendar for which data can be stored in the series.
threshold
The threshold for the time series. If the time series stores more than this number of elements, it is converted to a container. Otherwise, it is stored directly in the row that contains it. The default is 20. The size of a row that contains an in-row time series cannot exceed 1500 bytes.

If a time series has too many bytes to fit in a row before this threshold is reached, the time series is put into a container.

zero
Must be 0.
hertz (Optional)
An integer that specifies whether the times series stores hertz data:
0 = The time series does not contain hertz data.
1 - 2047 or 100000 = The number of records per second.
If you set the hertz parameter to a value other than 0, the values of the threshold parameter must be 0.
nelems (Optional)
The number of elements that are allocated for the resultant time series. If the number of elements exceeds this value, the time series is expanded through reallocation.
container_name
The name of the container that is used to store the time series. Can be NULL.
compression (Optional)
Figure 1: Syntax of the compression parameter

1+ ,
1  n ( )
1  q ( compress_size , lower_bound , upper_bound )
1 
2.1 lb
2.1 ls
1 (
1 max_deviation
1 )
1 
2.1 qlb
2.1 qls
1 (
1 compress_size
1 ,
1 max_deviation
1 ,
1 lower_bound
1 ,
1 upper_bound
1 )
A string that includes a compression definition for each column in the TimeSeries subtype except the first column. List the compression definition for each field in the order of the fields in the subtype, which are separated by commas. The compression definition consists of the compression type and the corresponding compression attributes:
Compression types:
  • n = None. The column is not compressed.
  • q = Quantization
  • lb = Linear boxcar
  • ls = Linear swing door
  • qlb = Quantization linear boxcar
  • qls = Quantization linear swing door
Compression attributes:
  • compress_size = The number of bytes to store: 1, 2, or 4. The value must be smaller than the size of the associated column.
  • lower_bound = A number that represents the lower boundary of acceptable values. The range of values is the same as the data type of the associated column.
  • upper_bound = A number that represents the upper boundary of acceptable values. The range of values is the same as the data type of the associated column.
  • max_deviation = A positive floating point number that represents the maximum deviation between the actual value and the compressed value. The range of values is 0 through the largest value of the data type of the associated column. The value 0 indicates that no deviation is allowed.
See Compressed numeric time series.
metadata (Optional)
The user-defined metadata to be put into the time series.
multiset_rows (Optional)
A multiset of rows to populate the time series. The type of these rows must be the same as the subtype of the time series.
Cannot be combined with the zero parameter.

Description

If the TSCreateIrr function is called with the metadata parameter, then metadata is saved in the time series.

If you include the compression parameter, each time series element is packed with compressed records until the size of the element approaches 4 KB or the transaction is committed. You must run the TSCreateIrr function within an explicit transaction.

If you include the hertz parameter, each time series element is packed with the number of records that are specified by the hertz parameter. An element is saved to disk after a record for the last subsecond boundary is inserted or the transaction is committed. You must run the TSCreateIrr function within an explicit transaction.

Returns

An irregular, hertz, or compressed time series.

An irregular time series.

Example: Create an empty time series

The following example creates an empty irregular time series:
select TSCreateIrr('daycal',
      '2011-01-05 00:00:00.00000',
      20,
      0,
      NULL,
      set_data)::TimeSeries(stock_trade)
    from activity_load_tab
    where stock_id = 600;

Example: Create a populated time series

The following example creates a populated irregular time series:
insert into activity_stocks 
    select 1234,
      TSCreateIrr('daycal',
         '2011-01-03 00:00:00.00000'::datetime year to fraction(5),
         20, 0, NULL,
         set_data)::timeseries(stock_trade)
    from activity_load_tab;

Example: Create a compressed time series

When you create a compressed time series, you need to know the structure of the TimeSeries subtype and characteristics about the data in each column. The following statement creates a TimeSeries row type that has a timestamp column and six other columns of numeric data:

CREATE ROW TYPE irregular_t
(
tstamp DATETIME YEAR TO FRACTION(5),
key1 smallint,
key2 int,
key3 bigint,
key4 smallfloat,
key5 float,
key6 int
);

The following statement creates a compressed time series instance in the table tstable:

BEGIN;
INSERT INTO tstable VALUES(1,
   TSCreateIrr('ts_1sec', '2013-01-01 00:00:00.00000', 
               'container_2k', 'n(),q(1,1,100),ls(0.10), 
               lb(0.10),qls(2,0.15,100,100000),qlb(2,0.25,100,100000)'))
COMMIT;

The columns in the irregular_t row type are compressed in the following ways:

  • The key1 column is not compressed.
  • The key2 column is compressed by the quantization compression type with a compression size of 1 byte, a lower bound of 1 and an upper bound of 100.
  • The key3 column is compressed by the linear swing door compression type with a maximum deviation of 0.10.
  • The key4 column is compressed by the linear boxcar compression type with a maximum deviation of 0.10.
  • The key5 column is compressed by the quantization linear swing door compression type with a compression size of 2 bytes, a maximum deviation of 0.15, a lower bound of 100, and an upper bound of 100000.
  • The key6 column is compressed by quantization linear boxcar with a compression size of 2 bytes, a maximum deviation of 0.25, a lower bound of 100, and an upper bound of 100000.

Example: Create a hertz time series

The following statement creates a time series that stores 50 records per second in each element:

BEGIN;
INSERT INTO tstable VALUES(1,
            TSCreateIrr('ts_1sec', '2014-01-01 00:00:00.00000', 
                        0, 50, 0, 'container1'))
COMMIT;