Welcome to WebmasterWorld Guest from 54.224.13.210

Forum Moderators: open

Message Too Old, No Replies

Floats and stupid e notation

httpwebwitch

4:15 am on Aug 10, 2009 (gmt 0)

Senior Member from CA

joined:Aug 29, 2003
posts:4061

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

8:08 am on Aug 12, 2009 (gmt 0)

Junior Member

joined:May 25, 2006
posts: 182

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

11:28 am on Aug 12, 2009 (gmt 0)

Senior Member from CA

joined:Aug 29, 2003
posts:4061

so true, I could use INT to count pennies.

MYSQL recommends using DECIMAL (10,2)

PCInk

11:47 am on Aug 12, 2009 (gmt 0)

Senior Member from GB

joined:Aug 13, 2003
posts:1032

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

1:26 pm on Aug 12, 2009 (gmt 0)

Senior Member from CA

joined:Aug 29, 2003
posts:4061

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

3:56 pm on Aug 12, 2009 (gmt 0)

Moderator from US

joined:Dec 10, 2005
posts:5702

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

6:56 pm on Aug 12, 2009 (gmt 0)

Senior Member from CA

joined:Aug 29, 2003
posts:4061

so, you also saw Superman III

LifeinAsia

7:02 pm on Aug 12, 2009 (gmt 0)

Moderator from US

joined:Dec 10, 2005
posts:5702