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