I had accidentally configured a power meter with yearly reset interval. I noticed that a month later, and created a new one for monthly values. However, it would be great to be able to use the previous month’s data, so I wonder if there is any way to change the reset cycle and migrate data.
I dug a little bit in my database (I use MariaDB as backend). In table statistics, I see both meters, with “sum” and “state”. “sum” seems to be ever increasing, while “state” is the value within the reset interval. “state” is reset at midnight of the next interval to 0. I can see this in the monthly meter, but not the yearly (a year is not over). Thinking as developer, I could modify the database to the yearly meter so that “state” is consistently being reset at month change, and the values being recalculated for each hour. IMHO this is a bit clumpsy, but would do the job. Does that sound good?
However, I also would need to change the reset interval inside the configuration. I have not yet found how to do that. Any advise would be appreciated here. Note that the meters were defined via the GUI.
Even if this works, I wonder if there is an easier way to do this reset interval change. I would guess that my mistake could be pretty common.
Are you talking about the Utility Meter integration/helper?
That measures energy, not power. Two different things. Do not confuse them.
You can change the interval and keep all your history if you define the Utility Meters in YAML. The UI is a much more simplistic approach and it will be a struggle to work around this by altering the recorder database.
As I said, I already have valuable data in this GUI defined utility meter. As such, I am not looking to set up a new one, but how to change the cycle for the existing one from “none” to “monthly” and “rewrite history” (records), so that the existing data is reprocessed according to the monthly cycle.
No, it will not rewrite/recalculate past data. It will only change the reset time for the next events.Be careful editing that file, mistakes have the potential to prevent your system from starting.
So I guess my only option is indeed to recalculate the recorder data inside the database plus change that setting. At least I now have a plan.
I’ll do the recalculation in an online backup database, just for the records. So I can switch tables back and forth and notice any issues before they cause harm.
Thanks for your help!
Rainer
PS: Do you think it would make sense to file a feature request for automatically doing such changes?
Just in case someone else is interested, I modified the recorder database with this python3 script below. The script has minimal dependencies, is intentionally written in very basic python and worked well.
NOTE: Be sure to have a backup in place before trying this!
Also note that you must modify the metadata_id, 320 is my actual value. You can query your metadata_id from the statistics_meta table.
DO NOT TRY THIS SCRIPT if you do not feel confident working with MySQL/MariaDB at the native SQL layer, including backup and restore procedures.
I actually copied the database first and then worked on a copy of the statistics table to verify everthing ran OK.
import MySQLdb
from datetime import datetime
# Database connection details
db_config = {
'user': 'usr',
'password': 'pwd',
'host': '127.0.0.1',
'database': 'homeassistant'
}
# Connect to the database
connection = MySQLdb.connect(**db_config)
cursor = connection.cursor()
# Fetch data with primary key `id`
query = """
SELECT id, start_ts, sum, state
FROM statistics
WHERE metadata_id = 320
ORDER BY start_ts;
"""
cursor.execute(query)
rows = cursor.fetchall()
# Variables to track the last sum at the end of each month
last_month = None
previous_sum = None
# Loop over each row, calculate the corrected_state, and update the database
for row in rows:
record_id, start_ts, sum_value, original_state = row
readable_start = datetime.fromtimestamp(start_ts)
# Determine if we are in a new month
current_month = readable_start.month
current_year = readable_start.year
if last_month is None or (current_month != last_month[0] or current_year != last_month[1]):
# New month - reset by setting the previous month's end sum as baseline
baseline = previous_sum if previous_sum is not None else sum_value
corrected_state = round(sum_value - baseline, 5)
else:
# Continuing in the same month
corrected_state = round(sum_value - baseline, 5)
# Update tracking variables
last_month = (current_month, current_year)
previous_sum = sum_value
# Update the database with the corrected_state
update_query = """
UPDATE statistics
SET state = %s
WHERE id = %s;
"""
cursor.execute(update_query, (corrected_state, record_id))
# Commit the updates to the database
connection.commit()
# Close the connection
cursor.close()
connection.close()
Looks like this solution alone is not sufficient. Each hour, the current month is reset to the full meter value. It looks like the statistics_short_term table also needs to be adjusted. I suspect values from there are carried over to statistics.
And advise is appreciated, in any case I keep on digging in these somewhat muddy waters (for me ;)).