Welcome to WebmasterWorld Guest from 18.104.22.168
Forum Moderators: open
Then I started to see some weird things in the UI. So I look in the database, and in the "balance" column, I see this:
What would cause that? I'm only ever adding and subtracting numbers with two decimal places, like -1.99 or 4 or 0.95
How do I prevent this from happening again?
Or, as you store money amounts, use INT and store the amount in cents.
(Just my 2e+0 cents...)
In decimal, one third is 0.33333333.... and it goes on forever.
In binary, the same thing happens when you store 0.1 - the binary goes on forever and therefore some of the number gets cut off.
If you add enough of these together repeatedly, the little bits that are cut off add up and cause problems. Imagine in decimal you store one third to two decimal places (i.e. 0.33). When you add three together you end up with 0.99 and not 1 which is the correct answer.
Because of this you should always store the pennies/cents rather than the dollars/pounds/euros if many calculations are to be performed on the amounts. The MYSQL DECIMAL(10,2) will be a quick way of telling the computer to do this without giving you lots of work, reformatting when outputting etc.
The amount showing with the 'e' is a very small number and possibly should be zero - perhaps caused by these rounding errors adding up.
Thanks for that explanation PCInk, it did help.
Now I understand that using FLOAT numbers, 0.1 + 0.1 != 0.2,
just as in decimal, 1/3 + 2/3 != 1
I've switched all my "money" columns to DECIMAL(10,2), and I haven't seen the error reappear.
joined:Dec 10, 2005