How to simulate a basic IoT sensor dataset on PostgreSQL or TimescaleDB

The Internet of Things (IoT) describes a trend where computing is becoming ubiquitous and is embedded in more and more physical things. For many of these things, the purpose of IoT is to collect sensor data about the environment in which it exists, for example oil wells, factories, power plants, farms, moving vehicles, office buildings, homes.

In other words, IoT is all about the data. And the datasets generated by these things are generally time-series in nature, with relational metadata to describe those things.

Often, it is necessary to simulate IoT sensor data, for example, when testing a new system. This tutorial shows how to simulate a basic IoT sensor dataset on PostgreSQL, or TimescaleDB.

note

For creating a more advanced simulated IoT dataset, try the Time-series Benchmarking Suite (TSBS).

Prerequisites

To complete this tutorial, you need a cursory knowledge of the Structured Query Language (SQL). The tutorial walks you through each SQL command, but it is helpful if you’ve seen SQL before.

To start, install TimescaleDB. When your installation is complete, you can ingest or create sample data and finish the tutorial.

Set up your tables

First, connect to your database via psql.

Second, create the “sensors” and “sensor_data” tables:

  2. CREATE TABLE sensors(
  4.   id SERIAL PRIMARY KEY,
  6.   type VARCHAR(50),
  8.   location VARCHAR(50)
  10. );
  12. CREATE TABLE sensor_data (
  14.   time TIMESTAMPTZ NOT NULL,
  16.   sensor_id INTEGER,
  18.   temperature DOUBLE PRECISION,
  20.   cpu DOUBLE PRECISION,
  22.   FOREIGN KEY (sensor_id) REFERENCES sensors (id)
  24. );

If you are using TimescaleDB, convert the sensor_data table into a hypertable:

  2. SELECT create_hypertable('sensor_data', 'time');

Fourth, populate the sensors table with 4 sensors:

  2. INSERT INTO sensors (type, location) VALUES
  4. ('a','floor'),
  6. ('a', 'ceiling'),
  8. ('b','floor'),
  10. ('b', 'ceiling');

Fifth, verify that the sensors were created correctly:

  2. SELECT * FROM sensors;

After running that last SQL statement, you should see something like this:

  2. id | type | location
  4. ----+------+----------
  6.   1 | a    | floor
  8.   2 | a    | ceiling
  10.   3 | b    | floor
  12.   4 | b    | ceiling
  14. (4 rows)

Create the simulated IoT sensor data

note

This section shows results of the queries as examples, but because the tutorial generates random data every time it is run, your results look different, but is structured the same way.

Generate a dataset for all of our four sensors and insert into the sensor_data table:

  2. INSERT INTO sensor_data (time, sensor_id, cpu, temperature)
  4. SELECT
  6.   time,
  8.   sensor_id,
  10.   random() AS cpu,
  12.   random()*100 AS temperature
  14. FROM generate_series(now() - interval '24 hour', now(), interval '5 minute') AS g1(time), generate_series(1,4,1) AS g2(sensor_id);

Verify that the simulated sensor data was written correctly:

  2. SELECT * FROM sensor_data ORDER BY time;

Example output:

  2. time              | sensor_id |    temperature     |         cpu         
  5. -------------------------------+-----------+--------------------+---------------------
  7.  2020-03-31 15:56:25.843575+00 |         1 |   6.86688972637057 |   0.682070567272604
  9.  2020-03-31 15:56:40.244287+00 |         2 |    26.589260622859 |   0.229583469685167
  11.  2030-03-31 15:56:45.653115+00 |         3 |   79.9925176426768 |   0.457779890391976
  13.  2020-03-31 15:56:53.560205+00 |         4 |   24.3201029952615 |   0.641885648947209
  15.  2020-03-31 16:01:25.843575+00 |         1 |   33.3203678019345 |  0.0159163917414844
  17.  2020-03-31 16:01:40.244287+00 |         2 |   31.2673618085682 |   0.701185956597328
  19.  2020-03-31 16:01:45.653115+00 |         3 |   85.2960689924657 |   0.693413889966905
  21.  2020-03-31 16:01:53.560205+00 |         4 |   79.4769988860935 |   0.360561791341752
  23. ...

Congratulations, you’ve created a basic IoT sensor dataset. Now let’s run some queries.

Run basic queries (optional)

This section requires TimescaleDB.

Average temperature, average cpu by 30 minute windows:

  2. SELECT
  4.   time_bucket('30 minutes', time) AS period,
  6.   AVG(temperature) AS avg_temp,
  8.   AVG(cpu) AS avg_cpu
  10. FROM sensor_data
  12. GROUP BY period;

SAMPLE OUTPUT:

  2. period         |     avg_temp     |      avg_cpu      
  5. ------------------------+------------------+-------------------
  7.  2020-03-31 19:00:00+00 | 49.6615830013373 | 0.477344429974134
  9.  2020-03-31 22:00:00+00 | 58.8521540844037 | 0.503637770501276
  11.  2020-03-31 16:00:00+00 | 50.4250325243144 | 0.511075591299838
  13.  2020-03-31 17:30:00+00 | 49.0742547437549 | 0.527267253802468
  15.  2020-04-01 14:30:00+00 | 49.3416377226822 | 0.438027751864865
  17.  ...

Average & last temperature, average cpu by 30 minute windows:

But what if you don’t just want the average temperature for each period, but also the last temperature? For example if you wanted to understand the final temperature value at the end of the interval:

  2. SELECT
  4.   time_bucket('30 minutes', time) AS period,
  6.   AVG(temperature) AS avg_temp,
  8.   last(temperature, time) AS last_temp,
  10.   AVG(cpu) AS avg_cpu
  12. FROM sensor_data
  14. GROUP BY period;

Example output:

  2. period         |     avg_temp     |    last_temp     |      avg_cpu      
  5. ------------------------+------------------+------------------+-------------------
  7.  2020-03-31 19:00:00+00 | 49.6615830013373 | 84.3963081017137 | 0.477344429974134
  9.  2020-03-31 22:00:00+00 | 58.8521540844037 | 76.5528806950897 | 0.503637770501276
  11.  2020-03-31 16:00:00+00 | 50.4250325243144 | 43.5192013625056 | 0.511075591299838
  13.  2020-03-31 17:30:00+00 | 49.0742547437549 |  22.740753274411 | 0.527267253802468
  15.  2020-04-01 14:30:00+00 | 49.3416377226822 | 59.1331578791142 | 0.438027751864865
  17. ...

Using the sensor metadata

Now let’s take advantage of some of the metadata you have stored in the sensors table:

  2. SELECT
  4.   sensors.location,
  6.   time_bucket('30 minutes', time) AS period,
  8.   AVG(temperature) AS avg_temp,
  10.   last(temperature, time) AS last_temp,
  12.   AVG(cpu) AS avg_cpu
  14. FROM sensor_data JOIN sensors on sensor_data.sensor_id = sensors.id
  16. GROUP BY period, sensors.location;

Example output:

  2. location |         period         |     avg_temp     |     last_temp     |      avg_cpu      
  5. ----------+------------------------+------------------+-------------------+-------------------
  7.  ceiling  | 20120-03-31 15:30:00+00 | 25.4546818090603 |  24.3201029952615 | 0.435734559316188
  9.  floor    | 2020-03-31 15:30:00+00 | 43.4297036845237 |  79.9925176426768 |  0.56992522883229
  11.  ceiling  | 2020-03-31 16:00:00+00 | 53.8454438598516 |  43.5192013625056 | 0.490728285357666
  13.  floor    | 2020-03-31 16:00:00+00 | 47.0046211887772 |  23.0230117216706 |  0.53142289724201
  15.  ceiling  | 2020-03-31 16:30:00+00 | 58.7817596504465 |  63.6621567420661 | 0.488188337767497
  17.  floor    | 2020-03-31 16:30:00+00 |  44.611586847653 |  2.21919436007738 | 0.434762630766879
  19.  ceiling  | 2020-03-31 17:00:00+00 | 35.7026890735142 |  42.9420990403742 | 0.550129583687522
  21.  floor    | 2020-03-31 17:00:00+00 | 62.2794370166957 |  52.6636955793947 | 0.454323202022351
  23. ...

Next steps

Congratulations, you now have a basic IoT sensor dataset you can use for testing in PostgreSQL or TimescaleDB.

To learn more about TimescaleDB, or about the TimescaleDB concepts and functions you just used, please visit these pages in our developer documentation: