Showing influxDB data in sensor card

Configuration
1

Hi all,

I am trying to display solar monthly production history data which is in an influx DB.
All I get is the average amount for the total months as below, which I have data for Jan to April 2021.

image

I am trying to get data to be displayed in an apex-chart, but I have also tried in other cards with same result. Below is apex-charts card config:

type: 'custom:apexcharts-card'
header:
  show: true
  title: Solar Monthly Production
  show_states: false
  colorize_states: true
apex_config:
  chart:
    type: line
    height: 270
  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_influx
    type: column
    name: Monthly Production
    color: green
    show:
      datalabels: true
update_interval: 1d

The data is in influxDB. If I do a query, below is what is returned.

root@pi4HA:~# influx -database 'sensors' 
Connected to http://localhost:8086 version 1.8.5
InfluxDB shell version: 1.8.5
> SELECT "value" FROM "solar" WHERE "entity_id"='month'
name: solar
time                value
----                -----
1612085400000000000 588.8
1614504600000000000 596.5
1617183000000000000 736.7
1619775000000000000 572.9
>

and using a curl query

root@pi4HA:~# curl -G 'http://localhost:8086/query?pretty=true' --data-urlencode "db=sensors" --data-urlencode "q=SELECT \"value\" FROM \"solar\" WHERE \"entity_id\"='month'"
{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "solar",
                    "columns": [
                        "time",
                        "value"
                    ],
                    "values": [
                        [
                            "2021-01-31T09:30:00Z",
                            588.8
                        ],
                        [
                            "2021-02-28T09:30:00Z",
                            596.5
                        ],
                        [
                            "2021-03-31T09:30:00Z",
                            736.7
                        ],
                        [
                            "2021-04-30T09:30:00Z",
                            572.9
                        ]
                    ]
                }
            ]
        }
    ]
}

and here is my configuration.yaml

  - platform: influxdb
    host: 127.0.0.1
    queries:
      - name: solar_monthly_influx
        database: sensors
        measurement: solar
        field: "value"
        unit_of_measurement: kWh
        where: '"entity_id" = ''month'''

Is it the data in influx that is the problem, the way I am accessing the data / sensor setup in HASS, or the card config ?

Thanks for any help.

2

I have installed grafana and have been able to graph the data as I want, however I don’t want to use grafana, but still can not get the data to display in home assistant using apex charts or the default charts/graphs.
For the daily solar production query is:

SELECT mean("value") FROM "solar" WHERE ("entity_id" = 'day') AND $timeFilter GROUP BY time($__interval) fill(none)

grafana_data_1
grafana_data_11774×1134 163 KB

for monthly solar production.

SELECT mean("value") FROM "solar" WHERE ("entity_id" = 'month') AND $timeFilter GROUP BY time($__interval) fill(none)

grafana_data_2
grafana_data_21777×1216 94.9 KB

Does anyone know how I can get it to display in home assistant without using grafana ?

thanks

1 Like
3

See Using HA or HACS graphs w/ InfluxDB data in HA? - #6 by koying for a proof-of-concept