Linux/TimescaleDB

Postgres

Setup

CREATE TABLE sensor_data (
        time TIMESTAMPTZ NOT NULL,
        topic TEXT,
        value DOUBLE PRECISION
);

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

Insert data via Node-RED

INSERT INTO sensor_data (time, topic, value) VALUES (
    now(),
    '{{{ msg.topic }}}',
    '{{{ msg.payload }}}'
);

Get table size

SELECT hypertable_size('sensor_data');

Time bucket

https://docs.timescale.com/use-timescale/latest/time-buckets/about-time-buckets/
15min average time_buckets

SELECT time_bucket('15 minute', time) as fifteen_min, avg(value) AS avg_value FROM "sensor_data" WHERE "topic" = 'home/plug-c86044-fridge/sensor/power' GROUP by fifteen_min ORDER by fifteen_min DESC LIMIT 50

Counters

https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md

first() + last ()

Links