On Thu, 1 Dec 2016 16:50:21 -0800
Jens Alfke <j...@mooseyard.com> wrote:

> I understand that the consensus among those who do is to _not_ store
> monetary values as floating-point, due to the roundoff error. 

I wouldn't be so sure.  I've worked in that space for 30 years designing
databases and applications.  I don't remember once representing money as
anything but floating point, except on mainframes.  Occasionally we
used exact-decimal database types for bulk-load tables, to verify the
vendor's promised 18,6 scaling factor.   But in production?  For
computation?  Floats, always and ever.  

I have seen quite a few discussions here suggesting integers instead
because they're exact.  To me, that sounds like nothing so much as
Charles Moore.  He made a virtue of necessity: since Forth had no
floating point, he recommended using integers, and keeping track fo the
decimal place yourself.  

The difficulty in using floating point for money is *not* accuracy.
It's equivalency.  The first-year programmer soon learns not to test
for equivalency using "=", but by the roundabout process of comparing
the absolute difference to an epsilon, usually about 1E-6.  

What's much more problematic is keeping anything, including money, in a
nonstandard format, and introducing bespoke logic to manage the decimal
place.  Opportunities abound for error and misunderstanding, and
instead of being off by a penny, you're off by 10 or 100 times.
Percentages were a great one for that.  Pounds and pence in Ireland,

Not to put words in Keith's mouth, I think his point is that if you're
surprised about floating point behavior in SQLite, you haven't mastered
your trade.  It's not a database problem; it's a question of
understanding how IEEE floating point works.  The decision to use
integers or strings instead will only yield different, and worse,

sqlite-users mailing list

Reply via email to