The structure of virtual tables

A virtual table that is based on a time series has the same schema as the base table, except for the TimeSeries column. The TimeSeries column is replaced with the columns of the TimeSeries subtype. A virtual table based on an expression on a time series displays the TimeSeries subtype that is the result of the expression, instead of the subtype from the base table.

For example, the table ts_data contains a TimeSeries column called raw_reads that contains a row type with tstamp and value columns. The following table displays part of the ts_data table. The actual time stamp values are shown for clarity, although the time stamp values are calculated instead of stored in regular time series.

Table 1. Data in a table with a TimeSeries column
loc_esi_id measure_unit direction raw_reads
4727354321000111 KWH P (2010-11-10 00:00:00.00000, 0.092), (2010-11-10 00:15:00.00000, 0.084), ...
4727354321046021 KWH P (2010-11-10 00:00:00.00000, 0.041), (2010-11-10 00:15:00.00000, 0.041), ...
4727354321090954 KWH P (2010-11-10 00:00:00.00000, 0.026), (2010-11-10 00:15:00.00000, 0.035), ...
The virtual table that is based on the ts_data table converts the raw_reads column elements into individual columns. The rows are ordered by timestamp, starting with the earliest timestamp. The following table displays part of the virtual table that is based on the ts_data table.
Table 2. Data in a virtual table based on a time series
loc_esi_id measure_unit direction tstamp value
4727354321000111 KWH P 2010-11-10 00:00:00.00000 0.092
4727354321000111 KWH P 2010-11-10 00:15:00.00000 0.084
. . .
4727354321046021 KWH P 2010-11-10 00:00:00.00000 0.041
4727354321046021 KWH P 2010-11-10 00:15:00.00000 0.041
. . .
4727354321090954 KWH P 2010-11-10 00:00:00.00000 0.026
4727354321090954 KWH P 2010-11-10 00:15:00.00000 0.035

When you create a virtual table that is based on the results of an expression that is performed on a time series, you specify the TimeSeries subtype appropriate for containing the results of the expression. The virtual table is based on the specified TimeSeries data type and the other columns from the base table.