Example for hertz data: Create and load a hertz time series

This example shows how to create, load, and query a time series that stores hertz data.

About this task

In this example, you create a time series that contains hertz data that is recorded 50 times a second. You can create the time series table in any database that you choose. The following table lists the time series properties that are used in this example.

Table 1. Time series properties used in this example
Time series property	Definition
Element size	1 second
When elements are valid	Always
Data in the time series	The following data: Timestamp A SMALLINT value An INTEGER value A BIGINT value
Time series table	The following columns: An ID column of type INTEGER A TimeSeries data type column
Origin	2014-01-01 00:00:00.00000
Regularity	Irregular
Hertz	50 records per second
Metadata	No metadata
Where to store the data	In a container that you create
How to load the data	The InsElem function
How to access the data	A virtual table

Procedure

To create, load, and view a hertz time series:

Create a TimeSeries subtype that is named ts_data_h in a database by running the following SQL statement:
```
CREATE ROW TYPE ts_data_h(
      tstamp       datetime year to fraction(5),
      tssmallint   smallint,
      tsint        int,
      tsbigint     bigint
);
```
You can include only certain data types in your TimeSeries subtype.

Create a time series table that is named tstable_h by running the following SQL statement:

CREATE TABLE IF NOT EXISTS tstable_h(
   id  int not null primary key,
   ts  timeseries(ts_data_h)
) LOCK MODE ROW;

Create a container that is named container_h by running the following SQL statement:
```
EXECUTE PROCEDURE
   TSContainerCreate('container_h', 'rootdbs', 'ts_data_h', 512, 512);
```
You can choose to create the container in a different dbspace than the root dbspace.
Create a calendar by running the following SQL statement:
```
INSERT INTO CalendarTable(c_name, c_calendar)
   VALUES('ts_1sec',
      'startdate(2014-01-01 00:00:00.00000),
       pattern({1 on}, second)');
```
You cannot specify a subsecond interval for a calendar, however, the hertz time series definition overrides the calendar interval.

Create a hertz time series with a hertz value of 50 by running the following SQL statement in an explicit transaction:

BEGIN;
Started transaction.

INSERT INTO tstable_h VALUES(50,
   TSCreateIrr('ts_1sec', '2014-01-01 00:00:00.00000', 0, 50, 0, 'container_h')
);
1 row(s) inserted.

COMMIT;

The threshold and nelems parameters must be set to 0.

Insert five records for the same second into the time series by running the following SQL statements:

BEGIN;
Started transaction.

UPDATE tstable_h SET ts = InsElem(ts, row('2014-01-01 00:00:00.00000', 
                         1, 201, 99991)::ts_data_h) 
               WHERE id = 50;
1 row(s) updated.

UPDATE tstable_h SET ts = InsElem(ts, row('2014-01-01 00:00:00.02000', 
                         2, 202, 99992)::ts_data_h) 
               WHERE id = 50;
1 row(s) updated.


UPDATE tstable_h SET ts = InsElem(ts, row('2014-01-01 00:00:00.04000', 
                         3, 203, 99993)::ts_data_h) 
               WHERE id = 50;
1 row(s) updated.


UPDATE tstable_h SET ts = InsElem(ts, row('2014-01-01 00:00:00.06000', 
                         4, 204, 99994)::ts_data_h) 
               WHERE id = 50;
1 row(s) updated.


UPDATE tstable_h SET ts = InsElem(ts, row('2014-01-01 00:00:00.08000', 
                         5, 205, 99995)::ts_data_h) 
               WHERE id = 50;
1 row(s) updated.

COMMIT;
Data committed.

You must insert records in chronological order.

Create a virtual table that is named vt_tstable_h_h that is based on the hertz time series by running the following SQL statement:
```
EXECUTE PROCEDURE
   TSCreateVirtualTab('vt_tstable_h_h', 'tstable_h',  4096, 'ts');
```

Query the virtual table to view the hertz data by running the following SQL statement:

SELECT * FROM vt_tstable_h_h;


id          50
tstamp      2014-01-01 00:00:00.00000
tssmallint  1
tsint       201
tsbigint    99991

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

id          50
tstamp      2014-01-01 00:00:00.04000
tssmallint  3
tsint       203
tsbigint    99993

id          50
tstamp      2014-01-01 00:00:00.06000
tssmallint  4
tsint       204
tsbigint    99994

id          50
tstamp      2014-01-01 00:00:00.08000
tssmallint  5
tsint       205
tsbigint    99995

5 row(s) retrieved.