Floats and stupid e notation

[httpwebwitch]

I had a cell in my database that contains a number. It's defined as a "float" datatype.
In it was a "money" amount, dollars and cents, so I'm pretty certain it only had two decimal places.

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:

1.78814e-09

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?

[OutdoorWebcams]

You could set the datatype to FLOAT (M,2) with M digits in total and 2 digits after the decimal point. Then the database should do the rounding and eliminate the annoying limited precision.

Or, as you store money amounts, use INT and store the amount in cents.

(Just my 2e+0 cents...)

[httpwebwitch]

so true, I could use INT to count pennies.

MYSQL recommends using DECIMAL (10,2)

[PCInk]

The problem with using a float to count money is that it isn't accurate.

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.

[httpwebwitch]

I'm embarassed to say that I never fully understood the whole floating point rounding deal, or rather I never took the time to grasp and comprehend it.

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.

thanks!

[LifeinAsia]

No- what you're supposed to do is write a subroutine that takes all the numbers to the right of the 2nd digit after the decimal point, add those amounts to the offshore bank account in the Cayman Islands and... Um, wait, sorry- posted in the wrong forum! ;)

[httpwebwitch]

so, you also saw Superman III

[LifeinAsia]

They brought me in as an evil consultant. ;)