Calculated derivative with variable recorded times (non-standard periods)

Fluxlang
1

I have manually recorded power meter readings over the last few years. The data has a period of ~1 day to 1 week (whenever I remembered to write the value down). I have switched to using a wireless meter reader that has much higher and reliable read rate, but I’d like to have the old data available since I have it. I have imported the data into Influxdb such that it has the recorded timestamp and the numeric value. Example:

> select * from excel limit 10
name: excel
time                reading
----                -------
1287644400000000000 802
1292313600000000000 1008
1297497600000000000 1279
1300003200000000000 1429
1300258800000000000 1438
1300345200000000000 1441
1300950000000000000 1461
1301468400000000000 1477
1302159600000000000 1501
1306220400000000000 1612

I have read that the derivative function can help with this if the data is all recorded at a fixed interval, but obviously that didn’t happen. Is there a builtin method or recommended approach to calculated either the usage with a non-consistent recording interval? I did this easily within Excel by simply taking the difference in measurement and dividing by the difference in reading date/time.

2

I think it may be possible to use the difference and elapsed time functions and divide to get the rate. I posted a similar question earlier this month for a different set of data but got no response, so I’m assuming this will also go unanswered. I’ll post in this thread what I come up with so others who need help may get it.

3

Hello @bkenobi,
yah I think you’re on to the right track.
I think it would look something like this:

  |> difference(nonNegative: false, columns: ["_value"])
  |> elapsed(unit: 1s)
  |> map(fn: (r) => ({ r with derivative: r._value/r.elapsed }))

Please let me know if that helps.

4

My RPi that was running the Influxdb crashed prior to generating a backup. This was a testing setup so hadn’t gotten to that point yet. Anyway, I have now mostly recovered the setup and intend to implement this in the near future. The syntax you are using doesn’t seem to match what I’m used to for either the CLI or input into Grafana. Forgive me as I’m new to Influxdb, but where would I utilize this code?

5

Hi @bkenobi,

What Anais posted is Flux code, a new scripting & query language that we’ve developed for working with timeseries data. It’s available in the latest InfluxDB 2.0 and 1.8, as well as the latest Grafana plugin for InfluxDB.

6

I just ordered a RPi4 to replace the RPi1 I was testing with. Since the new HW can handle 64-bit, would you recommend moving to Influxdb 2.0 from 1.8? I’ve read it is basically the same and I’ve also read it may cause issues for plugins in other applications (Grafana, Node Red,etc).

7

I’m running Grafana 7.3.6 which appears to already have access to the Flux language built-in.

What I’m not seeing is guidance on where to enter such a query into Grafana. The only place I see that I can type in a raw query is in the dashboard when editing a panel:

image
image1151×213 11.9 KB