InfluxDB select to Home assistant sensor

Configuration
,
1

Hi all, I’ve been working for a few months on this need.

Need: I want to have a sensor, representing the last 24 hours of rain accumulation in mm. I would use this sensor to control my automation that would be blocking (not started) the valves controlling the irrigation of the garden. For example, if the last 24 hours of precipitation is above 25mm, don’t start the irrigation automation.

For that:

  1. I added the AccuWeather API to HomeAssistant that sends this data to influxDB. That part works fine.

  2. I did a select in influxDB to sum the last 24 hours of data. That part works fine.

SELECT sum(“value”) as “Total_Rain” FROM “Semis_Garage”.“autogen”.“mm” WHERE time>now()-1d

For example, this morning, it shows 19.2 mm for the last 24h, it rained yesterday. Part 3 is commented out for now.

  1. Then I added in HomeAssistant config, an influxdb sensor, to have it available in HomeAssistant automation (maybe there’s another way?)

This is where it’s not working as expected!

  • platform: influxdb
    host: localhost
    username: homeassistant
    password: “PASSWORD”
    queries:
    • name: TotalRainPast24h
      unit_of_measurement: mm
      value_template: ‘{{ value | round(1) }}’
      where: ‘time > now() -1d’
      group_function: sum
      measurement: Semis_Garage.autogen.mm
      field: “value”
      database: Semis_Garage

After a restart, I added this sensor to the dashboard to test.

The first display of the number of mm of rain for the past 24 hours worked fine, but then, the next update after 60 seconds (I assume this is the default refresh rate of this sensor), the value was double! Then every 60 seconds or so, the value doubled. After a while, I was at 33 digits mm of rain!!! enough to fill the solar system :wink:

So the question is, what did I do wrong?

The funny thing is, when I went back to the influx DB screen and ran the same select, the value was doubled (x time). So it seems the sensors from HomeAssistant is writing back to the DB?

Thanks for any input.

Simon

2

This might be it

3

Yes the function sum is there to sum the last 24 hours of rain data.

The question is: Why is the select in the yaml config writing back (or something similar) to the DB?

I expect the select to select the influxDB and feed a sensor, not to write back to the influxDB.

Or maybe I don’t get how select in HA works?

Thanks for the reply.

4

HA is writing to it’s DB for recorder and history, so if you make select on those then you might get a loop.

5

Ok, here’s my understanding

  1. API (accuweather) > Internal DB of HA
  2. Internal DB of HA > InfluxDB
  3. Select influx DB > Sensor in HA
  4. Sensor > Internal DB of HA
    ** Not to back to InfluxDB

My select is on influxDB, not the internal DB. Unsure why it would be a loop?

Any thoughts? Thanks!

6

Internal DB as you call it can be MariaDB or InfluxDB or maybe some other solution.
What are you HA set up to use?

7

That’s a very good question, unsure how to check that.

Basically, I installed a virtual machine HASOS on virtual box.

After the initial setup, I added InfluxDB.

I would love to know how to find more about this, but after 15min pocking around I don’t find more.

8

@WallyR Just found out more about the Database

According to this: Database - Home Assistant

I see that I have an SQL Lite in /root/config/home-assistant_v2.db

Therefore, the default database used is SQLite
And I added InfluxDB on top of that

My understanding is good then?

  1. API (accuweather) > SQLite DB of HA
  2. SQLite DB of HA > InfluxDB
  3. Select influx DB > Sensor in HA
  4. Sensor > Internal DB of HA
9

Do you have lines like the ones below in you configuration.yaml?

influxdb:
  host: a0d7b954-influxdb
  port: 8086
  database: homeassistant
  username: MyUserName
  password: MyPassWord
  max_retries: 3
  default_measurement: state
10

Yep

influxdb:

host: a0d7b954-influxdb
port: 8086
database: Semis_Garage
username: homeassistant
password: “pass”
max_retries: 3
default_measurement: state

Plus I confirmed I have the SQLite with active data in it.

So 2 DB.