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?
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.