Please I need some help with influxdb addon DB- I need the total time of a device “on” state per day or month. Using sum(value) but the integer being returned does not make sense. I’m looking to return the total times (in hours or mins) from state - “on”. What unit is this returning? 4.0 for today for example doesn’t make sense. Thanks!
SELECT sum("value") AS "time" FROM "homeassistant"."autogen"."state" WHERE time > now() - 2d AND "entity_id"='51746580840d8e63e756' GROUP BY time(1d) FILL(null)
I didn’t realize you could override the 7 day limit on the default database.
It’s only a handful of entities I want to keep.
I’ll have a look into that thanks.
I was using history stats last week but due to the purging wasn’t really any good to me.
Your system will be slowed down by 180 days data if you use sqllite (the default). You’d be better to use one of the other options, eg mysql, postgresql, mariadb etc.
Made good progress. Created stored procedures and views through mysql workbench. In grafana I could use the call procedure method and create the table. Then render the dashboard url as iframe in a card on lovelace.