Final comment… my day runs at an end…meaning I seem to have a life to aside all of this…will return possibly tomorrow to look at the source and provide pointers.
Can you point me to the export mechanisma sqlite > mariadb ?
No worries, thanks for helping me
Here are the 2 files,
https://www.dropfiles.pro/S3bCorGfgoNhE2J
this is how i do the export
# Install pre-req
apk add python3
apk add py3-pip
pip3 install sqlite3-to-mysql
# run migration (CHANGE PASSWORD)
sqlite3mysql \
--sqlite-file /config/home-assistant_v2.db \
--mysql-database homeassistant \
--mysql-host core-mariadb \
--mysql-user homeassistant \
--mysql-password 'YOURPASSWORD' \
--ignore-duplicate-keys
That device where the sql is from is my off peak energy, its setup at 0.40€ per kWh in the energy dashboard, fixed price
Can’t read this 2nd file, please try as CSV or so
here its updated in csv
https://www.dropfiles.pro/jUf8o8caycet4eu
I did change my gas cost device in the mysql back to where it was. now i did use gas again and home assistant miscalculated it again… so there is something wrong somewhere else…
Correction…noticed rar… but… the export seems to be mariadb too and I expected your sqlite source
its sqlite, the csv is sqlite and the .sql is mariaDB
Summarizing, I would like to see both 100% source and 100% target next to one another and try to explain…the target sofar surprises me
EDIT: out now for the next 12+ hrs…
here is the link for both first 1000 rows.
https://www.dropfiles.pro/QHfFrvJjzOKv4Vp
but no worries, just let me know when u have time, no rush
the metadataID is 5 thats the Offpeak device,i did double check, the ID is correct.
So, a bit more time without other things… I cannot explain why this happens but as you already noticed from the graph there was an ‘reset’ at 13:42,
Q: was this around the moment you imported?
The data shows multiple resets (I have no clue why it resets) and from what I see when it jumps from growing figures to lower… But in all other it picks up correctly after the second entry with the calc/formula…only here it first deviates quite substantially and then the calc is completely off.
Guesswork that it is the sensor data causing it. i.e. they are out of sync… question is ‘why’
Q: When you exported the data to mysql format, how long did this take or more important… did you shutdown HA before you did this?
Q: Can you check the statistics for both sensor and cost?
select * from statistics where metadata_id in (5,SENSORID) order by created desc
well i have an interesting bit of info to update.
I did delete all the monetary device tables in the mariaDB so that everything is 0.
I setup the costs in the dashboard like this:
0.40 for peak/offpeak and consume and produce
and tot test 1.85m3 gas
Guess what happens, after a update i get messed up calculation, even when starting with no data.
Q: was this around the moment you imported?
yes
Q: When you exported the data to mysql format, how long did this take or more important… did you shutdown HA before you did this?
Yes i did first copy the database to an other folder and did the conversion
Q: Can you check the statistics for both sensor and cost?
Well, i dropped all monetary tables, so that no tables exist, and home assistant re-creates the data wrong, check screenshot.
So, a bit comes from statistics_short_term table…did you clean that too?
And not so good news for me as well, I have a odd situation too. I manually add meter readings and till now fine, yesterday it subtracted 153 eur…so it may be HA that is beeping things up…@&^#@
Maybe we just found a bug in HA ?
and this screenshot is from an other system with the same data but with a fresh mariaDB, without the convertion, see the difference…
You can see it resets all the time, and the time-stamps are weird.
the last reset of my working system was at 22th this month
while this migrated system with a wiped db, resets many times a day
My stats short term was screwed up and no clue where it got its figures from
But it seems that this populates statistics main table as I first repaired main table values, then took a bit of time to repair short-term…in the meantime main table again got the wrong value… it is a mystery to me what/how this is working but the repair sucks.
At somewhat the same time (1 min apart) I registered my water meter, also manual, same sensor setup and all… no problems there, really odd
Yeah but it ‘repairs’ itself too, I showed you my calc which I ran on the whole of your table, only the last 3 rows preseted odd data, guesswork this too from short_term?
EDIT: possibly the reset is when it cleans the stats_short_term, it copies some stuff over to stats … I need to have more time for this too…out now for the rest of the afternoon…sigh 