cleanup Influxdb

[swa72]

https://community.home-assistant.io/t/influxdb-removing-or-deleting-data/292637 https://dummylabs.com/post/2019-01-13-influxdb-part1/ https://dummylabs.com/post/2019-05-28-influxdb-part2

InfluxDB | Explore

SHOW DATABASES

SHOW MEASUREMENTS ON "homeassistant" -> gives m, min, W, etc.

SHOW TAG KEYS ON "homeassistant" FROM "W"

SHOW TAG VALUES ON "homeassistant" FROM "W" WITH KEY = "entity_id"

USE "homeassistant"; DELETE FROM "W" WHERE "entity_id" = 'smlreaderpower' -> worked

USE "homeassistant"; DELETE FROM "m³" WHERE "entity_id" = 'gasverbrauch_monthly' -> worked

restart HA

[swa72]

grafana influx

[swa72]

SHOW MEASUREMENTS ON "homeassistant" -> gives m, min, W, etc.

SHOW TAG KEYS ON "homeassistant" FROM "m³" -> gives domain or entity_id

SHOW TAG VALUES ON "homeassistant" FROM "m³" WITH KEY = "entity_id" --> gives gasverbrauch, gasverbrauch_daily usw.

SHOW SERIES ON "homeassistant" -> %,domain=sensor,entity_id=multi_sensor5_battery_level -> kWh,domain=sensor,entity_id=solar_energy_total

USE "homeassistant"; SELECT "entity_id","value" FROM "m³" -> alle Tag values aus m3 und alle Daten

USE "homeassistant"; SELECT "entity_id","value" FROM "m³" WHERE "entity_id" = 'gasverbrauch' -> nur zwei spalten, gefiltert auf entity_id gasverbrauch

USE "homeassistant"; SELECT "value" FROM "m³" WHERE "entity_id" = 'gasverbrauch' --> nur eine Spalte

USE "homeassistant"; SELECT CUMULATIVE_SUM(SUM(nnd)) FROM ( SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd FROM "m³" WHERE ("entity_id" = 'gasverbrauch') AND time >= 1608063751046ms and time <= now() ) GROUP BY time(1d) --> macht das was grafana auch macht. yessir.

USE "homeassistant"; SELECT CUMULATIVE_SUM(SUM(nnd)) into "cqgaszaehler" FROM ( SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd FROM "m³" WHERE ("entity_id" = 'gasverbrauch') AND time >= 1608063751046ms and time <= now() ) GROUP BY time(1d) --> stores the query into "cqgaszaehler"

USE "homeassistant"; SELECT * FROM "cqgaszaehler" shows the stuff

USE "homeassistant"; SELECT DIFFERENCE(*) FROM "cqgaszaehler"

continuous query ..........

CREATE CONTINUOUS QUERY "cq_gas" ON "homeassistant" BEGIN SELECT CUMULATIVE_SUM(SUM(nnd)) into "cqgaszaehler" FROM ( SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd FROM "m³" WHERE ("entity_id" = 'gasverbrauch') AND time >= 1608063751046ms and time <= now() ) GROUP BY time(1d) END

[swa72]

Instead of cumsum like in the above example try https://stackoverflow.com/questions/53711110/make-influxdb-grafana-cumulative-function-that-resets-daily-sawtooth-graph

[swa72]

Check out https://community.home-assistant.io/t/energy-management-in-home-assistant/326854/1127

[swa72]

grafana query ...

SELECT spread("value") FROM "kWh" WHERE ("entity_id" = 'shellys_keller_entfeuchter_energy') AND $timeFilter GROUP BY time(1d) fill(null)

for two values that spread too much (sensor restart?) do the following:

SHOW TAG VALUES ON "homeassistant" FROM "kWh" WITH KEY = "entity_id"

lists u.a. shellys_keller_entfeuchter_energy

USE "homeassistant"; SELECT "value" FROM "kWh" WHERE "entity_id" = 'shellys_keller_entfeuchter_energy' AND time > '2022-03-10' AND time < '2022-03-13'

closer

USE "homeassistant"; SELECT "value" FROM "kWh" WHERE "entity_id" = 'shellys_keller_entfeuchter_energy' AND time > '2022-03-11 00:00:00' AND time < '2022-03-12 00:00:00'

then do

USE "homeassistant"; DELETE FROM "kWh" WHERE "entity_id" = 'shellys_keller_entfeuchter_energy' AND time > '2022-03-11 00:00:00' AND time < '2022-03-12 00:00:00''

to remove values that have spread too much on the same day.

[swa72]

open influxdb

problem date 23.6.2022

explore

USE "homeassistant"; SELECT "value" FROM "kWh" WHERE "entity_id" = 'shellys_keller_entfeuchter_energy' AND time > '2022-03-11 00:00:00' AND time < '2022-03-12 00:00:00'

dont forget to change time top right to UTC

then delete with

USE "homeassistant"; DELETE FROM "kWh" WHERE "entity_id" = 'shellys_keller_entfeuchter_energy' AND time > '2022-03-11 00:00:00' AND time < '2022-03-12 00:00:00''

[swa72]

USE "homeassistant"; DROP series FROM "state" WHERE "entity_id" = 'accuweather' -> removes accuweather

SHOW SERIES ON "homeassistant" WHERE "entity_id" =~ /accu*/

shows all series (ready to drop) that contains "accu"

USE "homeassistant"; DROP series WHERE "entity_id" =~ /accu*/

drops all series with starts with accu regardless of FROM