Page 1 of 2
Water meter weird cost calculation
Posted: Monday 24 February 2025 11:21
by Ragdag
I'm using a ToonWater for some time now and it works fine, but I just looked at the Water meter report and the cost calculation is really weird.
This is the setup in the Domoticz Settings
This is the Water meter device
This is the cost report, how on earth is the cost so high?
17m3 should be €18.41
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 12:34
by FireWizard
Hi,
You have set your "Counter Divider" to 1000, but you have also setup the divider in "RFXMeter/Counter/Dividers" for Water to 1000
Change it in the "Device Setting" to 0 (0 = Application default) instead of 1000.
Regards
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 13:01
by Ragdag
Will give it a try, when are those reports updated to reflect new values?
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 13:05
by waltervl
Does it have the same kind of cost deviation in the water meter graphs (month/year)?
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 13:15
by waltervl
How is your daily report looking like? Does the counter and daily usage match each other?
Below a screenshot of my test environment where I apearantly fill the counter incorrectly with a script and sometimes counter and usage does not match. So cost calculation is incorrect (1 m3 = €1,5)
- Screenshot_20250224-130908.Firefox~2.png (284.58 KiB) Viewed 1216 times
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 14:26
by Ragdag
waltervl wrote: ↑Monday 24 February 2025 13:05
Does it have the same kind of cost deviation in the water meter graphs (month/year)?
It does not appear to be, but I also changed my cost per m3 today. It was set around 0.80 m3 and I have now set it to the 1.40m3 as that is more inline with the actual cost including tax etc.
The daily counter
If I go back to an earlier year, it seems to be okay
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 19:05
by waltervl
My first initial tests on a water counter shows correct costing data.
Perhaps check your database in the cost column to see what is going on. You can make a backup through menu Setup - settings and open the backup database with online sqlite viewer:
https://sqliteviewer.app/
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 19:19
by Ragdag
waltervl wrote: ↑Monday 24 February 2025 19:05
My first initial tests on a water counter shows correct costing data.
Perhaps check your database in the cost column to see what is going on. You can make a backup through menu Setup - settings and open the backup database with online sqlite viewer:
https://sqliteviewer.app/
Are those reports calculated on the fly, or is there a schedule during the night when it rolls over?
I'll open the db in DBeaver.
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 20:16
by waltervl
There is a cost column in meter table and a daily nightly rollup to the meter_calendar table.
The cost column is introduced somewhere in a 2024 release so empty for old values.
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 21:10
by Ragdag
waltervl wrote: ↑Monday 24 February 2025 20:16
There is a cost column in meter table and a daily nightly rollup to the meter_calendar table.
The cost column is introduced somewhere in a 2024 release so empty for old values.
Okay, so the cost is calculated at night and put in the column, so it is not retroactively recalculated if I change the cost per m3?
Re: Water meter weird cost calculation
Posted: Monday 24 February 2025 23:31
by waltervl
The cost is calculated per 5 minutes in meter table and rolled up at midnight and put in meter_calendar table.
Re: Water meter weird cost calculation
Posted: Tuesday 25 February 2025 12:13
by Ragdag
waltervl wrote: ↑Monday 24 February 2025 23:31
The cost is calculated per 5 minutes in meter table and rolled up at midnight and put in meter_calendar table.
So once the cost is in the DB, it is not affected anymore by price changes?
If that is the case I think I understand what has happened
Re: Water meter weird cost calculation
Posted: Tuesday 25 February 2025 12:26
by waltervl
Correct, the cost is calculated on the actual tariff, according the dynamic cost price settings in menu Setup Settings, tab counters.
For water there is only the water tariff in settings, no possibility to use a specific dynamic water tariff device.
Re: Water meter weird cost calculation
Posted: Tuesday 25 February 2025 12:28
by Ragdag
Awesome, I now know where and how to look.
Many thanks, as always.
Re: Water meter weird cost calculation
Posted: Sunday 02 March 2025 14:20
by Ragdag
Problem:
One of my devices (DeviceRowID 570) had incorrect cost values stored in the database. I needed to recalculate all price values for this device using a multiplier of 0.00141.
Solution:
I used DBeaver to connect to the SQLite database and executed the following SQL query:
Code: Select all
UPDATE Meter_Calendar
SET Price = ROUND(Value * 0.00141, 4)
WHERE DeviceRowID = 570;
This query:
- Selected all records for device #570
- Multiplied each Value by 0.00141
- Rounded the result to 4 decimal places
- Updated the Price column with this new calculated value
Optimization:
After making these changes, I also ran some database optimizations to ensure everything would run smoothly:
The VACUUM command rebuilds the database file, reclaiming unused space and reducing fragmentation. The ANALYZE command updates statistics that help SQLite choose efficient query plans.
This solution allowed me to correct historical cost data without having to delete and recreate any entries.
Note: Always back up your database before making direct SQL modifications!
Re: Water meter weird cost calculation
Posted: Monday 03 March 2025 10:41
by gizmocuz
Or you can use 'SQLite Expert Personal Edition' for a free database management application
Re: Water meter weird cost calculation
Posted: Monday 03 March 2025 10:43
by Ragdag
gizmocuz wrote: ↑Monday 03 March 2025 10:41
Or you can use 'SQLite Expert Personal Edition' for a free database management application
Dbeaver Community is open source and free also
Re: Water meter weird cost calculation
Posted: Monday 03 March 2025 14:54
by gizmocuz
Hmmm strange, went to their website
https://dbeaver.com/
And you have to pay for the 'lite' version as well...
If it's working, its working
Re: Water meter weird cost calculation
Posted: Monday 03 March 2025 15:32
by Ragdag
gizmocuz wrote: ↑Monday 03 March 2025 14:54
Hmmm strange, went to their website
https://dbeaver.com/
And you have to pay for the 'lite' version as well...
If it's working, its working
https://dbeaver.io/download/
Re: Water meter weird cost calculation
Posted: Monday 03 March 2025 16:47
by gizmocuz
Thanks! Tried it, and I stick to SQLite Expert Personal Edition for 100.000% sure.... pffff