Converting query to use with influxdb sensor

Hi all,

I’m trying to use the influxdb sensor for the first time, to work out the temperature change rate per hour. I have set up a query in Grafana which appears to work, but I am struggling to convert it into a suitable configuration in HA.

SELECT difference(mean("Temperature")) FROM "°C" WHERE ("entity_id" = 'living_room_temperature') AND time >= now() - 24h GROUP BY time(1h)

I have my sensors in a separate directory, and the influxdb.yaml sensor file I have tried is

- platform: influxdb
  host: 192.168.xxx.xxx
  port: xxxx
  username: xxxxxxx
  password: xxxxxxx
  queries:
  - name: C / hr
    unit_of_measurement: °C
    group_function: difference
    field: 'mean("Temperature")'
    measurement: '"°C"'
    where: '"entity_id" = ''living_room_temperature'' AND time >= now() - 24h GROUP BY time(1h)"'
    database: db_homeautomation

I think the issue is related to the difference(mean(“Temperature”)) part, as I couldn’t find any examples of nested functions so am not clear how to configure it.

Of course I may have gone about this completely wrong - just want to show the rate of change of temperature per hour.

Any guidance is welcome!

Hi @davefi,

this is interesting.
I have no insights to offer, sorry, just wondered if you got anywhere. I tried an Influxdb nested function before and couldn’t get it to work either. Do you get any useful info in the logs?

Hey @manu, no I did not manage to get it working, but to be honest I did not continue to play around with it after I posted! I may have another try this weekend, maybe as a first step get just a simple sensor working, then see if I can work out the rest. I did check the logs and I can’t recall the error off hand, but I don’t think it was anything particularly helpful in the sense that it did not give me any ideas of a possible solution.

Assuming I have time this weekend, I will come back and update this thread.

Thanks for your interest!

Wouldn’t the derivative group function work?

https://docs.influxdata.com/influxdb/v1.5/query_language/functions/#derivative

Did you ever fix this? I’ve got the same problem…

I got something similair

platform: influxdb
host: 192.168.1.75
scan_interval: 10
queries:

  • name: totalkwhidag
    unit_of_measurement: kWh
    where: ‘“domain” = ‘‘sensor’’ and “entity_id” = ‘‘totalkwh’’ time = today()’
    measurement: ‘“kWh”’
    field: ‘“value”’
    group_function: last
    database: homeassistant

and my query is:
SELECT difference(last(“value”)) FROM “kWh” WHERE (“entity_id” = ‘totalkwh’) AND $timeFilter GROUP BY time(1d) fill(null)

I keep getting nr 1 only, something terribly wrong :slight_smile:

Anyone with any success on this?