Using HA or HACS graphs w/ InfluxDB data in HA?

koying · May 22, 2021, 8:52am

Maybe due to how you patched apexchart?

EDIT: Removed obsolete link

davo · May 22, 2021, 12:08pm

I didnt patch apexchats - I installed HAC’s and added it from there. I did try your version of apexcharts-card.js too, which has a bigger file size (1,188kb) compared to the default HAC version (727kb).

I am still getting this error:

apexcharts-card.js:1 apexcharts-card:  TypeError: Cannot read property '1' of undefined
    at apexcharts-card.js:852
    at Array.forEach (<anonymous>)
    at HTMLElement._updateData (apexcharts-card.js:852)

Thanks

koying · May 22, 2021, 12:13pm

You have to, or the code won’t work.
If you still have the same error, it’s possible you’re still using the hacs version.

Did you adapt the lovelace resources to point to my version?

davo · May 22, 2021, 12:22pm

I think I have done it correct:
I copied your version to homeassistant/www/
I went to Configuration / Lovelace Dashboards / Resources
Selected the current apexcharts and changed the URL to /www/apexcharts-card.js

is this correct ?

EDIT: the URL is /local/apexcharts-card.js

koying · May 22, 2021, 12:30pm

Yes. Just be sure to remove the “/hacsfiles” reference to the original apexchart as well or you don’t know which one is used.
And restart HA, ofc.

EDIT: And clear browser cache

davo · May 22, 2021, 12:48pm

Koying - someone is a very smart lad !
I had to wipe browser cache and it worked.
I can’t beleive it is working the way I want. Its been my biggest problem with using HA and apex cards.
Here is my settings for anyone else

type: 'custom:apexcharts-card'
header:
  show: true
  title: Solar Monthly Production
  show_states: false
  colorize_states: true
apex_config:
  chart:
    height: 270
    extend_to_end: false
  yaxis:
    min: 0
    max: 800
  stroke:
    show: true
    width: 1
  legend:
    show: true
graph_span: 12 month
span:
  end: month
series:
  - entity: sensor.solar_monthly_influxdb
    type: column
    name: Monthly Production
    color: green
    extend_to_end: false
    show:
      datalabels: true
    group_by:
      duration: 1month
    data_generator: |
      console.log(start);
      var params = new URLSearchParams({
          db: "sensors",
          q: "SELECT \"value\" FROM \"solar\" WHERE \"entity_id\" = 'month' AND time < now()"
          });

      var myInit = { method: 'GET',
                      headers: {
                          'Accept': 'application/json',
                      },
                      mode: 'cors',
                      cache: 'default' 
      };

      const request = async () => {
          var result = [];
          const response = await fetch('http://10.0.0.10: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)
          }
          console.log("return: " + json) 
          return result;
      }
      return request();
update_interval: 1d

apex-working

Don’t know how much the sensor config makes, but below is what I have in configuration.yaml

  - platform: influxdb
    host: 127.0.0.1
    queries:
      - name: solar_monthly_influxdb
        database: sensors
        field: value
        measurement: solar
        where: '"entity_id" = ''month'' AND time < now()'
        unit_of_measurement: kWh

Thank you so much Chris

koying · May 22, 2021, 2:07pm

Glad it worked
You can remove those loggings, to not flood your browser.

koying · May 27, 2021, 9:04am

The needed change to apex-card has been incorporated in release 1.9.0.

electronichamsters · June 7, 2021, 4:45am

Thanks for putting in this work!

bacco007 · June 7, 2021, 5:40am

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:

github.com

bacco007/HomeAssistantConfig/blob/9c6e27aa6c1f5305c2682e48d93d2873208b1b58/automations/influxdb_to_sensor/weather_365day_rain.yaml

---
id: "influx2entity_365dayrain"
alias: "InfluxDB to Entity: 365 Day Rain"
description: "InfluxDB to Entity: 365 Day Rain"
trigger:
  - platform: homeassistant
    event: start
  - platform: time_pattern
    hours: "/1"
condition: []
action:
  - service: pyscript.influxdb_query_to_entity
    data:
      database: homeassistant
      query: >-
        SELECT max("value") FROM "homeassistant"."autogen"."mm"
        WHERE time >= now() - 365d AND ("entity_id"='netatmo_rain_sum_rain_24' OR "entity_id"='netatmo_brisbane_street_rain_sum_rain_24' or "entity_id"='weather_outside_rain_day')
        GROUP BY time(1d) FILL(null) tz('Australia/Sydney')
      key_field_name: time
      value_field_name: max

This file has been truncated. show original

Mattias_Persson · October 22, 2021, 9:45pm

@bacco007 @koying

This is amazing! Do you guys have anything like this for influxdb 2?
They are making it really hard to output json with flux

bacco007 · October 22, 2021, 11:53pm

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.

koying · October 23, 2021, 7:25am

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…

Mattias_Persson · October 24, 2021, 1:09am

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()

mf42 · December 9, 2021, 9:56pm

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…

Sebu · February 12, 2022, 3:55pm

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.

http://192.168.1.128:8086/query?pretty=true&db=homeassistant&q=SELECT+mean("value")+FROM+"autogen"."°C"+WHERE+("entity_id"+%3D+'vallox_outdoor_air')+GROUP+BY+time(1h)+fill(none)

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)

koying · February 13, 2022, 9:30am

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

arboeh · May 17, 2022, 11:02am

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?

suchar · February 4, 2023, 9:03pm

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.

Kneh · March 1, 2023, 11:46am

Asked a question here, maybe someone here can solve it too.