Graphing rate per second from randomly timed deltas

Grafana Dashboards
, ,
1

Hey all, I’m having some trouble charting some data, and it looks like InfluxQL may not be able to do what I need. That is, do some math using the time difference between two rows. So I’m hoping that Grafana can do the work with a transformation?

Here’s my sample data:

measurement field value time
foo evts 349 2026-02-02 20:16:13
foo evts 197 2026-02-02 20:16:47
foo evts 471 2026-02-02 20:17:09
foo evts 127 2026-02-02 20:17:23
foo evts 426 2026-02-02 20:17:51
foo evts 229 2026-02-02 20:18:19

The value is the number of events that have happened since the previous row of data. The time between rows is random. What I’m trying to get out is the rate of events:

value time
- 2026-02-02 20:16:13
5.8 2026-02-02 20:16:47
21.4 2026-02-02 20:17:09
9.1 2026-02-02 20:17:23
15.2 2026-02-02 20:17:51
8.2 2026-02-02 20:18:19

So the first value is skipped (because there’s no previous record to compare with), the second value shows 5.8 events per second (197 events in 34 seconds), etc.

Any suggestions?

In case it matters, I’m using Grafana 10.2.6 with an InfluxQL data source talking to InfluxDB 2.8.0. This is my current query, but obviously is not what I want:

SELECT mean("evts")
FROM "foo" WHERE ("host"::tag = '$server') AND $timeFilter
GROUP BY time($__interval) fill(null)
2

I would say that’s a task for DERIVATE() function on the InfluxQL level:

3

Unless I’m misunderstanding something, that only works if the difference between the values is what I’m interested in. For example, if the value was a consistently increasing counter showing total events. But the difference is not meaningful here; in many of the rows shown above the difference would be negative.

1 Like
4

This might be a good candidate for SQL expression but it might require upgrading grafana but not sure

5

You are right. So you have gauge, not counter (not very good metric type for this use case and now do a magic with that :-)).
In theory you may apply CUMULATIVE_SUM() first, so gauge will be “counter” and then you should be able to use use DERIVATE(). Just play with those 2 functions - probably with the subqueries.

6

This isn’t perfect but it seems to work well enough for me, thanks for the help!

SELECT DERIVATIVE(CUMULATIVE_SUM(MEAN("evts")),1s)
FROM "db"."foo"
WHERE time > NOW() - 12h
GROUP BY time(1m)
1 Like