homepage Welcome to WebmasterWorld Guest from 23.22.179.210
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Floats and stupid e notation
httpwebwitch




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

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




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

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




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

so true, I could use INT to count pennies.

MYSQL recommends using DECIMAL (10,2)

PCInk




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

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




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

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




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

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




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

so, you also saw Superman III

LifeinAsia




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

They brought me in as an evil consultant. ;)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved