Homeassistant + Influxdb: is it possible to select a different Retention policy for a couple of sensors?

Configuration
1

Hi to all,
I have many sensors that write into Influxdb; it uses a default retention policy of 1 week.

I have a couple of sensors (gas and water counters) that I want they use another infinite retention policy, called “infinite”.

Is it possible on configuration of HomeAssistant select different retention policy OR can someone help me to “copy” them from “one_week” retention to “infinite” retention?

At the moment I have these CQs:

CREATE CONTINUOUS QUERY cq_water ON homeassistant BEGIN SELECT value INTO homeassistant.infinite."l" FROM homeassistant.one_week."l" GROUP BY time(1h), entity_id fill(previous) END

CREATE CONTINUOUS QUERY cq_gas ON homeassistant BEGIN SELECT value INTO homeassistant.infinite."m3" FROM homeassistant.one_week."m3" GROUP BY time(1h), entity_id fill(previous) END

But infinite.l and infinite.m3 are empties (but not the one_week, I can show data in Grafana also). Thank you!

2

I dont know if this helps but If you are using node red, you can use the influxdb node to send date to any number of databases which can have any retention policy.

3

Thank you, but I dont use node red…

4

@sineverba @sheminasalam It is possible to define multiple retention policies for the same database in influxdb, please see a very good tutorial of influxdb retention policies… but have not been able to do it in HA

I also really need to define a different retention policy for different sensors

A feature request has been submitted

5

What’s “I” and “m3”? You have defined separate measurements for water and gas which you use in the HA integration to add data to correct measurement? That’s what “I” and “m3” is?

If data is correctly split in measurements I see no reason it shouldn’t work. My CQ’s work as intended. My config is everything one week in HA, all data transferred to InfluxDB (opt-in) one month and downsampled data moved into infinite through CQ’s.

Some possibly relevant lines from my integration (as I’m not sure what’s in m3):

influxdb:
(...)
  tags_attributes:
    - friendly_name
    - hidden
  include:
    entity_globs:
      - sensor.*temperatur*
(...)
  component_config_glob:
    sensor.*temperatur*:
      override_measurement: temperatur
(...)

CQ specifically for “temperatur” measurement listed above:

CREATE CONTINUOUS QUERY cq_1t_temperatur ON home_assistant BEGIN SELECT mean(value) AS value INTO home_assistant.uendelig.temperatur FROM home_assistant.autogen.temperatur GROUP BY time(1h), entity_id, friendly_name fill(none) END
1 Like
6

For those still interested, this is a way to do it : InfluxDB and Home Assistant (Part 2) | dummylabs.com

2 Likes
7

I am tryng to do similar thing, but your link does not work anymore, can you give me the info on how you did it?

thanks

8

Hi, sorry about that. I do not use influxdb anymore and I don’t remember how I did it…
Best I can find is this post : InfluxDB Data Retention. How to down-sample data and keep it for… | by Shawn Stafford | Towards Data Science
The steps are not all the same but basically create a second database in influxdb with a different retention policy, then create a continuous query to update that second database with only the sensors you chose.
That’s all I got, hope it’ll help

Edit : this one seems to explain things quite well: Retention policies and continuous queries made simple - Tutorials and Guides - Icinga Community

1 Like
9

This is something I have been meaning to do for quite some time. I want to keep a years worth of data then down-sample to hourly averages and keep for 4 more years.

Thanks for the links. I think I have the gist of it now and will give it a go this weekend after making a backup.

One question remains. How do I use the downsampled data from the second database in Grafana?

Is it possible to adjust my existing dashboards to use both databases, for when I look back further than a year ago?

10

I used grafana to exploit both databases, so I don’t know how to do this directly in the dashboards…
Should be possible to define sensors from influxdb though. See InfluxDB - Home Assistant

11

I have this exact setup with my dashboards, and have current and long term avg items on the page, however I have not yet figured out a way to overlay both databases within the same graph/visualization

12

Yeah I’ve just been looking at the data connection in grafana:

IMG_0865
IMG_08651920×1452 86 KB

So that note looks promising. Specifying the one db does not exclude others.

So a graph query like:

SELECT last("value") FROM "default"."lx" WHERE ("entity_id" = 'outside_light_level') AND $timeFilter GROUP BY time($__interval) fill(none) tz('Australia/Hobart')

Probably needs to change to:

SELECT last("value") FROM "homeassistant"."lx" OR "downsampled"."lx"  WHERE ("entity_id" = 'outside_light_level') AND $timeFilter GROUP BY time($__interval) fill(none) tz('Australia/Hobart')

if I named my second database “downsampled”.

1 Like
13

oh interesting, I will have to try out something like that when I get off work tonight

1 Like