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?
@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.
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)
- 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()
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?