Virtual tables for time series data

A virtual table provides a relational view of your time series data.

Virtual tables are useful for viewing time series data in a simple format. An SQL SELECT statement against a virtual table returns data in ordinary data type format, rather than in the TimeSeries data type format. Many of the operations that TimeSeries SQL functions and API routines perform can be done using SQL statements against a virtual table. Some SQL queries are easier to write for the virtual table than for an underlying time series table, especially SQL queries with qualifications on a TimeSeries column.

The virtual table is not a real table stored in the database. The data is not duplicated. At any moment, data visible in the virtual table is the same as the data in the base table. The data in the virtual table is updated to reflect changes to the data in the base table. You cannot create an index on a time series virtual table.

You can use a virtual table as the basis of a data mart and accelerate queries on time series data. When you accelerate queries, you can analyze large amounts of data faster than directly querying the base table or the virtual table.

Some operations are difficult or impossible in one interface but are easily accomplished in the other. For example, finding the average value of one of the fields in a time series over a time is easier with a query against a virtual table than by using TimeSeries functions. The following query against a virtual table finds the average stock price over a year:

select avg(vol) from daily_stocks_no_ts 
where stock_name = 'IBM' 
and timestamp between datetime(2010-1-1) year to day 
and datetime(2010-12-31) year to day;

However, aggregating from one calendar to another is easier using the AggregateBy routine.

Selecting the nth element in a regular time series is easy using the GetNthElem routine but difficult using a virtual table.

You can insert data into a virtual table that is based on a time series table, which automatically updates the underlying base table. You can use SELECT and INSERT statements with time series virtual tables. You cannot use UPDATE or DELETE statements, but you can update a time series element in the base table by inserting a new element for the same time point into the virtual table.

You can create a virtual table that is based on an expression that is performed on a time series table.

You can create a virtual table that is based on only one TimeSeries column at a time. If the base table has multiple TimeSeries columns, you can create a virtual table for each of them.