Substract last value from second last

Welcome & Getting Started
, , ,
1

Hello I’m completely new in influxdb. I have a time series which looks like this.

1576050398 8268.212
1576050411 8268.213
1576050424 8268.214
1576050437 8268.215
1576050450 8268.216
1576050463 8268.217
1576050476 8268.218
1576050489 8268.219

It represents the total of electric power consumption from my smart meter. I would like to get the difference between the last two values and make a graph in grafana.

8268.219 - 8268.218 = 0.001

How would you do that. Is there a nice query I can use in grafana or do I have to create a InfluxDB Task and make the calculation there?

Thank you very much for your answer.

Greetings Manuel

1 Like
2

@Manuel, if you’re using Flux in Grafana, this would be pretty simple. You’d just use the difference() function to calculate the difference between subsequent points, then use the last() selector function to return only the last record.

from(bucket: "database/retention-policy"
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "exampleMeasurement" and r._field == "exampleField")
  |> difference()
  |> last()
1 Like
3

Hell Scott
Thank you very much for your quick answer. Well, for a rookie this seems to be a InfluxDB task, right? I will follow this instruction and create a task option, a data source and a data transformation.

This functions are also available for Version 1.7.8?

Regards Manuel

4

Flux is InfluxData’s new functional data query/scripting language. This is just a Flux query, not a task. And yes, Flux is packaged with 1.7.8, you just need to enable it in your InfluxDB config.

5

Hello Scott, really cool. Thank you very much for your help. Will check Flux out this weekend :+1:

I didn’t get your point first, because I thought that Flux is just an abbreviation for InfluxDB :joy:

Greetings Manuel

6

Happy to help. And no worries. I should’ve given a little more context to Flux up front. If you have any other questions, feel free to send them my way.

7

Hello Scott
Maybe this is not the right forum to ask this question but I have difficulty to add Flux as a data source to grafana.

   show databases
name: databases
name
----
openhab_db

   show series
key
---
HighRate
Humidity
HumidityHome
LowRate
Temperature
TemperatureHome
TotalRate

  select * from TotalRate
name: TotalRate
time                value
----                -----
1576619160022000000 16958.838
1576619220058000000 16958.842
1576619280039000000 16958.847
1576619340045000000 16958.852
1576619400035000000 16958.857
1576619460055000000 16958.861
1576619520042000000 16958.865
1576619580045000000 16958.869
1576619640039000000 16958.872

After Save & Test I’m getting the Error

Data source connected, but has no data. Verify the “bucket” field and make sure the bucket has data.

What is wrong with my data source configuration?

Thank you very much for your help.

Greetings Manuel!

FluxDataSource|416x499

8

Did you enable Flux in your InfluxDB config and restart InfluxDB?

9

Hello Scott
you are so fast with answering :slight_smile:

Yes that’s what I did.

[23:36:20] openhabian@openHABianPi:~$ grep flux-enabled /etc/influxdb/influxdb.conf
  # flux-enabled = false
  flux-enabled = true

And I restarted also influxdb.

Manuel

10

Ok, and just to make sure – openhab_db is the database and TotalRate is the retention policy within that database? When using Flux with InfluxDB 1.7.x, Flux expects the bucket to be a combination of the database and the retention policy: db_name/rp_name.

You may also need to deselect basic auth. InfluxDB supports basic auth, but Flux doesn’t currently handle any of the authorizations. This may not make a difference. I’m not sure.

11

Hello Scott
Hm…this might be the point. As you can see in my code snippet openhab_db is the database, that’s right. TotalRate is a time series and represents the total of electric power consumption from my smart meter in kWh.

> use openhab_db
Using database openhab_db
> show retention policies
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        true

My retention policy is called autogen. So the bucket must be openhab_db/autogen? If I use this I still get

Data source connected, but has no data. Verify the "bucket" field and make sure the bucket has data.

Will try to enable logging/debugging in influxdb to see more when I make a Save & Test.

Greetings Manuel

12

Yeah, with your setup, your bucket name would be openhab_db/autogen. I’m not really sure why it wouldn’t detect any data in the bucket. You may also try walking through the process here.

13

Hello Scott
Thank you for your answer. I followed your instruction and also installed yarn although I don’t know what exactly what I’m doing. I’m still getting the same error and I can’t connect to openhab_db/autogen.

Manuel