Re: How useful is the money datatype?

On Sun, Oct 04, 2009 at 09:31:02AM -0700, Rich Shepard wrote:
> On Sun, 4 Oct 2009, Sam Mason wrote:
> >The point is that on *any* computer it's impossible to perform arbitrary
> >calculations to infinite precision (i.e. "without a loss of precision as
> >you put it).
>
> I've not followed this tread, but read this one message, so perhaps my
> comments are not appropriate. In that case, I apologize for jumping in.

More comments are normally good!

> Monetary values have always been an issue with computers. For a while, at
> least in the mainframe world of decades ago, binary-coded decimals (BCD)
> were a working approach.

I've never had to use BCDs for anything real, but I believe the
reason they're nice is that when you get a result you can't represent
accurately then it tends to get rounded to something that will always
look nicer than when you're working in base 2. PG's numeric type
effectively uses base 10 internally so would be a good fit for cases
when you used to use BCD numeric encodings before.

> In the early and mid-1980s we used a procedure for business applications
> involving money that worked regardless of programming language or platform.
> To each (float, real) monetary amount we added 0.005 and truncated the
> result
> to two digits on the right of the decimal point. In almost all cases, this
> allowed financial calculations to be correct to the nearest penny.

I was under the impression that floats have about 6 useful decimal
digits of precision, thus any calculations involving units of a 100
thousand or more would start to give arbitrary values to the cents.

> Financial calculations are still imperfect. Now and then I see this in
> both my business and personal bank statements when reconciliation is off by
> a penny or two. The transaction amounts (debits and credits) match, but the
> bank comes out with a different total than do I. This is usually only for a
> month or two before we are once again in agreement.

That seems to be the bug that Greg Stark noted in this thread; the bank
is probably storing values with more precision than it's choosing to
report to you. Thus the totals will drift into and out of being correct
over time.

--
Sam http://samason.me.uk/