MySQL time series with accumulation

Grafana
1

Dear all

I have data of a flow rate in grams per minute. I would like to have the accumulated flow shown in a graph. The accumulation should start at the earliest time that is shown in the dashboard.

Mathematically it can be expressed as “accumulated” = sum(“flow rate”)/“sampling rate”. For example, if I have 2 samples per minute, I should divide “accumulated” by two.

How can I produce this monotone increasing function in a graph?

Thanks and best regards

2

Hi,
does someone has an idea how to solve this? I was able to produce something useful in MySQL workbench:

SET @csumO2 := 0;
SET @csumH2 := 0;
SELECT
UNIX_TIMESTAMP(time) as time_sec,
(@csumO2 := @csumO2 + F2) as O2,
(@csumH2 := @csumH2 + F3) as H2
FROM measurements
WHERE $__timeFilter(time)

However, this query does not run on grafana…

Best

3

You can create a storage procedure an call it from grafana

4

Hi, thanks for the answer!
Unfortunately I don’t exactly get what you mean. Can you explain in a bit more detail?

5

You can do it like this:

Database:

CREATE PROCEDURE GetData(
IN timeFrom INT,
IN timeTo INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ‘’
BEGIN

SET @csumO2 := 0;
SET @csumH2 := 0;
SELECT
UNIX_TIMESTAMP(time) as time_sec,
(@csumO2 := @csumO2 + F2) as O2,
(@csumH2 := @csumH2 + F3) as H2
FROM measurements
WHERE time BETWEEN FROM_UNIXTIME(timeFrom) AND FROM_UNIXTIME(timeTo)

END;

Grafana query editor:

call GetData($__timeFrom(),$__timeTo())

6

Hi,

Thanks for this suggestion. Unfortunately I get an error in the line
SET @csumO2 := 0;
The zero is underlined in red and it says
Statement is incomplete, expecting: ‘;’

I was trying to run this script in MySQL workbench.

Best

7

Maybe

…
FROM_UNIXTIME(timeTo) ;
…

8

That did not solve the problem…