Jump to content

Linux/TimescaleDB

From Wiki

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

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

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"

Continuous aggregates

Links

Retrieved from "https://wiki.marcluerssen.de/index.php?title=Linux/TimescaleDB&oldid=4325"