Postgres/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');
Query
Grafana timefilter macro
- limits query to timeframe selected in grafana
SELECT *
FROM sensor_data
WHERE (topic = 'home/modbus-meter/sensor/total_system_power_w/state'
AND $__timeFilter("time"))
Time buckets
- https://docs.timescale.com/use-timescale/latest/time-buckets/about-time-buckets/
- https://docs.timescale.com/use-timescale/latest/time-buckets/use-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' AND $__timeFilter("time")
GROUP by fifteen_min
Counters
- https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md
- https://stackoverflow.com/questions/76774353/get-complete-hours-time-buckets-with-timescaledb :
SELECT time_bucket('1 hour', time) as bucket,
first(time,time) as period_begin,
last(time,time) as period_end,
MAX(counter) - MIN(counter) as total_consumption
FROM
GROUP BY 1;
first() + last ()
Grafana > Dashboards > Settings > Variables
- topic = SELECT DISTINCT topic FROM "sensor_data"