Difference from two values of the same series

Grafana Time Series Panel
1

Hello everyone,

I currently have a MariaDB table that looks like this. The left field is the date (in UNIX timestamp), the right are Wh from an energy meter (a new value is added every minute by a Python service) :

timestamp	BASE
1620500943 	11128308
1620501004 	11128362 	# (1 minute later)
1620501065 	11128384 	# (2 minutes later)
1620501126 	11128396 	# ...
1620501187 	11128406
1620501248 	11128416
1620501308 	11128437
...

I was able to display a graph of the BASE value in Grafana without much issue. However, what I am now trying to do is to display on the graph a line that shows the difference between a value and the previous one, like this:

timestamp	BASE
1620500943 	0
1620501004 	54
1620501065 	22
1620501126 	12
1620501187 	10
1620501248 	10
1620501308 	21
...

I was really surprised to see that Grafana does not come with features which make that kind of calculation (last value minus second-to-last value) and create a new line with the result.

Eventually, what I am trying to do is display the difference between two values separated from 24 hours - therefore being also able to filter out the lines that I do not want, and make the calculation on the remaining lines.

timestamp	BASE
1620501004 	0
1620587404 	23584 	# (24 hours later)

What is the best way to achieve that?

  • Does this feature actually exist in Grafana? (maybe I missed it)
  • Is it possible to achieve some things with the SQL of MariaDB?
  • If not, will switching to a system like InfluxDB or Prometheus will help?
  • Eventually, for maximal configuration, it is possible to put some code between the data source and the graph (like a Python script) that will do all the filtering, calculation and aggregation work for us?
1 Like
2

Hi @ailothaen! Cool to see another energy measurement use case.

What you’d line to do will almost certainly need to happen via the query itself, so will depend on support by your database query engine, rather that Grafana itself. Indeed, I don’t believe that Grafana internally supports this kind of calculation. There is a new Expressions feature currently in beta, but I don’t think it does what you need here.

It’s definitely possible to do this in an Influx query - and IIRC also with a PostgresSQL or MySQL query. Unfortunately I have no real familiarity with MariaDB. I suspect googling something like “difference between consecutive values in mariadb” may (or may not) turn up useful results.

This is possible, but it would involve a level of complexity that would probably not be justifiable, unless you have truly custom calculation needs. You would (probably) need to set up an API that behaves like a data source and does the data manipulation under the hood.

For your more immediate needs, I would investigate what’s possible via database queries; most databases these days have fairly powerful query engines.

1 Like
3

@ailothaen how did you actuallys solve this?

4

This needs to be done on mariadb side via sql

Grafana is presentation layer. It does do some cool stuff but not really designed for this

5

Thanks, I did it with a SQL query which stores the last values in variables:

SELECT
	date_format (tsData, '%Y-%m-%d %H:00') as tsDataGroup,
	SUM(`NETZ1.GEL.DIF`),
	SUM(`BHKW1.ERZ.DIF`)
FROM
	(
		SELECT
			tsData,
			(
				SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_ - @last1
			) / 10 as "NETZ1.GEL.DIF",
			@last1 := SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_,
			(
				SPS_01_VCN_ulCOUNTERS_COU_STRZ_BHKW1_ERZ_KWH_ - @last2
			) / 10 as "BHKW1.ERZ.DIF",
			@last2 := SPS_01_VCN_ulCOUNTERS_COU_STRZ_BHKW1_ERZ_KWH_
		FROM
			biog.statistics_15min
			CROSS JOIN (
				SELECT
					@last1 := NULL,
					@last2 := NULL
			) init_variable
		WHERE
			tsData >= now() - INTERVAL 30 DAY
	) as data
WHERE
	`NETZ1.GEL.DIF` IS NOT NULL
GROUP BY
	tsDataGroup;

Could get done also with the LAG() function, but that is not supported at older MySQL servers.

SELECT
	*
from
	(
		SELECT
			row_number() OVER (
				ORDER BY
					tsData
			) -1 AS RowNr,
			date_format (tsData, '%Y-%m-%d %H:00') as tsData,
			FORMAT(
				SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_BEZ_KWH_ / 100.0,
				2,
				'de_DE'
			) AS "NETZ.BEZ",
			FORMAT(
				SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_ / 100.0,
				2,
				'de_DE'
			) as "NETZ.GEL",
			FORMAT(
				(
					SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_BEZ_KWH_ - LAG(
						SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_BEZ_KWH_,
						1,
						0
					) OVER (
						ORDER BY
							tsData
					)
				) / 100.0,
				2,
				'de_DE'
			) as "BLP1.TO.FM1.DIF",
			FORMAT(
				(
					SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_ - LAG(
						SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_,
						1,
						0
					) OVER (
						ORDER BY
							tsData
					)
				) / 100.0,
				2,
				'de_DE'
			) as "BLP1.TO.FM2.DIF"
		FROM
			biog.statistics_15min
		WHERE
			tsData >= now() - INTERVAL 1 DAY - INTERVAL 15 MINUTE
		GROUP by
			2
		ORDER BY
			tsData
	) result_table
WHERE
	RowNr > 0;
6

How is performance on this query. Cool inline variable

7

The query itself is interestingly pretty fast, compared to readings only without calculations.

SELECT
    date_format (tsData, '%Y-%m-%d %H:00') as tsDataGroup,
    `NETZ1.GEL.DIF`,
    `BHKW1.ERZ.DIF`
FROM
    (
        SELECT
            tsData,
            (
                SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_ - @last1
            ) / 10 as "NETZ1.GEL.DIF",
            @last1 := SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_,
            (
                SPS_01_VCN_ulCOUNTERS_COU_STRZ_BHKW1_ERZ_KWH_ - @last2
            ) / 10 as "BHKW1.ERZ.DIF",
            @last2 := SPS_01_VCN_ulCOUNTERS_COU_STRZ_BHKW1_ERZ_KWH_
        FROM
            biog.statistics_15min
            CROSS JOIN (
                SELECT
                    @last1 := NULL,
                    @last2 := NULL
            ) init_variable
        WHERE
            tsData >= now() - INTERVAL 30 DAY
    ) as data
WHERE
    `NETZ1.GEL.DIF` IS NOT NULL
LIMIT
    0, 50000

2641 row(s) returned 0.234 sec / 1.172 sec

SELECT
    tsData,
    SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_ / 10 as "NETZ1.GEL.DIF",
    SPS_01_VCN_ulCOUNTERS_COU_STRZ_BHKW1_ERZ_KWH_ / 10 as "BHKW1.ERZ.DIF"
FROM
    biog.statistics_15min
WHERE
    tsData >= now() - INTERVAL 30 DAY
LIMIT
    0, 50000

2642 row(s) returned 0.250 sec / 1.594 sec

SELECT
    date_format (tsData, '%Y-%m-%d %H:00') as tsDataGroup,
    SUM(`NETZ1.GEL.DIF`),
    SUM(`BHKW1.ERZ.DIF`)
FROM
    (
        SELECT
            tsData,
            (
                SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_ - @last1
            ) / 10 as "NETZ1.GEL.DIF",
            @last1 := SPS_01_VCN_ulCOUNTERS_COU_STRZ_NETZ1_GEL_KWH_,
            (
                SPS_01_VCN_ulCOUNTERS_COU_STRZ_BHKW1_ERZ_KWH_ - @last2
            ) / 10 as "BHKW1.ERZ.DIF",
            @last2 := SPS_01_VCN_ulCOUNTERS_COU_STRZ_BHKW1_ERZ_KWH_
        FROM
            biog.statistics_15min
            CROSS JOIN (
                SELECT
                    @last1 := NULL,
                    @last2 := NULL
            ) init_variable
        WHERE
            tsData >= now() - INTERVAL 30 DAY
    ) as data
WHERE
    `NETZ1.GEL.DIF` IS NOT NULL
GROUP BY
    tsDataGroup
LIMIT
    0, 50000

674 row(s) returned 0.250 sec / 0.125 sec

I was connected remotely via VPN, means the fetch can take some time depending on the amount of data which gets received. Differential values get fetched faster as the counter raw values itself, because it is less data to transfer.

1 Like