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)
Can you configure history stats to pull from influxdb or is it just for the local sql default database that has only 7 days of data?
I don’t think so.
It has however much data you configure it to keep.
Looking back at your original question, a time is usually stored as a unix time stamp, but a period of time is usually in seconds.
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.
So on the yaml I only need to add this with the entities as required? The default purge time will be overridden?
recorder:
purge_keep_days: 180
I guess I could set up sql server in another container and use that as I’m familiar with sql
I’ll try the default first
I see history stats is quite limited
I’m looking to do further calculations on the values extracted.
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.
Got Mariadb set up and data is coming in.
But I can’t seem to connect from sql workbench for example.
Cannot connect from grafana either.
Using 192.168.1.26:3306.
Set the username and password in the addon config settings.
What am I missing? I’d like to use the workbench to query first and see what I can add the the sensor yaml first.
I don’t know about sql workbench or gafana, I have not used either.
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.
