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:
  1. 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.
  2. 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;
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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');
  8. 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.