This is the solution. Works for me after the changes now again. Thank you
Need some help here, what should I exactly change (and where) to get the energy dashboard working again?
Check in development tools the states of the entity. It should look like the picture of mine above. If not, you can customise it in customize.yaml. There howto’s in net.
It look like
customize.yaml
sensor.tasmota_sml_strom_in:
state_class: total_increasing
last_reset: '1970-01-01T00:00:00+00:00'
unit_of_measurement: 'kWh'
device_class: energy
This is my solution.
But there are other ways to do that.
1 Like
found it. It was in my configuration.Yaml
homeassistant:
customize_glob:
sensor.*_energy:
last_reset: ‘1970-01-01T00:00:00+00:00’
device_class: energy
state_class:measurementtotal_increasing
Seems to work now. Thx for the info.
I also used to have in my config
state_class: measurement
for the configuration of the energy consumption/injection from the main grid (MQTT input).
After the update the energy dashboard stopped showing values as mentioned in this post. However, after updating the state_class to total_increasing I got invalid entries that are way too big and also negative compared to the usual graphs in the overview as shown in the figure below.
Is there a way to remove these single invalid values from the database so that the totals reported for month/year are in line with the actual consumption?
Thanks,
Lieven.
1 Like
i change from measurement in configuration.yaml to state_class: total_increasing but still not working.also, input from solar production does not work in the energy management chart. bad
all after 2022.2 update
look like now start working
Yes, my device_class:measurement sensors also started reporting 0 in energy management until I changed them to total_increasing in my configuration.yaml file
customize_glob:
sensor.*_importenergy:
last_reset: '1970-01-01T00:00:00+00:00'
device_class: energy
state_class: total_increasing
sensor.*_exportgrid:
last_reset: '1970-01-01T00:00:00+00:00'
device_class: energy
state_class: total_increasing ```
Follow-up: apparently this issue is present when you have a sensor with state_class: measurement in your config, you update home-assistant to the latest version 2022.2.1 and you only notice after a while that the energy dashboard is no longer updating.
This is as far as I understand caused by a different way of storing the data in the home-assistant_v2.db file under the topic statistics in this version of HA:
Sensor with state_class measurement under the previous version of Home Assistant is stored as follows:
20433|2022-02-04 09:00:10.042489|5|2022-02-04 08:00:00.000000||||1970-01-01 00:00:00.000000|5740.244|354.485
After updating to the latest version of HA this becomes:
20451|2022-02-04 10:00:10.036745|5|2022-02-04 09:00:00.000000|5741.98231123358|5741.428|5742.471|||
And then after changing the configuration of the sensor state class to be total_increasing we get:
20541|2022-02-04 15:00:10.032608|5|2022-02-04 14:00:00.000000|5767.73258326141|5765.18|5769.98||5770.368|0.388000000000829
The jump in the graph for this sensor is as expected ±354 kWh in negative.
Is there any way to fix this? I tried removing the invalid readings that were added after the update, but as expected this does not make a difference because the sensor started counting from ‘0’ again after changing the state class to ensure the graphs started updating again.
Is there something we can do with the last_reset value in the database to ensure home assistant does understand the sensor sum was reset? I am willing to experiment furhter with this, but some insights on which changes to make would help me tremendously.
Kind regards,
Lieven.
A bit of off-topic (but this software seems to provide what you/we need when editing more than just a few things where few SQL statements would be fine):
1. Can we use DBeaver with our home-assistant_v2.db (default SQLite db of HA)?
2. Anything to consider (e. g. will DBeaver lock the DB during commits which could be a problem for HA)?
Update:
Terrible idea. 
Don’t do it!
- Thought about even deleting this comment but decided to keep it so others have been warned.
- ANY SQLite database access next to HA when running is very likely to corrupt the database.
- In this case HA will automatically create a new (empty) one.
Offline editing should be safe of course, e. g. by shutting down HA or using a copy.
DBeaver should in general be safe to use with every non-file based database which can be used for HA (like MySQL/MariaDB).
Gosh I really hope the migration from SQLite to MySQL will work some day… less trouble, more flexibility. Migrating home assistant database from sqlite to mariadb.. ONLY if you are VERY familiar with Database administration
Yes. It seems I was too late responding…don’t mess with the SQL lite version of the DB…it’s not a smart idea. The other DBs, particularly MariaDB, this is not a problem…
As it stands I do not understand why people do not move to a MariaDB straightaway if they have to CPU/RAM/Disk to accommodate it…migrating is very easy…and, if you control your history settings in the configuration.yaml file you can mimimise the storage overhead. I have mine to record only the last 48 hours because just how far back do you ever want to go for the status of a light switch? The long term statisitics however keep running on forever…nad for me at least MariaDB has been stable and easy to use!
CP
How did you set this up? Can it be done with an automation? Call service?
Recorder:Purge
It took me a while to figure it all out because I was only a noob…so I borrowed from lots of different places…but this is how I have mine running now:
In the configuration.yaml I put this line:
recorder: !include recorder.yaml
I pull the recorder settings into another file (recorder.yaml obviously) because I control what I keep to ensure my DB doesn’t get crazy large…so the config gets very long…
In the recorder.yaml start with this:
purge_keep_days: 2
db_url: !secret mariadb_url
That tells it to store everything in MariaDB, and tells it that you only want to keep two days of data. Periodically the purge service will sweep through your DB and drop everything older than two days…it is a bit of mystery as to when it actually runs (e.g. on boot, after 24 hours of uptime, etc etc) but I haven’t bothered to dig into it because it really doesn’t matter…it just works and timing is not critical.
Anyway, even with the 2 day setting my DB was large…which is a major pain because the DB is backed up when your backups run…and I was running out of room on my Google Drive…and I didn’t want to pay for additional storage because I have 1TB on OneDrive…and didn’t want to pay extra just for backups (and because I’m lazy and want to minimise moving parts I also didn’t want to set up something else to copy backups from HA to somewhere else so it could export).
I then used DBeaver to show me what was in the DB:
select entity_id,count(*) from states group by entity_id order by 2 desc;
This returns a list of all the state data that it stored sorted by count…for example:
sensor.conservatory_motion_detector_temperature 3766
sensor.services_cost_per_hour 2964
sensor.laundry_motion_detector_temperature 2871
sensor.white_phase 2704
sensor.energy_white_filtered 2704
sensor.white_phase_energy 2701
sensor.energy_white_filtered_cost 2692
sensor.garage_motion_detector_front_temperature 1354
sensor.garage_motion_detector_rear_temperature 1351
sensor.red_phase 1204
sensor.energy_red_filtered 1204
sensor.red_phase_energy 1200
sensor.energy_red_filtered_cost 1192
sensor.hallway_cupboard_motion_detector_temperature 894
sensor.myups_input_line_frequency 665
sensor.weatherflow_air_density 650
This is my current result (of 500 rows) and there’s nothing extreme though still plenty to filter if I wanted to do so to save even more space. In my first attempt I had entities with 10,000+ results…and my DB was pushing 800-1200MB.
I went through the list and started excluding entities where I didn’t need to know the history - for example for a specific temperature measurement (e.g. in the living room) I want the history because I like my nice graph card that shows 24 hours of data, but for the battery level of a door contact sensor I certainly don’t need to have a detailed history because I really only care about the now value. So I set to work and started excluding things by adding content to the recorder.yaml:
exclude:
domains:
- camera
- group
- device_tracker
- media_player
- input_text
- input_number
- input_boolean
- input_number
- input_select
- weblink
- updater
- sun
- timer
- weather
- person
- automation
entity_globs:
- sensor.rpi*
- sensor.pi_hole*
- sensor.processor*
- sensor.memory*
- sensor.disk*
- sensor.load*
- sensor.swap*
- sensor.last_boot*
- sensor.local_ip*
- sensor.home_alarm*
- sensor.*_sensitivity
- sensor.*_illuminance
- sensor.*_lux
- sensor.*_timeout
- sensor.*_price
- sensor.measured_outdoor_air_temperature_*
- sensor.*stairway*temperature
- sensor.*wardrobe*temperature
- sensor.bedroom_bed*
- sensor.hallway_motion_*_temperature
- sensor.esp*
- sensor.*_current
- sensor.*lights*energy*
- sensor.*rail*energy*
- sensor.*extractor*energy*
- sensor.*device_temperature
- sensor.energy_consumption_*_raw
- sensor.*power*
- sensor.*last_seen*
- sensor.myups_load
- sensor.cpu_temperature
- sensor.weatherflow_wind_*
- sensor.weatherflow_beaufort*
- sensor.weatherflow_feels*
- sensor.weatherflow_brightness
- sensor.weatherflow_solar_radiation
- sensor.weatherflow_dewpoint
- sensor.weatherflow_rain*
- sensor.weatherflow_*humidity*
- sensor.weatherflow_*pressure*
- sensor.weatherflow_uv*
- sensor.weatherflow_precipitation*
- sensor.weatherflow_heat_index
- sensor.weatherflow_*lightning*
- sensor.weatherflow_wet_bulb*
- sensor.weatherflow_*voltage*
- sensor.weatherflow_visibility
- sensor.weatherflow_*delta*
- sensor.*_ble
- binary_sensor.espresense_*
- binary_sensor.*_mqtt_room
- binary_sensor.weatherflow_is*
- binary_sensor.*_overpowering
- binary_sensor.*_overheating
- binary_sensor.motion_exterior*
- binary_sensor.zigbee_router*
- binary_sensor.*vibration*
- switch.schedule_*
- switch.*_led_indication
- number.*_occupancy_timeout
entities:
- sensor.hallway_heat_pump_outside_temperature
- sensor.lounge_heat_pump_outside_temperature
- sensor.shelly_laundry_energy
- sensor.shelly_garagebay_energy
My actual listing is considerably longer and probably overdue for a tidy up as I’m sure some entities have moved on…but this gives you the idea. If you list a domain then everything from that area is excluded from history - for example I don’t want anything from ‘weather’. But sometimes you want something, just not everything so then you can use entity_globs which allow you to wildcard an entity string…if you wanted to have no history for when something was last seen you can set a entity_glob of
sensor.*last_seen*
which will exclude anything that matches that pattern. Lastly if you need to be more surgical you can list specific entities to exclude in the entities section. This is useful where you have, say, two motion detectors at opposite ends of a hallway - you actually might want to use their temperature and lux data, but you don’t really need two measurements for the space so you specifically exclude one of the two sets of entities.
Set your config, restart HA, then using Developer Tools call the Recorder: Purge service. You can set the flags as follows:
Days to keep: 2 (or to whatever duration you want to keep)
Repack = Yes
Apply Filter = Yes
That will purge the DB to the meet your new include/exclude and duration filter. It can actually be fun to run this before you make changes so you can see progress:
Now: 800MB
Drop retention to 2 days: 300MB
Recorder.yaml update #1: 200MB
Recorder.yaml update #2: 150MB
Recorder.yaml update #3: 85MB
After that it is all pretty much automatic. I run the DBeaver query every month or so to see if anything needs a tune up because I’m always tinkering so things get added, renamed, or moved…and that’s about it…though now I do rather feel like I should be cleaning out my recorder.yaml again to keep it tidy!
The statistics function will track your long term data (such as energy) automatically and that doesn’t get impacted by keeping your other entity history in check.
Oh…and currently my DB is now floating somewhere between 90MB - 140MB depending on when the purge last ran. So for me I’m running at about 10% of what it would have been without any management…
Hope that helps!
CP.
1 Like
I’m looking for a little help if possible, I’ve spent a few days reading through different threads to try and solve the issue, since updating to 2022.2 my template sensors don’t appear to be functioning at all. As an example, in my config.ymal I have some template sensors to interpret negative data into positive figures:
- platform: template
sensors:
battery_discharging:
friendly_name: "Battery charging"
unit_of_measurement: 'w'
value_template: "{{ states('sensor.solax_battery_power') | float | max(0)}}"
device_class: "power"
battery_charging:
friendly_name: "Battery discharging"
unit_of_measurement: 'w'
value_template: "{{ states('sensor.solax_battery_power') | float | min(0) | abs }}"
device_class: "power"
grid_infeed:
friendly_name: "Grid infeed"
unit_of_measurement: 'W'
value_template: "{{ states('sensor.solax_exported_power') | float | min(0) | abs }}"
device_class: "power"
I’ve tried updating these to the newer template format with no joy, these templates are used for some integrations for the energy dashboard in the following format:
- platform: integration
source: sensor.grid_infeed
name: Imported Energy
unit_prefix: k
round: 2
method: left
I believe my issue is with the template sensors as they are showing unavailable within the dev tools.
Any help would be very much appreciated.
Yes have a look here Energy Management in Home Assistant - #1201 by petro
There has been a change in the min and max code and you have to slightly change your value_templates
1 Like
Here is what I am using after the December update, which is working for me:
platform: template
sensors:
tesla_card_grid_in:
unique_id: "tesla_card_grid_in"
# value_template: "{{ states('sensor.powerwall_site_now') | float | max(0) }}"
value_template: >-
{% set n = states('sensor.powerwall_site_now') | float %}
{{ n if n > 0 else 0 }}
device_class: power
unit_of_measurement: kW
tesla_card_grid_out:
unique_id: "tesla_card_grid_out"
value_template: >-
{% set n = states('sensor.powerwall_site_now') | float %}
{{ n | abs if n < 0 else 0 }}
device_class: power
unit_of_measurement: kW
tesla_card_solar_in:
unique_id: "tesla_card_solar_in"
value_template: >-
{% set solar = states('sensor.powerwall_solar_now') | float %}
{% set house = states('sensor.powerwall_load_now') | float %}
{{ solar if house > solar else house }}
device_class: power
unit_of_measurement: kW
tesla_card_battery_in:
unique_id: "tesla_card_battery_in"
value_template: >-
{% set n = states('sensor.powerwall_battery_now') | float %}
{{ n if n > 0 else 0 }}
device_class: power
unit_of_measurement: kW
tesla_card_battery_out:
unique_id: "tesla_card_battery_out"
value_template: >-
{% set n = states('sensor.powerwall_battery_now') | float %}
{{ n | abs if n < 0 else 0 }}
device_class: power
unit_of_measurement: kW
1 Like
If you read the changes, you saw it’s under dashboards now.
Ahh annoyingly I thought I did read the release notes.