Example for regular data: Create a regular time series

This example shows how to create a TimeSeries data type, create a time series table, and create a regular time series by running the TSCreate function.

Before you begin

Prerequisites:

The stores_demo database must exist. You create the stores_demo database by running the dbaccessdemo command.

About this task

In this example, you create a time series that contains electricity meter readings. Readings are taken every 15 minutes. The table and TimeSeries data type you create are similar to the examples in the ts_data table in the stores_demo database. The following table lists the time series properties used in this example.

Table 1. Time series properties used in this example
Time series property	Definition
Timepoint size	15 minutes
When timepoints are valid	Every 15 minutes with no invalid times
Data in the time series	The following data: Timestamp A decimal value that represents electricity usage
Time series table	The following columns: A meter ID column of type BIGINT A TimeSeries data type column
Origin	All meter IDs have an origin of 2010-11-10 00:00:00.00000
Regularity	Regular
Metadata	No metadata
Where to store the data	In an automatically created container in the same dbspace as the stores_demo database, which is in the root dbspace by default
How to access the data	A virtual table

Creating a TimeSeries data type and table

About this task

You create a TimeSeries data type with columns for the timestamp and the electricity usage value. Then you create a table that has primary key column for the meter ID and a TimeSeries column.

Procedure

To create the TimeSeries data type and table:

Create a TimeSeries subtype named my_meter_data in the stores_demo database by running the following SQL statement:
```
CREATE ROW TYPE my_meter_data(
    timestamp    DATETIME YEAR TO FRACTION(5),
    data         DECIMAL(4,3)
);
```
The timestamp column contains the time of the meter reading and the data column contains the reading value.

Create a time series table named my_ts_data by running the following SQL statement:

CREATE TABLE IF NOT EXISTS my_ts_data (
	meter_id	BIGINT NOT NULL PRIMARY KEY,
 raw_reads	 TIMESERIES(my_meter_data)
) LOCK MODE ROW;

Creating regular, empty time series

About this task

You need to define the properties of the time series for each meter ID by loading the meter IDs into the time series table and creating a regular, empty time series for each meter ID. You use the meter IDs from the ts_data table in the stores_demo database to populate the meter_id column of your my_ts_data table.

Procedure

To create regular, empty time series:

Create an unload file named my_meter_id.unl that contains the meter IDs from the loc_esi_id column of the ts_data table by running the following SQL statement:
```
UNLOAD TO "my_meter_id.unl" SELECT loc_esi_id FROM ts_data; 
```
Create a temporary table named my_tmp and load the meter IDs into it by running the following SQL statements:
```
CREATE TEMP TABLE my_tmp (
  id BIGINT NOT NULL PRIMARY KEY);

LOAD FROM "my_meter_id.unl" INSERT INTO my_tmp;
```
You use this table in the next step to create a time series for each meter ID with one SQL statement instead of running a separate SQL statement for each meter ID.
Create a regular, empty time series for each meter ID that uses the pre-defined calendar ts_15min by running the following SQL statement, which uses the time series input function:
```
INSERT INTO my_ts_data
  SELECT id, 
         "origin(2010-11-10 00:00:00.00000),calendar(ts_15min),
          threshold(0),regular,[]"
  FROM my_tmp;
```
Because you did not specify a container name, the time series for each meter ID is stored in a container in the same dbspace in which the table resides. The container is created automatically and is a member of the default container pool.

Accessing time series data through a virtual table

About this task

You create a virtual table to view the time series data in relational data format.

Procedure

To create a virtual table based on the time series table:

Use the TSCreateVirtualTab procedure to create a virtual table named my_vt2 that is based on the my_ts_data table by running the following SQL statement:

EXECUTE PROCEDURE TSCreateVirtualTab("my_vt2", "my_ts_data", 
   "calendar(ts_15min), origin(2010-11-10 00:00:00.00000)");

Results

You can query the virtual table by running standard SQL statements. For example, the following query returns the first value for each of the 28 meter IDs:

SELECT * FROM my_vt2 WHERE timestamp = "2010-11-10 00:00:00.00000";