Help with query in InfluxDB sensor component (0.32)

Configuration
1

I just discovered https://home-assistant.io/components/sensor.influxdb/ which is nice, as i log my smartmeter to InfluxDB and create graphs of it with Grafana. But having the actual power consumption / daily total today so far in HASS would be nice.

I am trying to set it up, this is the query that gives me the result that i want to have: SELECT ("+P") * 1000 FROM "raw"."smartmeter" order by time DESC limit 1

But how do i translate it to a config that i can put in sensors.yaml ? I now have this:

- platform: influxdb
  host: 192.168.4.10
  username: admin
  password: admin
  queries:
    - name: Huidig verbruik
      unit_of_measurement: W
      value_template: '{{ value }}'
      group_function: last
      measurement: '"raw"."smartmeter"'
      field: '"+P"'
      database: p1smartmeter
```

But that gives an error (`The following components and platforms could not be set up: * [sensor.influxdb](https://home-assistant.io/components/sensor.influxdb/) Please check your config`)

Who could give me some pointers on how to format this?
2

The where close is required in the sensor. It probably could (should) be made optional.
You can try this :

queries:
    - name: Huidig verbruik
      unit_of_measurement: W
      value_template: '{{ value }}'
      group_function: last
      measurement: '"raw"."smartmeter"'
      field: '"+P"'
      database: p1smartmeter
      where: 'time < now()'

The where close do nothing in this case. If you know that you are interested in last 24h values only, you can have

where: 'time > now() - 24h'

To have the equivalent of

SELECT ("+P")  * 1000

you can use

value_template: '{{ value * 1000 }}'
3

@bestlibre Thanks, that got me a bit further! This is the result:

  queries:
    - name: Huidig verbruik
      unit_of_measurement: W
      value_template: '{{ value | float * 1000 | round(1) }}'
      group_function: last
      measurement: '"raw"."smartmeter"'
      field: '"+P"'
      database: p1smartmeter
      where: 'time < now()'

I also have a query that uses a GROUP BY, this is the query SELECT mean("+T_spread") FROM "smartmeter_daily" WHERE $timeFilter GROUP BY time(1d). Do you know how i can write that GROUP BY ?

And do you know if i can use scan_interval: 10 with the InfluxDB component? The database gets new values every 10 seconds. The default interval seems a bit slow.

1 Like
4

The problem with the group by is that it will output multiple points, which is not supported right now. You can have it in the where clause, but only the first point will be keep.

I can try to support multiple points in conjunction with group by, but I’m not sure how to interpret the results. Multiple sensors ? In this case, how should I choose their names?

If you have a limited and known list of “group by” results, an alternative would be to create one query for each.

I don’t know the answer for the interval. I will check ASAP, probably tomorrow morning (GMT +1 here)

5

You can try to override the default scan interval with scan_interval: 10, it should work.

6

I’m trying to implement this querie but I get an error:

'retention policy not found: raw'

Any ideas ?

7

Ok, fixed that. Now I’m trying to adjust the query.

My influxdb:

$ influx -execute 'SHOW SERIES' -database=home_assistant
key
---
kW,domain=sensor,entity_id=power_consumption
kWh,domain=sensor,entity_id=power_consumption_normal
m3,domain=sensor,entity_id=gas_consumption

Sensors:

  - name: kWh vandaag
    unit_of_measurement: kWh
    value_template: '{{ value | round(2) }}'
    group_function: last
    measurement: '"power_consumption"'
    field: '"+T_spread"'
    database: home_assistant
    where: 'time < now()'
  - name: Gas vandaag
    unit_of_measurement: m3
    value_template: '{{ value | round(2) }}'
    group_function: last
    measurement: '"gas_consumption"'
    field: '"G_spread"'
    database: home_assistant
    where: 'time < now()'

I tried adjusting the measurement values to power_consumption and gas_consumption but that gives me an error:

Running query: select last("+T_spread") as value from "power_consumption" where time < now()
16-12-23 10:02:54 homeassistant.components.sensor.influxdb: Query returned no points, sensor state set to UNKNOWN : select last("+T_spread") as value from "power_consumption" where time < now()
16-12-23 10:02:54 homeassistant.components.sensor.influxdb: Running query: select last("G_spread") as value from "gas_consumption" where time < now()
16-12-23 10:02:54 homeassistant.components.sensor.influxdb: Query returned no points, sensor state set to UNKNOWN : select last("G_spread") as value from "gas_consumption" where time < now()

I’m using this as an example: https://bitbucket.org/frankiepankie/home-assistant/src

2 Likes
8

Hi Piotr, Looks like you tried exactly what I try to achieve right now. With the same results as in your post. Did you fix your values in the sensors? How did you do this?