I have an issue during a migration of my database or when i set back my backup.
My energy dashboard shows up fine, but the energy costs are shown way to high…
How can i correct this?
As the sensor.p1_gas_consumed_cumulative_cost device has really a lot of small entries end it does not match up with the set-up 0.80€ m3 cost in the energy dashboard…
But 50x higher
Regards
You would have to go into the db and update the values. What’s your db and do you know how to get ‘in’ and do you know SQL?
Well i use the normal DB from homeassistant.
But when i did migrate to MARIADB it messed up only the costs.
I have a monetary device, but you can’t correct the value’s at all.
i did check my database in phmyadmin as well, but there are tons of values that the monetary device is using
I did setup 0.80€ in the energy dashboard, but does not work, atleast not work after the migration…
We are discussing this in an other topic, as me and an other person have exactly the same issue, and we can’t figure out why.
thats why i did ask help for this issue seperatly here.
The devices in the energy dashboard uses the statistics table but you probably already figured that out and yes…that can contain a lot of values. Is it as simple as updating all of them in one go? i.e. all of them for A SINGLE device ?
Yes, but the editing is not that easy, as when i change that value, after 2 hours it again shows wrong…
I did also go into the energy dashboard and put again my m3 price and update. so after 2 hours same thing…
even when i manual edit it without doing the dashboard update, it will get messed up again.
And i don’t know why, it shows value from 3€ and 1.20€ i don’t even know where HASS gets that from…
thats why i reverted back to main hass DB
The thing I found out is that the order of the id in the statistics table matters, i.e. the newest values should have the newest ID too, not just the date matters (at least…not with me) and sum and state have to be updated both
Well its still weird, i did the conversion of my whole database, except the costs are shown wrong somehow, however the rest is all correct with no issue…
i need to find a solution for this.
I struggled with it too, I tried to add single lines with historical ‘paper’ entries but these then got a new ID and the whole thing went pearshaped…the order matters a lot id and data and sum and state work together was my finding
I suggest to play around with it, create a new manual costs sensor based on nothing and add data, for what I know you only need per-day , i.e. one line per day
Yes i already use also manua cost sensor and does the same thing, also for static…
But still why does it go wrong… could it maybe an issue that the home assistant db was still writing the data while converting ?
I’m now almost finished converting with some other settings, and see if it works.
otherwise ill do it with the recorder disabled.
i forgot to mention, all my costs are messed up, not only gas, but all of the monetary devices.
I have no clue, never migrated
What COULD be the case is that when adding measurements data…HA starts to calc itself and that messes it up?
i don’t know yet, i only know it updates every 2 hours when new data is received or managed.
lets hope after my first test i get it solved, as its annoying.
Yes it is… in the end I did the calc myself for each existing row (with SQL, not all manual)
repeating that the order is important
could you show me maybe a bit of data where it is wrong? i.e. database, not graphs
I just added a new line and in contrast what I said earlier, for COSTS the ID is no important, just the date
EDIT: so you could re-create it based on the measurements… I (again) agree that this is not nice when wanting to only migrate but it may be the only thing left to do
Well, i maybe have found the issue, but i wait 2 hours just to be sure.
i did remove this line of my conversation --ignore-duplicate-keys and it shows up correct now on my test system, with the correct costs.
SO what i think happened is when it does see suplicated value it did not overwrite the new one, just like you said it needs the new ID so i removed that line and i think it worked.
but i need to wait 2 hours now, lets hope, than you did help me solve the problem 
Fingers crossed then !
EDIT: btw, with me, when updating the costs in the DB, it shows immediately, of course…you want to know if the new entries are not beeping it up
Bad news… i did wait and then it got messed up… showing up values with 13€ each… its really weird where those value omes from.
also my energy costs are very high with all kind of values vary from 1.62 to 1.80 …
As I have no experience in migrating (HA) and no direct interest diving into its technicalities, can you possibly show me what you had in the source and now in the target? Add two zipped tables of ONE device? All in all, I would not see this as rocket science, you should be exporting from one and importing into another db, not?
The adjust stat is OK for one or two things to modify, if (almost) all is beeped-up then this probably won’t be of much help
Or…just send me the target one… just want to compare to what I have
