Display a growing sum of a value beginning at a fixed time

Grafana InfluxDB
1

  • What Grafana version and what operating system are you using?
    Grafana 11.3.0 on OpenSuse Tumbleweed

  • What are you trying to achieve?
    Get liters available in a tank in a time series starting from the last filling date (up to ~2 years) to now by summing used liters (calculated from active consumption seconds per day) from an influx db

  • How are you trying to achieve it?
    I have this sql that shows the curently remaining liters:
    SELECT 7000 - sum(“active”) / 2877086 * 3259 FROM “heating” WHERE time > ‘2024-06-12T00:00:00Z’

  • What happened?
    The panel shows only the one correct value of today

I have difficulties expanding this to getting a value for each day not just for now.
End result should be a panel that starts at the last filling date (no need for this to be variable) and ends today. It should show the diminishing available liters for each day.

1 Like
2

Welcome @joba1 to the Grafana forum.

Does this work? You may have to mess with the WHERE time section to start at the last fill date.

SELECT
    DATE_TRUNC('day', time) AS calendar_day,
    7000 - SUM("active") / 2877086.0 * 3259 AS amount_remaining
FROM
    "heating"
WHERE
    time >= $__timeFrom() AND time <= $__timeTo()
GROUP BY
    calendar_day
ORDER BY
    calendar_day;
3

Thank you for thinking about this!

I removed the __timeTo() because it is always now()
I replaced __timeFrom() with my date string because grafana or influxdb complained it does not exist.

SELECT
    DATE_TRUNC('day', time) AS calendar_day,
    7000 - SUM("active") / 2877086.0 * 3259 AS amount_remaining
FROM
    "heating"
WHERE
    time >= '2024-07-12T00:00:00Z'
GROUP BY
    calendar_day
ORDER BY
    calendar_day;

Now I get
InfluxDB returned error: error parsing query: only ORDER BY time supported at this time

4

@joba

Just to be clear, are you using SQL or InfluxQL as your query language?

5

I use InfluxQL.

If this is not easily achievable: I get the active times from analysing the burner temperature with a python script - maybe it is easier to also convert to liters used/remaining there

6

Oh, you had previously stated “I have this sql that shows the currently remaining liters” so I presumed you were using SQL with Influx 3 or something.

If you are using InfluxQL, maybe this?

SELECT 
    SUM("active") / 2877086.0 * 3259 AS amount_used, 
    7000 - SUM("active") / 2877086.0 * 3259 AS amount_remaining 
FROM "heating" 
WHERE time >= '2024-07-12T00:00:00Z' 
GROUP BY time(1d) 
ORDER BY time;

InfluxQL does not have DATE_TRUNC(), so we use GROUP BY time(1d) to aggregate data per day.

7

You brought me close enough! With cumulative_sum() this works:

SELECT cumulative_sum(mean("active"))  / 2877086 * -3259 +7000 
FROM "heating" 
WHERE ("time" > '2024-07-01') AND $timeFilter 
GROUP BY time(1d) 
fill(null)

image
image2815×1324 246 KB

Thanks!

1 Like