After browsing trough the “Your topic is similar to…” I couldn’t really get a solution for my problem.
Which is:
I would like to store long term historical data for my thermostat heating on and then access it inside HA, preferably through Apex charts and other HA plugins.
With “platform: history_stats” as far as I can see only 7 days could be recorded/tracked and if you have cleared your browser cache, everything else is lost.
So I started to look for configuration possibilities which I did not found. Searching for alternative solutions or external plugins I’ve come to InfluxDB, where I can transfer data back to HA with “platform: influxdb”.
However when I add back my old measurement data, as of now we are talking about from november to december heating on, it is not visible inside HA. It is however visible inside InfluxDB and if i install Grafana then also inside Grafana.
Any ideas how I could resolve that (avoiding Grafana)?
My Influx sensor config:
- platform: influxdb
host: homeassistant.local
username: !secret influxdb_username
password: !secret influxdb_password
queries:
- name: heating_on_testv2
database: ha
measurement: h
field: "value"
unit_of_measurement: h
where: ' "entity_id" = ''heating_on_daily'' '
My current implementation with the built-in “platform: history_stats”, so you can have a feeling what I would like to achieve, but with way longer history than the 7days (preferably years, however displaying only a reasonable amount at a time as HA Apex chart and may access older data when available as a comparison, like last year same time vs. actual year, maybe same with average outside / inside temperature).
Sorry for the late reply, but after NYE i have time only now to look into it, unfortunately could not make it working.
replaced sensor name
replaced database name and address (do i need to provide passowrd somewhere? influx db is on the same physical machine as HA, running HA as HA OS installation on an SSD)
modified the original query in the example to fit my needs at least for testing purposes.
All I get is a blank chart with “loading”.
I see some “console.log(start)”, but where do i find these logs?
Thank You!
type: custom:apexcharts-card
graph_span: 7d
header:
show: true
title: ApexCharts-Card
show_states: true
colorize_states: true
series:
- entity: sensor.heating_on_daily
data_generator: |
console.log(start);
var params = new URLSearchParams({
pretty: true,
db: "ha",
q: "SELECT last(\"value\") FROM \"h\" WHERE \"entity_id\"='heating_on_daily' AND time >= '" + start.toISOString() + "' AND time <= '" + end.toISOString() + "' GROUP BY time(60m) FILL(none)"
});
var myInit = { method: 'GET',
headers: {
'Accept': 'application/json',
},
mode: 'cors',
cache: 'default'
};
const request = async () => {
var result = [];
const response = await fetch('http://localhost:8086/query?' + params, myInit)
const json = await response.json();
if (json["results"] && json["results"][0] && json["results"][0]["series"] && json["results"][0]["series"][0] && json["results"][0]["series"][0]["values"]) {
for(var val of json["results"][0]["series"][0]["values"]) {
result.push([new Date(val[0]), val[1]]);
}
} else {
console.log("error: " + json)
}
return result;
}
return request();
Grafana was a good idea, query wasnt good enough, so i made a simplified that should do the trick for now:
SELECT last("value") FROM "h" WHERE ("entity_id" = 'heating_on_daily') GROUP BY time(1d) fill(none) tz('CET')
which looks like this inside HA: q: "SELECT last(\"value\") FROM \"h\" WHERE (\"entity_id\" = 'heating_on_daily') GROUP BY time(1d) fill(none) tz('CET')"
it does not have the start/end section of the original PoC work.
I do not know which version of influxdb I have, i got it from addon store inside home assistant (not from HACS), it says 4.3 which is definitely not what probably you are looking for, im sure it supports API interface 1.x and 2.x too since inside Influxdb own interface i can switch between InfluxQL and Flux interface.
The only difference that inside Influxdb I have to speciy the database and the default pocket too.
Tried both variant in Apex, unfortunately no results.
Looks and sounds like you’re using the InfluxDB addon so therefore you’re using v1.8.10 of InfluxDB since that’s what it was updated to in the latest release of the addon. And yes 1.8 does include Influx 2.0 compatibility endpoints for forward compatibility as noted in their release notes. So it supports InfluxQL fully and has a preliminary, read-only version of the Flux APIs.
History stats can only see as far back as you’ve allowed recorder to keep data. By default recorder purges data that is more then 10 days old. That can be set in config using the purge_keep_days option.
That being said, setting that to years is generally not a good idea as your DB will get way too big. You’re right to look at Influx for that although the new Long-Term Storage features do provide hope that there will be a native solution to this problem at some point without requiring InfluxDB or Grafana.
I think you’re right but just FYI the doc to look at for v1.8 is here. And as it lists here you can either provide a basic auth header or just put the username and password in query string parameters u and p.
Also just as an aside, addons can talk to each other directly using their slug. So in here for example:
You can just put http://a0d7b954-influxdb:8086 as the URL. This way grafana will talk directly to influx without going to your router. Same Home Assistant since that also runs inside the same docker network as the addons and supervisor. This also means that you don’t have to expose the 8086 port on your host since the only things talking to the InfluxDB Addon are local.
Well unless you have an external service which talks to InfluxDB on a different machine in your network. Then you do still have to expose the port on the host.
Either way you shouldn’t use homeassistant.local there. Use either localhost, 127.0.0.1 or a0d7b954-influxdb. homeassistant.local is an mDNS address and I’ve personally found using it outside of a browser can be kind of a crapshoot. I don’t fully understand the issues but I’ll just say in my personal testing integrating all my stuff trying to use the homeassistant.local URL for integrations between systems and services was a bad idea and led to a lot of communication issues. Adding a DNS rewrite for it in my local DNS server helped but honestly it was much simpler to use either the IP or a hostname.
EDIT: Oh hang on a minute. i just realized that JS you wrote here:
That runs in the browser doesn’t it? Can you open up browser dev tools when you see the “Loading…”? What does the network tab say, is it trying to connect to http://localhost:8086 from your browser? Because that’s not going to work, your laptop/phone isn’t running influx.
EDIT AGAIN: Duh the server is python, that is obviously running in the browser. You’ve also got cors on there. So yea there’s a few things you need to look at then:
You can’t use localhost there, that doesn’t make sense. You also might not be able to use homeassistant.local as that URL only works from inside your network (unless you have no external access to HA). A better option is to set the URL to this: "http://" + window.location.hostname + ":8086?query" since that will use the domain of the window.
Is CORs working? For CORs to work InfluxDB has to return headers which say access from any domain is allowed or access from the domain you are on is allowed. Is that happening?
So what I have done:
Disabled auth in Influx config, just to make things simpler → Grafana works fine
Replaced homeassistant.local references with http://a0d7b954-influxdb:8086 → Grafana works fine
So I’m a bit confused now. I was expecting a JSON response. You’re seeing what looks like the response for loading an panel in the HA frontend. I’m pretty sure that’s not the ajax call at all.
In this screenshot it looks like you have filtered the network calls to ones that contain influx in chrome dev tools. That’s actually not correct, you should type query. Chrome is confusing here, it names the requests based on the last resource path in the URL. So when you hit a URL like http://a0d7b954-influxdb:8086/query the name in that list will be query. And that search box at the top only searches on name so a search for influx won’t show that api call at all.
That being said your screenshot does highlight an issue with the code I shared. I assumed it was always http which was silly of me, if you allow external access it would be https. Please change fetch code to this:
I copied and pasted what you had and tweaked the protocol without really looking closely at it. You put the question mark in the wrong place. Now we can try it.
Ah duh, I’m being dumb again. Ok so I assume you have port forwarded 8086 from your router to HA which means https://xxxxxxxxxx:8086 talks directly to your influx add-on. But as you showed in the config above, SSL is disabled for the influx addon so that won’t work. But you also can’t talk to it using HTTP since that’s a mixed content error. So you have to do one of the following:
Enable HTTPS for the InfluxDB add-on. Note that this will enable it always (LAN and external access) so that might break the dashboard when using the LAN URL (homeassistant.local) unless you are using https there as well.
Drop the window.location stuff and just use http://homeassistant.local. Your dashboard won’t work when you are outside your network but should be when on LAN.
Use a reverse proxy like NGinx Proxy Manager so external access to Influx and HA is via HTTPS and internal access is via HTTP instead of port forwarding individual ports. But you’ll need to tweak the fetch logic then based on the subdomain you pick for influx.
Sorry for the late anser, unfortunately could not solve it, gave up for now as there are other things to take care of, but If i manage to solve it once, i will post the solution.
