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.
