Postgres/TimescaleDB
< Postgres
Setup Timescale for Unified Namespace data logging
- connect
psql -d "postgres://user:password@localhost/postgres"
- check
\dx # list extensions \dt # list all tables
- Create database
# DROP DATABASE unifiednamespace; CREATE DATABASE unifiednamespace OWNER user; GRANT ALL PRIVILEGES ON DATABASE unifiednamespace TO user; \c unifiednamespace # switch to database
Option A: Simple table (with topic as text column
-- Create table
CREATE TABLE IF NOT EXISTS process_value_numeric (
timestamp TIMESTAMPTZ NOT NULL,
topic TEXT NOT NULL,
value DOUBLE PRECISION NULL );
-- Create hypertable
SELECT create_hypertable('process_value_numeric', 'timestamp');
-- add index
CREATE INDEX ON process_value_numeric (topic, timestamp DESC);
- View table information
\d process_value_numeric
Option B: Linked tables (with topic as id)
- create topic table
CREATE TABLE IF NOT EXISTS topic (
id SERIAL PRIMARY KEY,
topic TEXT NOT NULL,
UNIQUE(topic)
);
- create data tables
CREATE TABLE IF NOT EXISTS process_value_numeric (
timestamp TIMESTAMPTZ NOT NULL,
topic_id INTEGER NOT NULL REFERENCES topic(id),
value DOUBLE PRECISION NULL,
UNIQUE(timestamp, topic_id)
);
Insert data
- via Node-RED
INSERT INTO process_value (timestamp, topic, value) VALUES (
now(),
'{{{ msg.topic }}}',
'{{{ msg.payload }}}'
);
Get table size
SELECT hypertable_size('process_value');
Query
Grafana timefilter macro
- limits query to timeframe selected in grafana
SELECT *
FROM process_value
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('1 hour', timestamp) as time,
avg(value) AS avg_value
FROM sensor_data
WHERE
topic = 'home/modbus-meter/sensor/total_system_power_w/state'
AND
$__timeFilter("timestamp")
GROUP by time
Meter / increasing counter
- https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md
- https://stackoverflow.com/questions/76774353/get-complete-hours-time-buckets-with-timescaledb
- https://docs.timescale.com/use-timescale/latest/query-data/advanced-analytic-queries/#calculate-the-increase-in-a-value
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"