Water meter weird cost calculation

[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

[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

[Ragdag]

Will give it a try, when are those reports updated to reflect new values?

[waltervl]

Does it have the same kind of cost deviation in the water meter graphs (month/year)?

[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 939 times

[Ragdag]

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

[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/

[Ragdag]

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.

[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.

[Ragdag]

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?

[waltervl]

The cost is calculated per 5 minutes in meter table and rolled up at midnight and put in meter_calendar table.

[Ragdag]

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

[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.

[Ragdag]

Awesome, I now know where and how to look.
Many thanks, as always.

[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:

Code: Select all

VACUUM;
ANALYZE;

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!

[gizmocuz]

Or you can use 'SQLite Expert Personal Edition' for a free database management application

[Ragdag]

Or you can use 'SQLite Expert Personal Edition' for a free database management application

Dbeaver Community is open source and free also

[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

[Ragdag]

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/

[gizmocuz]

Thanks! Tried it, and I stick to SQLite Expert Personal Edition for 100.000% sure.... pffff