Thanks for putting in this work!
I’ve gone a different approach - I’m using PyScript and this script to pull the data in as a sensor (excluding them from my recorder & influx databases)
Updates daily via an Automation:
1 Like
This is amazing! Do you guys have anything like this for influxdb 2?
They are making it really hard to output json with flux
I havent made the jump to InfluxDB 2 yet - I was considering it, but then discovered my Influx database has become a bit unwieldy in size and I want to try and strip out some of the crap before converting first.
1 Like
Everything is hard with flux 
I think I’ll never take the plunge to 2.x (unless they bring back influxql). Too hard for me to wrap my head around it…
2 Likes
THANK YOU @koying!
I hate grafana and influxdb2 which are clunky and counterintuitive. Now using influxdb 1.8.10 
compressed version
let params = new URLSearchParams({
db: "home_assistant",
q: 'SELECT "value" FROM "kg" WHERE "entity_id" = \'mattias_weight\' AND time < now()'
}),
request = async () => {
let a = [],
r = await fetch("http://192.168.1.241:8086/query?" + params),
j = await r.json();
for (let r of j.results[0].series[0].values) a.push([new Date(r[0]), r[1]]);
return a
};
return request()
I really like the approach of bacco007! If you like pls have a look at
GitHub - mf42/influxdb2-to-entity where I tried to implement this is for InfluxDB 2 (and AppDaemon).
This is my first public GitHub repo, so please don’t be too hard…
1 Like
I’m not able to get this working. Just stuck with “loading…”. Would somebody be able to pin point where it goes wrong.
I’ve installed the patched version of ApexChart Card (https://nextcloud.semperpax.be/s/D3YLw2MmPLwqoHK/download/apexcharts-card.js) .
In chart
type: custom:apexcharts-card
header:
show: true
title: ApexCharts-Card
show_states: true
colorize_states: true
series:
entity: sensor.vallox_outdoor_air
data_generator: |
console.log(start);
var params = new URLSearchParams({
pretty: true,
db: “homeassistant”,
q: “SELECT mean("value") FROM "autogen"."°C" WHERE ("entity_id" = ‘vallox_outdoor_air’) GROUP BY time(1h) fill(none)”
});var myInit = { method: ‘GET’,
headers: {
‘Accept’: ‘application/json’,
},
mode: ‘cors’,
cache: ‘default’
};const request = async () => {
var result = ;
const response = await fetch(‘http://192.168.1.128: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();
The query it generates below and if paste that to the browser it fetches data from Influx correctly.
In the browser console I get below error:
apexcharts-card.js?v=1.10.0:1
apexcharts-card: Error: TypeError: Failed to fetch in 'console.log(start);var params = new URLSearchParams({
pretty: true,
db: “homeassistant”,
…’
at request (eval at _generateData (apexcharts-card.js?v=1.10.0:829:41195), :20:28)
at eval (eval at _generateData (apexcharts-card.js?v=1.10.0:829:41195), :31:8)
at Ba._generateData (apexcharts-card.js?v=1.10.0:829:41296)
at Ba._updateHistory (apexcharts-card.js?v=1.10.0:829:38680)
at apexcharts-card.js?v=1.10.0:887:3507
at Array.map ()
at er._updateData (apexcharts-card.js?v=1.10.0:887:3462)
at er._firstDataLoad (apexcharts-card.js?v=1.10.0:829:72578)
at er._initialLoad (apexcharts-card.js?v=1.10.0:887:3152)
- The patched apexchart is not necessary (I removed the link to avoid further confusion)
- Please format your code and/or check the indentation of
data_generator
Hi there. Is it possible to read data from InfluxDB2 via data_generator?
I would like to visualize monthly energy consumption over a period of one year.
Or an example reading data with influxdb2-to-entity would be nice. What do I need for that? AppDaemon and Pyscript?
Hi, I’d like to do the same. Does anybody help you with?
My goal is to visualize monthly energy consumption over a period of one year.
Asked a question here, maybe someone here can solve it too.
Do i have to add a login to the url to the database ?
If i go in my browser to the url, i have to login
For anyone who is having problems with displaying data in apexcharts from influxDB… here is a basic setup with all instructions I sent Kneh… so maybe it will help others.
I have done a fresh install of home assistant 2023.3 on a raspberry pi 4B running the latest version of piOS, and installed influxdb.
In hosts file on the RPI I have…
127.0.0.1 localhost pi2.intranet.lan pi2
::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
127.0.1.1 pi2.intranet.lan pi2
This is why I have pi2 in the following line in apexcharts, but you can put 127.0.0.1, or IP address of that PC (eg 10.0.0.5) if running on local PC, or if influxdb is running on another PC the the IP address of the other PC.
const response = await fetch('http://pi2:8086/query?' + params, myInit)
In configuration.yaml I have the following entries.
influxdb:
host: 127.0.0.1
database: sensors
default_measurement: value
measurement_attr: entity_id
max_retries: 3
exclude:
entity_globs: "*"
sensor:
#INFLUX DATABASE READ
- platform: template
sensors:
influxdb_read:
friendly_name: "Influx DB Read"
value_template: ""
I have a database called sensors with the following data in influxdb.
root@pi2:~# influx -database sensors -execute "SELECT * FROM energy"
name: energy
time entity_id value
---- --------- -----
1678024799000000000 grid_export 8.4
1678024799000000000 grid_import 1.59
1678024799000000000 solar 9.69
1678111199000000000 grid_export 10.39
1678111199000000000 grid_import 1.36
1678111199000000000 solar 11.96
1678197599000000000 grid_export 18.32
1678197599000000000 grid_import 1.49
1678197599000000000 solar 22.56
1678283999000000000 grid_export 16.8
1678283999000000000 grid_import 1.4
1678283999000000000 solar 21.17
1678370399000000000 grid_export 23.53
1678370399000000000 grid_import 1.03
1678370399000000000 solar 26.56
1678456799000000000 grid_export 24.36
1678456799000000000 grid_import 1.04
1678456799000000000 solar 28.53
1678543199000000000 grid_export 25.48
1678543199000000000 grid_import 0.97
1678543199000000000 solar 28.22
1678629599000000000 grid_export 23.39
1678629599000000000 grid_import 1.11
1678629599000000000 solar 26.33
1678715999000000000 grid_export 23.13
1678715999000000000 grid_import 1.05
1678715999000000000 solar 26.14
1678802399000000000 grid_export 22.65
1678802399000000000 grid_import 1.16
1678802399000000000 solar 25.57
1678888799000000000 grid_export 23.93
1678888799000000000 grid_import 0.93
1678888799000000000 solar 27.07
1678975199000000000 grid_export 7.33
1678975199000000000 grid_import 0.47
1678975199000000000 solar 9.22
1680271199000000000 grid_export_month 274.25
1680271199000000000 grid_import_month 15.01
1680271199000000000 solar_month 315.69
1704031199000000000 grid_export_year 274.25
1704031199000000000 grid_import_year 15.01
1704031199000000000 solar_year 315.69
root@pi2:~#
I only want to display the solar output values in apexcharts so I would execute…
root@pi2:~# influx -database sensors -execute "SELECT value FROM energy WHERE entity_id='solar'"
name: energy
time value
---- -----
1678024799000000000 9.69
1678111199000000000 11.96
1678197599000000000 22.56
1678283999000000000 21.17
1678370399000000000 26.56
1678456799000000000 28.53
1678543199000000000 28.22
1678629599000000000 26.33
1678715999000000000 26.14
1678802399000000000 25.57
1678888799000000000 27.07
1678975199000000000 9.22
root@pi2:~#
This equals the query in apexcharts…
q: "SELECT \"value\" FROM \"energy\" WHERE \"entity_id\" = 'solar'"
So now we know we have the data in influxdb that we want to display and the correct query to get the data out of the influxdb.
I add the apexchart card to a dashboard - in my case ENERGY, and add the following code in the card configuration.
type: custom:apexcharts-card
header:
show: true
title: Solar Production - Daily
show_states: false
colorize_states: true
apex_config:
chart:
height: 440
extend_to: now
yaxis:
min: 0
max: 30
forceNiceScale: true
stroke:
show: true
width: 2
legend:
show: true
experimental:
brush: true
graph_span: 1month
span:
end: day
series:
- entity: sensor.influxdb_read
type: column
name: Daily Production
color: '#acd373'
extend_to: false
show:
in_brush: true
group_by:
duration: 24hour
func: first
data_generator: |
var params = new URLSearchParams({
db: "sensors",
q: "SELECT \"value\" FROM \"energy\" WHERE \"entity_id\" = 'solar'"
});
var myInit = { method: 'GET',
headers: {
'Accept': 'application/json',
},
mode: 'cors',
cache: 'default'
};
const request = async () => {
var result = [];
const response = await fetch('http://pi2: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]]);
}
}
return result;
}
return request();
update_interval: 5m
This is what the data looks like in apexcharts.
Thats all that is needed.
Does this work for someone using the influxdb addon, i can’t get it working.
i’m also using influxdb integration, and i get error message
InfluxDB database is not accessible due to '401:
when i set up the sensor
username and password aren’t allowed as arguments, so i don’t know to make it work.
did you find the solution @Kneh ?
Have you solved the authentication problem?