Hi all, I’ve recently set the water metering, and initially haven’t set the price for it. Now I added it, but would like to know how to add it for existing historic data. I do believe it can be done directly in MariaDB recorder DB, just need to point me to the right direction. Thanks a lot.
Indeed, you would have to update the specic sensor in the db, what I did is I remove all entries from the water_cost sensor (I donot know how your sensor is called but it has _cost), then used the water sensor value to insert the records back in to the _cost sensor with the price calc.
Ok, let’s be more specific…
SELECT * FROM statistics_meta WHERE statistic_id LIKE "%water_consumption_total%";
310 |sensor.water_consumption_total |recorder |m³ |0 |1 |NULL|
342 |sensor.water_consumption_total_cost |recorder |EUR |0 |1 |NULL |
SELECT * FROM statistics WHERE metadata_id=310 ORDER BY id DESC;
1324978 *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* 0.239 4.9770000000000065|1691564410.302072 1691560800 *NULL*
There are state and sum, no price, though.
SELECT * FROM statistics WHERE metadata_id=342 ORDER BY id DESC;
1324992 *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* 0.218079999999999 0.218079999999999 [342] 1691564410.302539 1691560800 1691499209.8823
There are state and sum as well, but can’t imagine how to INSERT values for older data… I can live without it, but wondering whether is possible to add or not 
The state of the cost is used when the sensor changes, e.g. you add 10l a 7:00 then it will add 10*price for the state in _cost at the same time, it will also increase the sum which is the increasing total
Two things:
- after removing all cost records, use metadata_id, state * price and sum* price from the sensor to insert the new values, all other should be the same
imo there is no risk as you are not using _cost anyhow, so you can repeat delete/uinsert this until it shows the expected result in the energy dashboard, nevertheless I would always have a backup
- note that there is a table statistics_short_term which also has data and this may screm up the NEXT entries in statistics, this you can likely correct via the GUI (devtools > statistics)
I found an old one which I used for my gas meter, I only wanted to update this from 1 Jan 2022, hence the data
delete from statistics where metadata_id = 10160
insert into statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) select created,start,mean,min,max,last_reset,state * 0.21,sum *0.21,10160,created_ts,start_ts,last_reset_ts from statistics where metadata_id=470 and FROM_UNIXTIME(start_ts) >= "2022-01-01"
3 Likes
Perfect, done. Thanks a lot!
vingerha, now I see negative price for today (expected), but can’t see anything to fix in Statistics GUI. Any suggestions? Maybe update short term data somehow?
That si probably due to the short term statisctis…di you try to change it via dev tools > statistics ?
Try to correct an entry via the statistics (shot above), you can add also negative value
I remember doing:
- stop HA for a while, to prevent short term stats
- remove short term stats for that sensor
- redo the insert as per above
I am sorry for not 100% but has been a while and it is not very intuitive
1 Like
Done, maybe I lost some eurocents, but who cares