16 dec 2015, James K. Lowden: > On Fri, 11 Dec 2015 16:21:30 +0200 > "Frank Millman" <frank at chagford.com> wrote: > >> sqlite> UPDATE fmtemp SET balance = balance + 123.45; >> sqlite> SELECT bal FROM fmtemp; >> 5925.59999999999 > > To a question like that you'll receive a lot of answers about > numerical > accuracy. And it's true that there are ways to "do the math" without > using floating point representation. It's also true that it's rarely > necessary, which is why floating point representation exists and *is* > widely used. You may find it works for you too, unless you have to > adhere to a specific rounding policy. > > Per your example, you're working with 2 decimal places of precision. > 5925.59999999999 rounds off to 5925.60; it even rounds off to > 5925.6000000000, not too shabby. If you keep adding 123.45 to it, > you'll find you can go on forever before the answer is wrong in the > second decimal place. > > IEEE 754 is a solid bit of engineering. It's capable of representing > 15 decimal digit of precision. That's good enough to measure the > distance to the moon ... in millimeters. > > You could have an exceptional situation, but that would be > exceptional. Usually, double-precision math works just fine, provided > you have some form of round(3) at your disposal when it comes time to > render the value in decimal form. > > --jkl Hello, so in short, rounding the column anywhere it is used, is another solution. I confirmed this below. Thanks, E. Pasma.
BEGIN; UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; (repeat a 1.000.001 times END; SELECT bal FROM fmtemp; 123450123.45
