BTW: Another nice side effect for me is that I can now use the Timescale Docker container for both, the HA recorder and the long time state storage (Each with its own database of course).
This saves me the separate Postgress and Influxdb containers and I only have to maintain one container now.
Would it be possible to take the data that is being logged and back feed it into a sql sensor in order to graph it with the mini graph card for example?
select time_bucket('1 day', time)::date as ts,
round( cast( (cast(last(state, time) as float) - cast(first(state, time) as float)) as numeric), 4) as kwh
from ltss
where entity_id = 'sensor.main_totalenergy'
and time::date >= cast(date_trunc('month', current_date) as date)
group by ts;
This returns date’s and the daily consumption and would love to be able load it back into a graph.
@Tjeerd , this question is not really directly related to the LTSS component but here is my take on it:
The SQL sensor is 100% compatible with LTSS but is implemented as a “typical” HA sensor integration (i.e expects a single value on each update and then stores it with the regular recorder instance etc. Not really suitable for this kind of task where you want to show a history of another entity (regardless of db)
The mini-graph lovelace card depends on the Home Assistant API. More specifically the history sub-api which is integrated with the regular history/recorder component of HA only. Hence, no luck here unless HA makes the history component a bit more modular (i.e. accepts other storage solutions and not only the regular recorder component).
My preferred choice is to use grafana, which makes it really easy to graph things like your example but is a little more complicated to integrate with HA frontend, a nice guide can be found here (just ignore all the talk about influxdb and replace those step with LTSS instead.
Hope this gives some ideas about your possibilities!
@Tjeerd another option is to write a script that is run daily and generates a custom output/plot. See this thread. You might also want to checkout the SMA filter.
Cheers
I went a different route for now. Although Grafana is very flexible, it’s a bit of a pain to get some main line stats back into HA. I’ve setup a SQL Sensor which logs the min / max for the current day. No history, but as it runs it will build it. The value of the sensor is going back into the recorder component and I can use in the mini graph card for now
So for the choice of /your/data/dir :
e.g. would it be better to: 1.) bind it to the same dir as Home Assistant
(in my case generic linux install of Hassio): -v /usr/share/hassio/homeassistant:/var/lib/postgresql/data
OR 2.) use default Docker volume location: -v chooseVolumeName:/var/lib/postgresql/data
OR 3.) somewhere else
?
I would be glad to receive any help on this…
Thanks!
Even if it is basically feasible, it is probably not a wise decision to run a relational database to back up long term data on a Raspberry Pi.
You might encounter performance issues.
Here is a very interesting blog article about the Postgress Performance on a Rasberry Pi 3: https://blog.rustprooflabs.com/2019/04/postgresql-pgbench-raspberry-pi
It is also questionable whether the space on a Raspberyy Pi is sufficient for this.
Just to give you a rough idea: I currently have about 350 entities and sensors in Home Assistant being tracked. My LTSS Postgres database has been running since the beginning of the year and already occupies about 400MB:
Thank you for answer. So i need to find different solution for that what i want - store at least min, max, average for each hour for last 3 months, and then for data older than 3 months values for each day for each sensor recorded by recorder. Any idea?
The timescaledb-postgis image you linked in your github doesn’t support raspberry pis/arm7v32, I created my own container here, in case anybody wants to use it.
For your feature request, please also put this in the issue tracker on the github repo. Unfortunately I have no previous experience with HACS and dont use it myself. I you feel up for the challenge I am happy to accept a pull request for this.
Yes, I also think the error message is related to the current changes how translations are handled now.
Regarding HACS support, I just thought it would help ltss to be known to a wider audience and make it easier to track features and fixes.
I don’t know much about the conditions myself, but it seems you’ll have to restructure your repo a bit for that:
I’ll see to it that I create a track for you for both things on GitHub. If you don’t mind, maybe I would really read in and send you a pr for HACS support