Time series input string

You can use the time series input string to create a time series with the INSERT statement. You can also use the time series input string in other routines, such as the TSCreateVirtualTab procedure and the TSL_SetNewTS function.

Use the following syntax for the time series input function.

Table 1. Time series input function elements
Element	Description
`calendar_name`	The name of the calendar.
`comp_definition`	A string that includes a compression definition for each column in the TimeSeries row type except the first column. For the syntax of the compression definition, see TSCreateIrr function. The irregular keyword must be included and the value of the `threshold` parameter must be 0.
`column_value`	A value of any column in the table except the TimeSeries column.
`container_name`	The name of an existing container.
`data_value`	The value of a column in a time series element, except the time stamp column.
`datafile_name`	The name of a file that contains time series data. For the format of the file, see BulkLoad function.
`hertz`	An integer 1-2047 or 100000 that specifies the number of records per second. The irregular keyword must be included and the value of the `threshold` parameter must be 0.
`metadata_type`	The user-defined metadata type. For more information, see Creating a time series with metadata.
`metadata_value`	The metadata values. Can be NULL. For more information, see Creating a time series with metadata.
`origin`	The origin of the time series. The default origin is the calendar start date. You can include the year formatting directives to set the origin to the current date. For example: `origin(%Y-%m-%d 00:00:00.00000)`. For more information, see The year formatting directives.
`table_name`	The name of the time series table.
`threshold`	The threshold for the time series, which is the maximum number of time series elements that are stored in-row. Extra elements are stored in containers. The default is `20`. The maximum size of a row that contains an in-row time series is approximately 1500 bytes. A value of 0 means that all time series elements are stored in containers. Important: Some parameters and flags that you can use when you create a time series require that the threshold value is set to 0.
`timestamp`	The time stamp of the element. The time stamp is optional for regular time series but mandatory for irregular time series.

All data types have an associated input function that is automatically run when ASCII data is inserted into the column. For the TimeSeries data type, the input has several pieces of data that is embedded in the text. This information is used to convey the name of the calendar, the time stamp of the origin, the threshold, the container, the regularity, and the initial time series data. A time series is regular by default; the regular keyword is optional. To define an irregular time series, you must include the irregular keyword.

If you did not specify a data file, then you can supply the data to be placed in the time series (the data element), surrounded by square brackets, after the parameters. Elements consist of data values, each separated by a comma. The data values in each element correspond to the columns in the TimeSeries subtype, not including the initial time stamp column. Each element is surrounded by parentheses and followed by an @ symbol and a time stamp. The time stamp is optional for regular time series but mandatory for irregular time series. Null data values or elements are indicated with the word NULL. If no data elements are present, the function creates an empty time series.

If you include the hertz or compress keywords, you must run the input function in an explicit transaction.

Example: Create a regular time series

Following example shows an INSERT statement for a regular time series that is created in the table daily_stocks:

insert into daily_stocks values (1234, 'informix',
         'regular, calendar(daycal), 
[(350, 310, 340, 1999), (362, 320, 350, 2500)]');

This INSERT statement creates a regular time series that starts at the date and time of day that is specified by the calendar called daycal. The first two elements in the time series are populated with the bracketed data. Since the threshold parameter is not specified, its default value is used. Therefore, if more than 20 elements are placed in the time series, the database server moves the data into a container. Because no container is specified, the default container is created.

Example: Create an irregular time series

The following example shows an INSERT statement for an irregular time series that is created in the table activity_stocks:

insert into activity_stocks values (
   600, 'irregular, container(ctnr_stock), origin(2005-10-06 00:00:00.00000),
calendar(daycal), [(6.25,1000,1,7,2,1)@2005-10-06 12:58:09.12345, (6.50, 2000,
1,8,3,1)@2005-10-06 12:58:09.23456]');

The INSERT statement creates an irregular time series that starts on 06 October 2005, at the time of day that is specified by the calendar called daycal. Two rows of data are inserted with the specified time stamps.

Example: Create a time series for hertz data

The following statement creates an empty irregular time series that stores hertz data with a frequency of 50 records per second:

BEGIN;
INSERT INTO tstable VALUES(0, 'origin(2013-01-01 00:00:00.00000), 
                           calendar(ts_1sec), container(container_2k), 
                           threshold(0), hertz(50), irregular, []')
COMMIT;

Example: Create a time series for compressed data

The following statement creates an empty irregular time series that compresses the time series records for a TimeSeries subtype that has six numeric columns in addition to the time stamp column:

BEGIN;
INSERT INTO tstable VALUES(0, "origin(2013-01-01 00:00:00.00000), 
              calendar(ts_1sec), container(container_4k), threshold(0), 
              compress(n(),q(1,1,100),ls(0.10), lb(0.10),qls(2,0.15,100,100000),
              qlb(2,0.25,100,100000)), irregular, []")
COMMIT;