I have spent quite a while with the following problem:
We have weather stations spread around the house, they collect different data (temperature, pressure, humidity, etc.) around the house and I would love to display them in my homeassistant frontend as a graph. The stations themself use mqtt to communicate and I collect the data with an external python script to save into a non homeassistant postgres database. I do it this way because I want long term storage for those values without it being purged by the recorder every few days.
Now I know about a few ways to do that:
grafana iframe setup
grafana still image import
still image of a graph created by a script
history (or custom) graph using sql sensor
history (or custom) graph using mqtt sensor
I tried number 1 but regardless of how I set it up, I only got a graph displayed once or twice, the rest of the time I just got to display an error message even though I could access it from a browser of the same machine without problems. So this never worked like I wanted it to
The still image grafana I never tried because I like interactive graphs and I think graphana is overkill for the simple graphing i require. It is also only available in English language and this leaves me as the only member of my family able to actually use it, instead of just looking at a graph I configured.
The approach I am currently experimenting with is saving the data twice, once in homeassistant and once in long time storage. This enables me to display my graph using apexcharts-card but it has disadvantages. I am limited in how far the history goes by the recorder and I have to save the data twice.
So my question is, is there any way, be it custom integration or a python script or something, that enables me to display data from my external database (that is not the recorder database) in my homessistant dashboard that is interactive and does not necessitate saving the data in the recorder database? It feels like I am not the only person that would like to display longterm data.
If I use a command line sensor, will the “result” of that not be saved in the recorder as well? If so, it does not differ from the mqtt or sql sensors. And the kind of data I would need from my sql query would be multiple days worth of timestamp and value pairs, I don’t know how I would even feed that into a graph.
I don’t actually want to display a still image, there are many ways to do that, I am looking for a way to somehow get external data into a graph without saving it in the home assistant database itself.
I know about the sql integration, but as far as I can see, it only queries one value from the external database and then saves it in the homeassistant database as sensor data.
Just to be clear, I am very aware of how things can be done with sql/command line/mqtt sensors, or still images. What I am asking about is a way to feed data not coming from homeassistant into a homeassistant graph or custom card.
Progress on this rough now is an idea where apex charts card data generators will be used to connect to a web service that queries the database and returns the results as json to be used in the data generator. But I don’t know java script at all and my friend that does is still trying to puzzle a snippet together that can be used in the data generator.
It just feels kinda strange to me that no one in this huge community ever had the need to display external data without having to result in still images which are showcased in an official blog post somewhere. There might just be a solution in form of a custom component or display card out there that someone wrote just to fix this issue that never made it into HACS or someone might have come across a solution and never posted it where my google searches could find it. That is what this thread is about.
If you don’t have a solution but an idea that is worth looking into (I have never actually figured out what the python script integration does that exists, maybe someone that did knows if it could be used for something like this) that might just be something that could be tweaked into working, then by all means, please tell me about it. In turn, if I ever find a way to get external data into a graph on my dashboard I will post about it here, hoping other ppl looking for the same thing might find it.
That’s using the REST API from Influxdb, though, not sure if it’s applicable to PostgresQL
Basically, as long as you can sql-guery the data through javascript, it should be doable.
Thanks for that link, that is exactly the direction I was aiming at.
Postgres seems to have a rest api that I can host myself, but since I already wrote a web backend to query my existing database we will use that. I forwarded your link to my friend to help with the java script, he had something similar but longer. Once I got it working as a proof of concept I will post the working query as well as my python web backend here so others can adapt it to use with whatever data they want to display.
I was tinkering with a custom non HA mysql database and how to display the information inside lovelace.
I think I found a pretty usable and easy “solution” relating the backend (querys, saving data) but unfortunately not a perfect solution for the frontende side