Time series database

A time series database (TSDB) is a software system that is optimized for handling time series data, arrays of numbers indexed by time (a datetime or a datetime range). In some fields these time series are called profiles, curves, or traces.

Ideally, repositories of time series are natively implemented using specialized database algorithms. However, it is possible to store time series as binary large objects (BLOBs) in a relational database or by using a VLDB approach coupled with a pure star schema. Efficiency is often improved if time is treated as a discrete quantity rather than as a continuous mathematical dimension.

Overview

A TSDB allows users to create, enumerate, update and destroy various time series and organize them. The server often supports a number of basic calculations that work on a series as a whole, such as multiplying, adding, or otherwise combining various time series into a new time series. They can also filter on arbitrary patterns such as time ranges, low value filters, high value filters, or even have the values of one series filter another. Some TSDBs also build in additional statistical functions that are targeted to time series data.

For example, for the following expression:

select gold_price * gold_volume

the TSDB would join the two series 'gold_price' and 'gold_volume' based on the overlapping areas of time for each, multiply the values where they intersect, and then output a single composite time series.

TSDBs often allow users to manage a repository of filters or masks that specify in some way a pattern. In this way, one can readily assemble time series data. Assuming such a filter exists, one might hypothetically write

select onpeak( cellphoneusage )

which would extract out the time series of cellphoneusage that only intersects that of 'onpeak'.

This syntactical simplicity drives the appeal of the TSDB. For example, a simple utility bill might be implemented using a query such as:

select max( onpeak( powerusagekw ) ) * demand_charge;

select sum( onpeak( powerusagekwh ) ) * energy_charge;

Supporting time series data in a relational database

A workable implementation of a time series database can be deployed in a conventional SQL-based relational database provided that the database software supports both binary large objects (BLOBs) and user-defined functions. SQL statements that operate on one or more time series quantities on the same row of a table or join can easily be written, as the user-defined time series functions operate comfortably inside of a SELECT statement. However, time series functionality such as a SUM function operating in the context of a GROUP BY clause cannot be easily achieved.

List of time series databases

The following database systems have functionality optimized for handling time series data.

See also

• Operational historian
• NoSQL

References