Creating Hypertables

The primary point of interaction with your data is a hypertable, the abstraction of a single continuous table across all space and time intervals, such that one can query it via vanilla SQL.

TIP:First make sure that you have properly installed AND setup TimescaleDB within your PostgreSQL instance.

Creating a (Hyper)table

To create a hypertable, you start with a regular SQL table, and then convert it into a hypertable via the function create_hypertable.

The following example creates a hypertable for tracking temperature and humidity across a collection of devices over time.

  1. -- We start by creating a regular SQL table
  2. CREATE TABLE conditions (
  3. time TIMESTAMPTZ NOT NULL,
  4. location TEXT NOT NULL,
  5. temperature DOUBLE PRECISION NULL,
  6. humidity DOUBLE PRECISION NULL
  7. );

Next, transform it into a hypertable with create_hypertable:

  1. -- This creates a hypertable that is partitioned by time
  2. -- using the values in the `time` column.
  3. SELECT create_hypertable('conditions', 'time');

TIP:The ‘time’ column used in the create_hypertable function supports timestamp, date, or integer types, so you can use a parameter that is not explicitly time-based, as long as it can increment. For example, a monotonically increasing id would work. You must specify a chunk time interval when creating a hypertable if you use a monotonically increasing id.

TIP:If you want to use distributed hypertables in a multinode TimescaleDB setup, refer to the scaling out section for more information.

Inserting & Querying

Inserting data into the hypertable is done via normal SQL INSERT commands, e.g. using millisecond timestamps:

  1. INSERT INTO conditions(time, location, temperature, humidity)
  2. VALUES (NOW(), 'office', 70.0, 50.0);

Similarly, querying data is done via normal SQL SELECT commands.

  1. SELECT * FROM conditions ORDER BY time DESC LIMIT 100;

SQL UPDATE and DELETE commands also work as expected. For more examples of using TimescaleDB’s standard SQL interface, please see our use pages.