[TriLUG] Money in mysql

[Phillip Rhodes]

Matt Frye wrote:


> Good point.  

caveat: I know nothing about the problem you are trying to
solve, so all of this may be irrelevant. Ignore if it is.


The whole issue of handling currency in computer programs is
kinda complex.  Even if you store the values as an integer
number of cents,  you can still create a floating point
situation... for example, if you have 132 cents ($1.32)
and you need to divide by 7 for some reason, you'll still
get a floating point answer, something like:
18.857142857142857142857142857143, or so.  And because of limits
in the precision that can be represented, that's going to get
truncated to something... maybe 18.857142857.  Pretty precise,
but the problem then becomes if you multiply the result by
7, you won't get back your original number.  This can cause
unexpected glitches if you're not careful.

There are actually IEEE specs that define standards
for fp math, and all sorts of white papers, etc. out
there to help people with these considerations.

Depending on what language you're working in, there may
be a currency datatype built in to handle this stuff.  or not. :-)


And FWIW this is not something I'm an expert at.  I was
exposed to some of this when I asked a question similar
to your original question on the JUG list.  However we'd already
committed to storing our currency data in a DB/2 'number'
field and using the java 'float' datatype, so it was kinda
too late to redo everything.


 > I guess I could handle the conversions client-side.

It doesn't really matter where you do the conversions.
Although if you're talking about a browser based app,
I'm not sure you'd want to do any serious number
crunching / converting on the client side (in javascript
or whatever).  If you're building something client / server
with a thick client, then it might make sense.


TTYL,

Phil