SQL lookup of kWh from Solar/Battery into the Energy tab?

Configuration
1

I have an SQL(MariaDB) database getting updated every few seconds with lots of solar and battery systems. I have an SELECT statement that will return a total kWh today on any device I want. This works fine as an SQL integration, but I can’t make any of them selectable in the Energy tab.
Aka, I go to Energy tab to add a battery, but I can’t get my battery to appear. I do have the “Unit of Measure” set to kWh in and the SQL statement is working.
All the smart plugs I have show up fine.

What else do I have to do to make it show up?

2

Using Customize, add:

sensor.one_of_your_energy_sensors:
  state_class: total_increasing
  device_class: energy
sensor.another_one_of_your_energy_sensors:
  state_class: total_increasing
  device_class: energy
etc...

You could open a feature request for the SQL sensor to support device and state classes.

3

Thank you. You sent me down the path that may have solved the issue.
Any extra columns sent in an SQL select statement are treated as attributes. If I include them in my select statement, it seems to work.

select ABS(round(sum(((volts*current)/3600*10))/1000,3)) AS kWh, "energy" AS device_class, "total_increasing" AS state_class FROM batt1 WHERE current < 0.01 AND timestamp >= CURDATE() - INTERVAL 1 DAY;

The above line is what I am trying out, but I am not sure I have the math right. It sums up all the kw used in a day period. It makes the assumption that the database updates every 10 seconds with new information.
< sets it to show used kWh.
> sets it to show charged kWh.

I have the charge controllers added the same way. I am interested to see what HA shows since I don’t have any loads added. I am off-grid, so the entire house is a load. Not sure how to add that.

4

For anyone looking to integrate their off-grid system. The Energy tab, as of 2022.8, is really designed for on-grid applications and reading from a power meter. If you don’t have the power meter integration, you don’t get much. I have the solar working and I get the “Solar Production” bar chart in kWh. And with the batteries, there is a table with in/out of kWh. But that is it.

5

What else is there for off-grid?

Solar energy supplied, battery energy discharged and battery energy charged.

You could add a generator in the grid connection I suppose.

6

Different numbers are important for off-grid then for on-grid.

The “Solar Production” bar chart is near pointless in off-grid. Once the batteries get full, solar production drops to cover the loads. So my 7kw array drops to 600 watts of output.

My battery total is 4.55kWh right now. Since I have used 4.55kWh out of the bank, I would expect that number to be negative, but regardless, that number itself tells me little.

I am not complaining as I am sure it works great for on-grid as that is what it is targeted for. But the numbers I really want is not kWh, but watts and state of charge. I need to know the state of charge of my batteries, along with solar panel output. Will the batteries will get fully charged today? Or will it not happen and do I need to cut back? Do I have an abnormally large load going right now?

I have all that information in HA and/or Grafana. Just not the Energy tab in HA.

7

Well you are not going to get Watts as that is power. It’s the Energy Dashboard, not Power Dashboard.

You can make your own dashboard.