InfluxDB to store data in intervals

Configuration
1

Hi
does the InfluxDB integration have an option to store sensor data in intervals and not only on change?

I’m storing some sensor data to influxdb for visualisation in grafana, like the state for my room lights or device tracker state.
In grafana I use polystat- or statusmap-panels to display the data, similar to history-graph in Lovelace, but grafana has better options to change the timeframe to be displayed, custom colouring based on value, …
The query limits the data to the configured timeframe, like for example “last 12 hours”. If there is no data at the start of the interval, grafana displays no data until the first value is found ( for example light was switched on), from this point I can fill an with the fill(previous) option on the query, until the next changed was recorded

For example:
grafik
at 17:37 I restarted the homeassistant service, this writes the current state automatically to influx, prior to 17:37 grafana has no data to display

Am I missing a configuration option or is this something (not yet?) implemented?

kind regards,Armin

1 Like
2

I like the look of statusmap, might use that for my motion sensors if we can fix the issue.

There are a couple of places you can fill previous. In the query setup and in the right side panel. Have you tried both?

3

I think I tried all options.
the problem is that the query is something like
SELECT last("value") FROM "autogen"."state" WHERE ("entity_id" = 'sensor1') AND time >= now() - 30m GROUP BY time(10s) fill(previous)
it returns correct data from the point in time where I have data, but prior to that there is no data displayed.
the data returned by query is
…

1611160640000000000
1611160650000000000 0
1611160660000000000 1

…
After that it is all “1”, until the status changes or the last row is returned
Not the best sample perhaps, I restarted the service at this point, it first records 0 for this time ( guess because the default for the sensor is 0) and then changes to the actual value.

Worst case there is no data at all, because there is no data in the past 30 minutes ( last 30 minutes is the interval I selected in Grafana) and thus the WHERE-Clause time>=now() - 30m can’t be fulfilled. Then the row remains empty. The sample in the screenshot is showing the status for 9 devices in one panel, one row for each device.

I do have a parallel installation with openhab, currently I’m trying to figure out if I want to switch to Home Assistant, because of issues with migrating to the next major version of openhab… different story
There it works, but the influxdb implementation in openhab can be configured like
sensor1 : strategy=everyChange,everyMinute

Size of the database and frequent writing is not really a concern for me, it’s running on a raspberry pi bootet from a SSD.

Armin

4

fill(0) is working for me:

e.g.

SELECT mean("value") FROM "state" WHERE ("entity_id" = 'pir_bathroom') AND time >= now() - 7d GROUP BY time(1h) fill(0)

I only started recording data within the last hour and it has filled the last 7 days:

movement
movement1000×500 31.8 KB

5

Hi, for me it fills in incorrect data.
Sample: at about 5pm local time I switched on lights in a room, the the database 0=off, 1=on

SELECT value FROM "autogen"."state" WHERE ("entity_id" = 'arbeitszimmer') and time >= now() -20m tz('Europe/Berlin')
name: state
time                                value
----                                -----
2021-01-21T17:03:03.038395904+01:00 1

Using a query to show data for the past 20 minutes in 1 minute intervals it does this, first using “mean” and fill(0) like you did it:

SELECT mean(value) FROM "autogen"."state" WHERE ("entity_id" = 'arbeitszimmer') and time >= now() -20m group by time(1m) fill(0) tz('Europe/Berlin')
name: state
time                      mean
----                      ----
2021-01-21T16:51:00+01:00 0
2021-01-21T16:52:00+01:00 0
2021-01-21T16:53:00+01:00 0
2021-01-21T16:54:00+01:00 0
2021-01-21T16:55:00+01:00 0
2021-01-21T16:56:00+01:00 0
2021-01-21T16:57:00+01:00 0
2021-01-21T16:58:00+01:00 0
2021-01-21T16:59:00+01:00 0
2021-01-21T17:00:00+01:00 0
2021-01-21T17:01:00+01:00 0
2021-01-21T17:02:00+01:00 0
2021-01-21T17:03:00+01:00 1
2021-01-21T17:04:00+01:00 0
2021-01-21T17:05:00+01:00 0
2021-01-21T17:06:00+01:00 0
2021-01-21T17:07:00+01:00 0
2021-01-21T17:08:00+01:00 0
2021-01-21T17:09:00+01:00 0
2021-01-21T17:10:00+01:00 0
2021-01-21T17:11:00+01:00 0

before switching on lights the value is 0 ( would be correct), but after the the event it fills all remaining slots with 0 again, however light is still on. In my graphs this means the colour changes to green once, then goes back to red for the rest of the line.

with fill previous the slots after the event are filled with “1” (because it keeps the value), but then slots from the first timestamp until the event are empty:

SELECT mean(value) FROM "autogen"."state" WHERE ("entity_id" = 'arbeitszimmer') and time >= now() -20m group by time(1m) fill(previous) tz('Europe/Berlin')
name: state
time                      mean
----                      ----
2021-01-21T16:57:00+01:00
2021-01-21T16:58:00+01:00
2021-01-21T16:59:00+01:00
2021-01-21T17:00:00+01:00
2021-01-21T17:01:00+01:00
2021-01-21T17:02:00+01:00
2021-01-21T17:03:00+01:00 1
2021-01-21T17:04:00+01:00 1
2021-01-21T17:05:00+01:00 1
2021-01-21T17:06:00+01:00 1
2021-01-21T17:07:00+01:00 1
2021-01-21T17:08:00+01:00 1
2021-01-21T17:09:00+01:00 1
2021-01-21T17:10:00+01:00 1
2021-01-21T17:11:00+01:00 1
2021-01-21T17:12:00+01:00 1
2021-01-21T17:13:00+01:00 1
2021-01-21T17:14:00+01:00 1
2021-01-21T17:15:00+01:00 1
2021-01-21T17:16:00+01:00 1
2021-01-21T17:17:00+01:00 1

worst case, there is no change to the data within the requested timeframe, in this case the resultset is just empty and neither method to fill in values can help:

SELECT mean(value) FROM "autogen"."state" WHERE ("entity_id" = 'arbeitszimmer') and time >= now() -5m group by time(1m) fill(previous) tz('Europe/Berlin')

so far I did not have a solution for me, but … positive side effect, you added the statusmap to your panels :slight_smile:

6

Found something similiar here:

trying to figure out if this (“force_update”) applies to all sensors

Armin

7

Only ones you are able to define an attribute template for unfortunately.

8

You should always be able to wrap a template sensor around any other sensor and add a templated attribute to it, or not?

Sebastian

9

Sure, but that is going to get cumbersome with a lot of entities (binary sensors, lights, switches etc…).

10

Hi
I’ll try with template sensors… but need to read quite a bit of documentation first, it is my 2nd week with Home Assistant.

Armin

11

Hi
at least as a workaround this works for me:

binary_sensor:
  - platform: template
    sensors:
      tbalkontuer:
        value_template: "{{ states('binary_sensor.balkontuer') }}"
        friendly_name: "Template Balkontür"
        device_class: door
        unique_id: tbalkontuer
        attribute_templates:
          update_now: "{{ (now().minute / 5) | round(0) }}"

Gets updated every 5 minutes ( at 03, 08, 13, … due to the rounding), using now() only updates every minute.
Next step is to add the same for other sensors and using only the template_sensors for storage in the influx-database

thanks for the help
Armin

12

Hi
After a bit of work on a boring Saturday afternoon, I created template sensors for all relevant sensors and adjust the panels in Grafana to use the template sensors.
Postive side effect is that I have much better control on what is going to the influx database, id for all relevant sensors ends with “_db” and only these sensors go to the database using “include” with “entity_globs” for the influx configuration.

next and hopefully final step is to exclude the *_db settings from the builtin database, just to avoid duplicates

thanks for the help and pointing me in the right direction
Armin

13

Just a heads up, the latest release of Grafana (7.4.0) breaks the status-map plugin. The developer is working on a fix.

14

Hi, yes, found out this afternoon…
sudo apt-get install grafana=7.3.7 :slight_smile:

Armin

15

Update for the Statusmap Plugin is available from Grafana, updated the plugin with grafana-cli, then updated Grafana itself to 7.4.3.
Looks good so far…

Armin

1 Like
16

Hi
can you share your developments?

17

Hi
What do you mean? Samples for template sensors or a sample for the influxdb configuration in configuration.yaml? A sample for a binary sensor is in one of the posts above, similar for a temperature sensor:

bme680_temperature_db:
  value_template: "{{ states('sensor.bme680_temperature') }}"
  friendly_name: "Sensor BME680"
  unique_id: bme680_temperature_db
  device_class: temperature
  attribute_templates:
    update_now: "{{ (now().minute / 5) | round(0) }}"

takes the existing entity “sensor.bme680_temperature” and creates an addtional sensor with name “sensor.bme680_temperature_db” including the additional attribute to change the sensor every 5 minutes. This triggers the influxdb integration to write a value to the database.

this is the configuration for influx:

influxdb:
  host: 127.0.0.1
  port: 8086
  database: homeassistant
  username: influxuser
  password: influxpassword
  ssl: false
  verify_ssl: false
  max_retries: 3
  default_measurement: state
  include:
    entity_globs:
      - binary_sensor.*_db
      - sensor.*_db
      - sensor.*_db_2
  component_config_glob:
    sensor.*humidity*:
      override_measurement: humidity
    sensor.*temperature*:
      override_measurement: temperature
    sensor.*valve*:
      override_measurement: valve
  tags:
    instance: prod
    source: hass

the “include” defines the entities to be stored to influxdb. I’m storing my template sensors ( all ending with “_db” or “_db_2”) only, all other entities are not transferred to influxdb.
the “component_config_glob” is a workaround for a feature in the influxdb-integration I didn’t really like. By default it was creating measurements where the unit of the entity was the name of the measurement (for example “°C” for temperature entities). I didn’t like that, here I’m setting for example “temperature” as the name ( based on the name of the entity)

Finally I have this “recorder” configuration in configuration.yaml

recorder:
  exclude:
    entity_globs:
      - binary_sensor.*_db
      - sensor.*_db
      - sensor.*_db_2

This excludes the entities I’m writing to influxdb from being written to the standard homeassistant database

Armin

4 Likes