The only thing I’ve done was to install the latest version 2023.6 (now 2023.6.1).
According to the Riemann integration developer, the fix merged in the last release should have solved the issue.
Two days in a row that look correct after at least 6 weeks of negative values every morning.
If it appears again I will come back to this post.
Are you still seeing the negative values even with the latest release ? 
I got same issue. I installed 2023.6 and in this restart a negative value has appeared which I cannot find in the developers tools. I then installed also 2023.6.1 and on this restart no new negative values have been generated. Unfortunately the negative value from the restart after 2023.6 installation is still in the statistics and there is no way to find it. Even looking inside the SQLite DB is impossible to identify it as I cannot filter per date since all date tables are NULL. Anyone any hint how to the this single entry deleted?
Hi, I managed to fix the negative entries, but it is NOT easy. It involved running SQL updates from the SQLite Web addon. I can do a small writeup if you want.
Ok, I did a writeup anyway. Sure there are easier ways for the people with more SQL and scripting knowledge, but here it goes.
This is how I fixed the negative values on the Energy dashboard after the 2023.6 update when I could not fix it in developer tools anymore. Two days later, and the problem has not yet appeared again. Hopefully the update fixed it and only left one last negative mark.
Do at your own risk, but it worked for me.
Have SQLite Web addon ready in HA
Use https://www.epochconverter.com/ to get the binary timestamp of the day before the negative value
For example the 10th 12am GMT = 1686355200
Go to the statistics_meta table, select Content tab, and look for the id of the relevant kWh recorder. Say for example it is id = 78
Go to the statistics table and select the Query tab.
Add a new line below the pre-populated query with the following:
WHERE metadata_id = 78 AND created_ts > 1686355200
Click Execute
You should now hopefully only have a handful of records, depending on how long back it happened.
Now focus on the sum column and look for the line where the value went down.
Copy the created_ts of two lines before the decreased value and paste that in the query in place of the previous timestamp.
Execute again, and you should end up with one correct line and then all the lower value lines below it.
If you still have hundreds of lines left, I do not recommend proceeding with this tedious process.
Click export csv and import it in google sheets or excel.
Create a new formula in column N3: =$I$2+H3
Copy that new formula and paste next to all rows below.
You should now have a new column with the correct sum value.
Back to SQOLite Web addon,
Now we will manually do an update for each value of the state column. The Query will look like below, be mindful the keep the previous WHERE part and just add the state part at the end. You will set the sum field to the new corrected value as per spreadsheet for that particular state value. Note that the same state can occur many times, but you only update each value of the state column ONCE.
UPDATE "statistics"
SET sum= 567.7
WHERE metadata_id = 78 AND created_ts > 1686276010.3757 AND state = 0.09
Repeat for the other state values.
Not sure if required, but I also repeated the same process on the statistics_short_term table.
And of course, the tedious part, repeat all on the other negative sensors.
Hi folks,
I experienced the same problem and updated to Home Assistant 2023.6.1 (Docker).
I tried to find the broken database entries and wrote a little python script to fix the errors.
Disclaimer: The script recalculates the broken entries based on the difference between two consecutive entries in the existing database. No further logic is applied. But this seems to solve the problem on my end. No further testing done. Please provide feedback if you find anything 
Link to script:
More information is provided in the README.md.
Please make sure to stop HomeAssistant before changing anything in the database!
This script makes a backup of your existing database first before it touches anything.
Bl4DEx/HA_FixNegativeStatistics (github.com)
I am going a similar route as @rekenaar and do the following:
→ I am fixing the database from bottom to top to ensure sum consistency. This means that the script might update the same entry twice if two broken values exist in the table for a given metadata_id
The script handles the following:
Results:
All my graphs no longer show any negative values but rather a consistent graph.
BUT:
The previous broken entries are fine. But after running HomeAssistant for a while, I have experienced that the latest values in all my graphs show negative values again. I am not sure where this is coming from. Can someone confirm this?
This sounds like the problem I ran into last month trying to manually fix the database. Even though I thought it was fixed and before it recurred, the data was still not displaying properly. I assumed somehow there was some third table (or cached somewhere maybe even in RAM) that was corrupted but I couldn’t find it.
I rolled back to 2023.4.x in early May (now just waiting for the “all clear”), but want to say thanks to you and others for working on this…even though I’m not currently affected.
EDIT: Did update to 6.2 and not seeing any issues overnight and some restarts.
Thank you @rekenaar. I’ll test it as soon I am back from holiday. Somewhere I must have missed the wrong entry line when I looked at the db.
Can this script also be tailored for MariaDB?
Hi @fversteegen,
my script makes use of basic SQL commands only and should work with both. I would propose to give it a try on your database and see what happens
Hi @Bl4DEx ,
I tried my best in hacking the python script (second attempt ever at scripting :-)) and managed to connect to the DB, but am now totally stuck in errors (see below).
I do have phpmyadmin running, but cannot figure out how to do the same in phpmyadmin (especially the looping). Is that something you can help with?
Traceback (most recent call last):
File "/config/HA_FixNegativeStatistics.py", line 136, in <module>
fixDatabase()
File "/config/HA_FixNegativeStatistics.py", line 50, in fixDatabase
fix_table_state(table, key, metadata_id)
File "/config/HA_FixNegativeStatistics.py", line 61, in fix_table_state
cur.execute("SELECT id,{} FROM {} WHERE metadata_id=? ORDER BY created_ts".format(key, table), (metadata_id,))
File "/usr/local/lib/python3.11/site-packages/MySQLdb/cursors.py", line 203, in execute
raise ProgrammingError(str(m))
MySQLdb.ProgrammingError: not all arguments converted during bytes formatting
Hello @fversteegen
I see that you are using the python module MySQLdb (MySQL-python – https://pypi.org). So, you are trying to connect to a MySQL server instead of modifiying a database file?
In my original script, I have used the native python package sqlite3.
The issue you are seeing is related to the module that you are using. I have searched the web a little and came across this post: https://stackoverflow.com/a/53563287
Basically, If you have version 1.2.5 installed (which is installed by default by pip because it is the latest version), then you might want to change
cur.execute("SELECT id,{} FROM {} WHERE metadata_id=? ORDER BY created_ts".format(key, table), (metadata_id,))
to
cur.execute("SELECT id,{} FROM {} WHERE metadata_id=? ORDER BY created_ts".format(key, table), [metadata_id])
NOTE: Please remember my “BUT” statement from my previous post. Even if you fix the database with the current version of the script, it will result in negative value with the next calculation of the Rieman Sum.
Currently, I am working on the next version of the script. I am not sure yet but I think I have found the issue why this is happening. I will update here if there is any news on that. I am planning to be backwards compatible meaning that you can execute the script on a database which was broken weeks ago
Same issue here with the Vicare integration.
I’ve checked the statistics and everything looks to be good on that day (June 12). How can I fix that please?
With the great help of @Bl4DEx (and a bright scripter in my team) we managed to hack together a similar script for maridb/mysql. Find the script below.
docker exec -it homeassistant /bin/bash
pip install pymysql
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
This script fixes the broken statistics in the HomeAssistant database caused by a bug in the Riemann Sum in HA 2023.05
It fixes the tables: statistics, statistics_short_term
It fixes the keys: sum, state
Usage: python HA_FixNegativeStatistics [--list]
"""
import os
import sys
import shutil
import pymysql
import MySQLdb
__author__ = "fversteegen"
__version__ = "0.9.0"
# Database connection details
DATABASE_HOST = 'core-mariadb'
DATABASE_USER = 'homeassistant'
DATABASE_PASSWORD = 'SUPERSECRETPASSWORD'
DATABASE_NAME = 'homeassistant'
# Path to your database
#DATABASE_PATH = r"config/home-assistant_v2.db"
# Set the metadata_ids that you want to have fixed; run "python FixNegativeStatistics.py --list" for some help
METADATA_IDS = (711, 249, 731, 720, 735, 490, 140, 137, 136, 141, 134, 176, 128, 123)
#if not os.path.isfile(DATABASE_PATH):
# sys.exit("Database does not exist!")
# Open database connection
db = pymysql.connect(host=DATABASE_HOST, user=DATABASE_USER, password=DATABASE_PASSWORD, database=DATABASE_NAME)
# Create cursor object within the database
cur = db.cursor()
# Amount of entries changed
entries_changed = 0
def fixDatabase():
# Create database backup
# shutil.copyfile(DATABASE_PATH, os.path.splitext(DATABASE_PATH)[0] + ".db.BAK")
# Update both tables statistics and statistics_short_term
for table in ("statistics", "statistics_short_term"):
# Update both keys state and sum
for key in ("state", "sum"):
# Fix value for all metadata_ids
for metadata_id in METADATA_IDS:
fix_table_state(table, key, metadata_id)
# Store database on disk
db.close()
# Print result
print(f"\n\n{entries_changed} values changed!")
def fix_table_state(table: str, key: str, metadata_id: int):
# Execute SQL query to get all entries for this metadata_id
cur.execute("SELECT id,{} FROM {} WHERE metadata_id=%s ORDER BY created_ts".format(key, table), (metadata_id,))
result = cur.fetchall()
# Step through database in reverse order
for index, (id, value) in reversed(list(enumerate(result))):
# Get previous entry
_, pre_value = result[index - 1]
if pre_value <= value or index == 0:
# We reached the first entry
# OR
# current and previous are the same or incrementing. Value is correct
# nothing to do
continue
print(f"\nStarting with ID: {id}")
# First broken value; re-use old value (we might lose one time period of measurement)
new_value = pre_value
# Update value in database
updateValueInDatabase(table, id, key, new_value)
print(f"({id}, {value}) -> ({id}, {new_value})")
# Fix ALL following entries
for fix_index, (fix_id, fix_value) in enumerate(result[index + 1:], index + 1):
# Get previous value (before it was fixed)
_, pre_value = result[fix_index - 1]
# Add difference between last value and new value
new_value += fix_value - pre_value
# Update value in database
updateValueInDatabase(table, fix_id, key, new_value)
print(f"({fix_id}, {fix_value}) -> ({fix_id}, {new_value})")
# Update entries for next run
cur.execute("SELECT id,{} FROM {} WHERE metadata_id=%s ORDER BY created_ts".format(key, table), (metadata_id,))
result = cur.fetchall()
def updateValueInDatabase(table: str, id: int, key: str, value: int):
"""
Update key with value in table
:param table: Table to update in database
:param id : ID of entry to update
:param key : Key to update
:param value: New value
"""
# Execute SQL query
cur.execute("UPDATE `{}` SET `{}`=%s WHERE id=%s".format(table, key), (value, id))
# Commit changes to DB
db.commit()
global entries_changed
entries_changed += 1
def list_metadataIds():
# Execute SQL query
cur.execute("SELECT id,statistic_id FROM statistics_meta WHERE has_sum=1")
for id, entity in cur.fetchall():
# Print metadata_id and entity name
print(f"metadata_id: {id: >3} | {entity}")
if __name__ == "__main__":
if len(sys.argv) == 1:
# Fix database
fixDatabase()
elif len(sys.argv) == 2 and sys.argv[1] == "--list":
# List available metadata_ids
list_metadataIds()
else:
sys.exit("Unknown input argument!")
sys.exit(0)
Hi folks,
after several hours of testing, debugging, reading HA code and breaking my own database multiple times, I have finally found the issue why the next calculation results in an invalid value even if the database was fixed 
I have updated my script here and also described how to use and what it does:
Bl4DEx/HA_FixNegativeStatistics (github.com)
This script scans your whole history and fixes all following entries if something broken is found. This means that it should be able to fix your database even if the bad values happened weeks ago.
I also updated how to input the entities you want to fix. You no longer have to find the metadata_ids in the database but a entities.list file is created which holds the entities_ids visible in the HA UI.
This script is tested with HomeAssistant Docker 2023.6.2
I have the database running for 24 hours and all my statistics look good!
You can see my graph of the current month. Finally, no more negative values:
Please feel free to test it on your database and post the result. I would like to extend the list of “tested on”
I’m running my MariaDB on my Synology NAS.
Can I run this script for it as well and how?
It won’t work directly as it only supports the standard SQLite DB, needs to be adapted for MariaDB the way @fversteegen shared.
Think the below should do the trick. Do change the path and the database configuration!
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
This script fixes the broken statistics in the HomeAssistant database caused by a bug in the Riemann Sum in HA 2023.05
Usage: python HA_FixNegativeStatistics [--list]
"""
import json
import os
import sys
import shutil
import pymysql.cursors
from datetime import datetime
__author__ = "Sebastian Hollas"
__version__ = "2.0.0"
####################################################################################
# USER INPUT REQUIRED !
# Connection details for the MySQL database
DB_HOST = 'core-mariadb'
DB_USER = 'homeassistant'
DB_PASSWORD = 'homeassistant'
DB_NAME = 'homeassistant'
####################################################################################
HA_CONFIG_ROOT = "/config"
ENTITIES_FILE = os.path.join(HA_CONFIG_ROOT, "entities.list")
RESTORE_STATE_PATH = os.path.join(HA_CONFIG_ROOT, ".storage", "core.restore_state")
CONFIG_ENTRIES_PATH = os.path.join(HA_CONFIG_ROOT, ".storage", "core.config_entries")
ENTITY_REGISTRY_PATH = os.path.join(HA_CONFIG_ROOT, ".storage", "core.entity_registry")
if not os.path.isfile(RESTORE_STATE_PATH):
sys.exit(f"File {RESTORE_STATE_PATH} does not exist! (Path to HomeAssistant config valid?)")
if not os.path.isfile(CONFIG_ENTRIES_PATH):
sys.exit(f"File {CONFIG_ENTRIES_PATH} does not exist! (Path to HomeAssistant config valid?)")
if not os.path.isfile(ENTITY_REGISTRY_PATH):
sys.exit(f"File {ENTITY_REGISTRY_PATH} does not exist! (Path to HomeAssistant config valid?)")
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
cursorclass=pymysql.cursors.DictCursor
)
def main():
if len(sys.argv) == 1:
if os.path.isfile(f"{RESTORE_STATE_PATH}.BAK"):
sys.exit("core.restore_state backup file already exists!")
shutil.copyfile(RESTORE_STATE_PATH, f"{RESTORE_STATE_PATH}.BAK")
if not os.path.isfile(ENTITIES_FILE):
sys.exit(f"File {ENTITIES_FILE} does not exist! (Run with --list first and remove unwanted entities)")
with open(ENTITIES_FILE, "r") as file:
ENTITIES = file.read().splitlines()
fixDatabase(ENTITIES=ENTITIES)
elif len(sys.argv) == 2 and sys.argv[1] == "--list":
with open(ENTITIES_FILE, "w") as file:
for entity_id in getEntitiesPrecision():
file.write(f"{entity_id}\n")
print(f"File '{ENTITIES_FILE}' created with entities that are most likely Riemann Sum Entities"
f"\nPlease adjust to your needs and rerun the script with no arguments.")
else:
sys.exit("Unknown input argument!")
def fixDatabase(ENTITIES: list):
EntityPrecision = getEntitiesPrecision()
with connection.cursor() as cursor:
for entity_id in ENTITIES:
cursor.execute("SELECT metadata_id FROM states_meta WHERE entity_id=%s", (entity_id,))
result = cursor.fetchone()
if not result:
print(f" [WARNING]: Entity with name '{entity_id}' does not exist in table states_meta! Skipping...")
continue
metadata_id_states = result['metadata_id']
cursor.execute("SELECT id FROM statistics_meta WHERE statistic_id=%s", (entity_id,))
result = cursor.fetchone()
if not result:
print(f" [WARNING]: Entity with name '{entity_id}' does not exist in table states_meta! Skipping...")
continue
metadata_id_statistics = result['id']
if entity_id not in EntityPrecision:
print(f" [WARNING]: Entity seems not to be a Riemann Sum Entity! Skipping...")
continue
roundDigits = EntityPrecision[entity_id]
print("\n========================================================================")
print(f"{entity_id} | {metadata_id_states = } | {metadata_id_statistics = }")
lastValidSum = recalculateStatistics(cursor, metadata_id=metadata_id_statistics, key="sum", roundDigits=roundDigits)
lastValidState = recalculateStatistics(cursor, metadata_id=metadata_id_statistics, key="state", roundDigits=roundDigits)
fixShortTerm(cursor, metadata_id=metadata_id_statistics, lastValidSum=lastValidSum, lastValidState=lastValidState)
recalculateStates(cursor, metadata_id=metadata_id_states, roundDigits=roundDigits)
fixLastValidState(entity_id=entity_id, lastValidState=lastValidState)
print(f"\n{connection.affected_rows()} changes made to database!")
connection.commit()
connection.close()
def recalculateStatistics(cursor, metadata_id: int, key: str, roundDigits: int) -> str:
print(f" Fixing table statistics for key: {key}")
cursor.execute("SELECT id,{} FROM statistics WHERE metadata_id=%s ORDER BY created_ts".format(key), (metadata_id,))
result = cursor.fetchall()
try:
current_value = float(result[0][key])
except ValueError:
sys.exit(f" [ERROR]: Cannot fix this entity because first entry in table 'statistics' for {key} is not a number! Sorry!")
for index, row in enumerate(result[1:]):
pre_row = result[index]
if row[key] < current_value:
if row[key] >= pre_row[key]:
current_value += (row[key] - pre_row[key])
roundedValue = f"{current_value:.{roundDigits}f}"
print(f" Updating {row['id']} = {row[key]} -> {roundedValue}")
cursor.execute("UPDATE statistics SET {}=%s WHERE id=%s".format(key), (roundedValue, row['id']))
continue
current_value = row[key]
return f"{current_value:.{roundDigits}f}"
def fixShortTerm(cursor, metadata_id: int, lastValidSum: str, lastValidState: str):
print(" Deleting short term statistics")
cursor.execute("DELETE FROM statistics_short_term WHERE metadata_id=%s", (metadata_id,))
now = datetime.now()
minute_end = now.minute - (now.minute % 5)
minute_start = (minute_end - 5) if minute_end else 55
now_end = now.replace(minute=minute_end, second=0, microsecond=0)
now_start = now.replace(minute=minute_start, second=0, microsecond=0)
cursor.execute("INSERT INTO statistics_short_term (state, sum, metadata_id, created_ts, start_ts) VALUES(%s, %s, %s, %s, %s)",
(lastValidState, lastValidSum, metadata_id, now_end.timestamp(), now_start.timestamp()))
def recalculateStates(cursor, metadata_id: int, roundDigits: int):
print(f" Fixing table states")
cursor.execute("SELECT state_id,state,old_state_id,attributes_id FROM states WHERE metadata_id=%s ORDER BY state_id",
(metadata_id,))
result = cursor.fetchall()
try:
current_state = float(result[0]['state'])
attributes_id = result[0]['attributes_id']
except ValueError:
sys.exit(" [ERROR]: Cannot fix this entity because first entry in table 'states' is not a number! Sorry!")
for index, row in enumerate(result[1:]):
pre_row = result[index]
state_id = row['state_id']
state = row['state']
old_state_id = row['old_state_id']
attr_id = row['attributes_id']
if old_state_id is None:
cursor.execute("UPDATE states SET old_state_id=%s WHERE state_id=%s", (pre_row['state_id'], state_id))
if attributes_id != attr_id:
cursor.execute("UPDATE states SET attributes_id=%s WHERE state_id=%s", (attributes_id, state_id))
if state is None or not state.replace(".", "", 1).isdigit():
roundedValue = f"{current_state:.{roundDigits}f}"
print(f" Updating {state_id} = {state} -> {roundedValue}")
cursor.execute("UPDATE states SET state=%s WHERE state_id=%s", (roundedValue, state_id))
continue
state = float(state)
if state < current_state:
if pre_row['state'] and pre_row['state'].replace(".", "", 1).isdigit() and state >= float(pre_row['state']):
current_state += (state - float(pre_row['state']))
roundedValue = f"{current_state:.{roundDigits}f}"
print(f" Updating {state_id} = {state} -> {roundedValue}")
cursor.execute("UPDATE states SET state=%s WHERE state_id=%s", (roundedValue, state_id))
continue
current_state = state
def fixLastValidState(entity_id: str, lastValidState: str):
with open(RESTORE_STATE_PATH, "r") as file:
restore_state = json.load(file)
for state in restore_state["data"]:
if state["state"]["entity_id"] == entity_id:
state["state"]["state"] = lastValidState
state["extra_data"]["native_value"]["decimal_str"] = lastValidState
state["extra_data"]["last_valid_state"] = lastValidState
break
with open(RESTORE_STATE_PATH, "w") as file:
json.dump(restore_state, file, indent=2, ensure_ascii=False)
def getEntitiesPrecision() -> dict[str, int]:
returnDict = dict()
with open(CONFIG_ENTRIES_PATH, "r") as file:
configEntries = json.load(file)
with open(ENTITY_REGISTRY_PATH, "r") as file:
configEntities = json.load(file)
configIds = dict()
for configEntry in configEntries["data"]["entries"]:
number = configEntry["options"].get("round", -1)
if number == -1:
continue
configIds[configEntry["entry_id"]] = int(number)
for configEntity in configEntities["data"]["entities"]:
if configEntity["config_entry_id"] not in configIds:
continue
entity_id = configEntity["entity_id"]
config_entry_id = configEntity["config_entry_id"]
# Store precision and entity_id
returnDict[entity_id] = configIds[config_entry_id]
# Return dict with format {entity_id: precision}
return returnDict
if __name__ == "__main__":
# Call main function
main()
# Exit with positive return value
sys.exit(0)
Thanks for the code snippet!
@erik7
I plan to give an option to fix any entity in your database. And also include the possibility to connect to a database server using the code provided by @fversteegen
I hope that everybody can benefit this way.
I am trying to do it this weekend
What is a bit strange is that some sensors do not get corrected as EntityPrecision kicks in. Am quite certain they are integration sensors though…
Looking forward to it. 
Thank you very much. 