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, too. 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, problems. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
