Migrate grafana dashboards from InfluxDB datasource to TimescaleDB

TimescaleDB and PostgreSQL
1

Hi, everybody! Have you tried this migration? Is there some suggestion, how to replace InfluxQL function like “non_negative_derivative”, for example query from Telegraf metric “net”

SELECT non_negative_derivative(mean(“bytes_recv”), 1s) * 8 AS “RX”, non_negative_derivative(mean(“bytes_sent”), 1s) * 8 AS “TX” FROM “default”.“net” WHERE (“host” =~ /^ad2rm1$/ AND “interface” =~ /^bond0$/ AND “dc” =~ /^tower$/) AND time >= now() - 24h and time <= now() GROUP BY time(2m) fill(linear)

how to get the same results from TimescaleDB?

2

Hi Miroslav, I never did any math like this but I’m interested in know if you made any progress on it.

I was thinking that maybe the toolkit functional pipelines would give you some building blocks to build it. Have you checked it?

3

Hi, it’s slightly more complicated, but in standard (TimescaleDB) SQL it looks something like this:

WITH subquery AS (
    SELECT 
        time_bucket('1m', time) AS time_interval,
        interface,
        AVG(bytes_recv) *8 AS RX,
        AVG(bytes_sent) *8 AS TX
    FROM "net_default"
    WHERE host = 'ad2rm1'
    AND interface = 'bond0'
    AND time BETWEEN '2024-01-14T08:29:00.702Z' AND '2024-01-15T08:29:00.702Z' 
    GROUP BY time_interval, interface
    ORDER BY time_interval
)
SELECT
    interface,
    time_interval,
    GREATEST(0,(RX - lag(RX) OVER (PARTITION BY interface ORDER BY time_interval))) / EXTRACT(EPOCH FROM ('1m'::interval)) AS RX,
    GREATEST(0,(TX - lag(TX) OVER (PARTITION BY interface ORDER BY time_interval))) / EXTRACT(EPOCH FROM ('1m'::interval)) AS TX
FROM subquery
ORDER BY time_interval;