The goal of this guide is to explore QuestDB’s features to interact with time series data. This assumes you have an instance running. You can find guides to setup QuestDB on the introduction page.

In this tutorial, you will learn how to

• Create tables
• Populate tables with sample data
• Run simple and advanced queries
• Delete tables

As an example, we will look at hypothetical temperature readings from a variety of sensors.

:::info

All commands are run through the Web Console accessible at http://localhost:9000.

You can also run the same SQL via the Postgres endpoint or the REST API.

:::

Creating a table

The first step is to create tables. One will contain the metadata of our sensors, the other will contain the readings from these sensors.

Let’s start by creating the sensors table:

CREATE TABLE sensors (ID LONG, make STRING, city STRING);

For more information about this statement, please refer to the CREATE TABLE reference documentation.

Inserting data

Let’s populate our sensors table with procedurally-generated data:

INSERT INTO sensors
    SELECT
        x ID, --increasing integer
        rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make,
        rnd_str('New York', 'Miami', 'Boston', 'Chicago', 'San Francisco') city
    FROM long_sequence(10000) x
;

For more information about this statement, please refer to the INSERT reference documentation. About the functions, please refer to the random generator and the row generator pages.

Our sensors table now contains 10,000 randomly generated sensor values of different makes and in various cities. It should look like the table below:

Let’s now create some sensor readings. In this case, we will create the table and generate the data at the same time:

CREATE TABLE readings
AS(
    SELECT
        x ID,
        timestamp_sequence(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'), rnd_long(1,10,2) * 100000L) ts,
        rnd_double(0)*8 + 15 temp,
        rnd_long(0, 10000, 0) sensorId
    FROM long_sequence(10000000) x)
TIMESTAMP(ts)
PARTITION BY MONTH;

The query above demonstrates how to use the following features:

• TIMESTAMP(ts) elects the ts column as a designated timestamp. This enables partitioning tables by time.
• PARTITION BY MONTH creates a monthly partitioning strategy where the stored data is effectively sharded by month.

The generated data will look like the following:

Running queries

Let’s select all records from the readings table (note that SELECT * FROM is optional in QuestDB):

readings;

Let’s also select the count of records from readings:

SELECT count() FROM readings;

and the average reading:

SELECT avg(temp) FROM readings;

We can now leverage our sensors table to get more interesting data:

SELECT *
FROM readings
JOIN(
    SELECT ID sensId, make, city
    FROM sensors)
ON readings.sensorId = sensId;

The results should look like the table below:

SELECT city, max(temp)
FROM readings
JOIN(
    SELECT ID sensId, city
    FROM sensors) a
ON readings.sensorId = a.sensId;

The results should look like the table below:

SELECT ts, city, make, avg(temp)
FROM readings timestamp(ts)
JOIN
    (SELECT ID sensId, city, make
    FROM sensors
    WHERE city='Miami' AND make='Omron') a
ON readings.sensorId = a.sensId
WHERE ts ='2019-10-21;1d' -- this is an interval between 21-10 and 1 day later

The results should look like the table below:

For more information about these statements, please refer to the SELECT and JOIN pages.

Deleting tables

Upon dropping the table, all data is deleted:

DROP TABLE readings;
DROP TABLE sensors;