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 below:

Let’s now create some sensor readings. In this case, we will generate the table and 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;

While creating this table, we did the following:

• TIMESTAMP(ts) elected ts as designated timestamp. This will enable time partitioning.
• PARTITION BY MONTH created a monthly partition strategy. Our data will be sharded in monthly files.

The generated data will look like the below:

Running queries

Let’s first select all records from the readings table (note the omission of SELECT * FROM):

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;

Results should look like the data below:

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

Results should look like the data below:

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

Results should look like the data 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.