is there an issue of rounding errors when using float math on database tables?
Using MySQL and Ignition V7.8.2.
I am keeping track of manually added ingredients added into fermentation tanks and updating an ingredient usage table by adding the ingredients entered and subtracting from the database usage table.
I am noticing rounding errors already starting:
[attachment=4]2016-07-05 09_05_19-192.168.11.10 - Remote Desktop Connection.png[/attachment]
actual record of ingredients added:
[attachment=3]2016-07-05 09_04_28-192.168.11.10 - Remote Desktop Connection.png[/attachment]
script to retrieve data entered and update database table:
[attachment=2]2016-07-05 09_08_06-192.168.11.10 - Remote Desktop Connection.png[/attachment]
database structure of ingredients entered from Ignition screen:
[attachment=1]2016-07-05 09_06_22-192.168.11.10 - Remote Desktop Connection.png[/attachment]
table structure to track ingredient usage:
[attachment=0]2016-07-05 09_06_52-192.168.11.10 - Remote Desktop Connection.png[/attachment]
There are always rounding errors when using floating point to track non-integer quantities. This is not a database or Ignition phenomenon. Computer floating point uses binary fractions, not decimal fractions, so there are many terminating decimal fractions that are repeating binary fractions. You can minimize the problem by using doubles instead of floats, but it won’t ever be perfect. To be precise, you should use fixed point arithmetic in an integer or big integer column. That is, define the units of the column as integer “microLiter” instead of float “milliLiter”, and integer “tenths of a pack” instead of float “packs”. The units you choose must accommodate the smallest quantity actually consumable, or the least common denominator of the possible quantities.
Most databases also offer some form of decimal fixed precision column datatype which does this under the hood and keeps track of the decimal place for you. Typically used in finance for currency calculations. They’re great as long as all calculations are done in the database, not in your scripts. (Moving data to and from such columns often re-introduces the rounding errors.)
[quote=“pturmel”]There are always rounding errors when using floating point to track non-integer quantities. This is not a database or Ignition phenomenon. Computer floating point uses binary fractions, not decimal fractions, so there are many terminating decimal fractions that are repeating binary fractions. You can minimize the problem by using doubles instead of floats, but it won’t ever be perfect. To be precise, you should use fixed point arithmetic in an integer or big integer column. That is, define the units of the column as integer “microLiter” instead of float “milliLiter”, and integer “tenths of a pack” instead of float “packs”. The units you choose must accommodate the smallest quantity actually consumable, or the least common denominator of the possible quantities.
Most databases also offer some form of decimal fixed precision column datatype which does this under the hood and keeps track of the decimal place for you. Typically used in finance for currency calculations. They’re great as long as all calculations are done in the database, not in your scripts. (Moving data to and from such columns often re-introduces the rounding errors.)[/quote]
Thanks for that.
I am aware that once a float becomes large and adding or subtracting small quantities, resolution will be lost, but I did not expect adding 4 x 0.5 values I am already out by 0.1