[sqlite] Problem with accumulating decimal values
On 18 Dec 2015 at 00:06, Keith Medcalf wrote: >> I was taught "Round [only] before printing.". These days it would be >> something like "Round [only] before your API returns to the calling >> program. > > Those are not the same. Round only before printing (whether to the screen or > to a printer). In other words, rounding is a way to make things palatable for > humans. Therefore, the rounding function should only be used when the next > step is presenting the value to a human. > > If the value is not being directly presented to a human, then you should not > be rounding (yet). > > In other words, you do not apply the rounding when your API returns (for > example, in the sin() function). You apply rounding only when the next > consumer is a human. If there is the slightest possibility that the next > consumer is not a human, you should not be rounding. And the rounded value should *only* be used for that purpose. It should not be kept for future calculations. -- Cheers -- Tim
[sqlite] Problem with accumulating decimal values
On Thu, 17 Dec 2015 13:13:24 Simon wrote: >to round only when you need to be handling integers. Round the result to maximum two digits after the decimal point (or comma) when it handles about money. Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse, Klaas `Z4us` V ?- LinkedIn# 437429414
[sqlite] Problem with accumulating decimal values
> I was taught "Round [only] before printing.". These days it would be > something like "Round [only] before your API returns to the calling > program. Those are not the same. Round only before printing (whether to the screen or to a printer). In other words, rounding is a way to make things palatable for humans. Therefore, the rounding function should only be used when the next step is presenting the value to a human. If the value is not being directly presented to a human, then you should not be rounding (yet). In other words, you do not apply the rounding when your API returns (for example, in the sin() function). You apply rounding only when the next consumer is a human. If there is the slightest possibility that the next consumer is not a human, you should not be rounding.
[sqlite] Problem with accumulating decimal values
On 17 Dec 2015, at 4:22am, R Smith wrote: > it was a seemingly too-convoluted detour for simply saying: "Don't store > rounded numbers. Round only the results." I was taught "Round [only] before printing.". These days it would be something like "Round [only] before your API returns to the calling program. Another way to think of it is to ask yourself "At this point, should I be processing reals or integers ?" and to round only when you need to be handling integers. Simon.
[sqlite] Problem with accumulating decimal values
On 2015/12/17 3:26 AM, James K. Lowden wrote: >> Calculated errors are fine because we can at any time revisit the >> calculation procedures, we can refine and perhaps opt for more >> significant digits - but we can ALWAYS guarantee the accuracy-level >> of the calculated result. However, storing wrong values (or let's >> call them "approximate" values if you like) is pure evil. > I'm not sure what you mean. Yes, it was quite misunderstood - my apologies though, it was a seemingly too-convoluted detour for simply saying: "Don't store rounded numbers. Round only the results." (Which I'm hoping we do agree on).
[sqlite] Problem with accumulating decimal values
On 2015/12/16 4:05 PM, E.Pasma wrote: > 16 dec 2015, Keith Medcalf: >>> BEGIN; >>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; >>> (repeat a 1.000.001 times >>> END; >>> SELECT bal FROM fmtemp; >>> 123450123.45 >> >> You should NEVER round as you have done above. You may get lucky and >> the errors may cancel each other out, or you may get more usual >> results where the error equals the theoretical max of the sum of the >> absolute value of all the truncated values, which can be quite >> significant depending on the scale of the number you are dealing with >> (and theior scales relative to each other). > > > Hello, I was only trying to digest JKL's post and the result looks > good. The example prints the value as it is in the database and shows > that there is no accumulated error there. I do not see a counter > example (not yet). > > Ok this does not work of any scale of numbers. But a solution with > integers neither does I think the bit that Keith tried to highlight is that we should always refrain from storing errors. Calculated errors are fine because we can at any time revisit the calculation procedures, we can refine and perhaps opt for more significant digits - but we can ALWAYS guarantee the accuracy-level of the calculated result. However, storing wrong values (or let's call them "approximate" values if you like) is pure evil. If the data that feeds my calculator is flawed by whichever tiny amount, or stored with errors, or retrieved with errors, then there is pretty much NOTHING I can do to revisit the original / "really really real" values of what actually happened or in any way confirm any degree of certainty on my calculation, because my axioms are wrong. And let's be clear*, No computer value is absolute in accuracy - but in the same way that 1.6667 is closer to the real value than 1.67, in computer and IEEE:754 terms, 1.29978 might well be much closer to 1.3 than the 1.30010378 which may be the next representable IEE:754 bit formation that gets stored when you put "1.3" into a float database field. * I'm just using artistic license here, did not calculate the real values, but the principle remains - I think Keith posted a way of finding the representable minimum differences between specific IEE:754 representations recently, if anyone is interested in the actual values. Cheers, Bag-of-water-Ryan. :)
[sqlite] Problem with accumulating decimal values
On Wed, 16 Dec 2015 15:05:34 +0100 "E.Pasma" wrote: > 16 dec 2015, Keith Medcalf: > >> BEGIN; > >> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; > >> (repeat a 1.000.001 times > >> END; > >> SELECT bal FROM fmtemp; > >> 123450123.45 > > > > You should NEVER round as you have done above. You may get lucky > > and the errors may cancel each other out, or you may get more > > usual results where the error equals the theoretical max of the sum > > of the absolute value of all the truncated values, which can be > > quite significant depending on the scale of the number you are > > dealing with (and theior scales relative to each other). > > Hello, I was only trying to digest JKL's post and the result looks > good. The example prints the value as it is in the database and > shows that there is no accumulated error there. I do not see a > counter example (not yet). > > Ok this does not work of any scale of numbers. But a solution with > integers neither does Keith's advice is well taken. Keep the real number; round for presentation. I always say, "store what you know". Yes, starting from zero you can add 123.45 to a double-precision floating point number for a very, very long time, about 81,004,455,245 times, before the error will appear in pennies. When it does, you'll have 13 digits left of the decimal. That's on the order of the US GDP. We don't measure things like that down to the penny, so no one will know if you're wrong. ;-) The thing to keep in mind is that you get ~15 decimal places of precision. The decimal floats. You can put it way on the left, and measure tiny things accurately. You can put it on the right, and measure astronomical things accurately. Unless you care about millimeters to the moon, it will do the job. Whole books have been written on numerical accuracy. I suspect if that mattered to your application you'd know about it. My advice is to let the engineers worry about it -- they did, years ago -- and accept rounded output unless and until you have an example of a computation for which that doesn't work. --jkl
[sqlite] Problem with accumulating decimal values
On Wed, 16 Dec 2015 20:33:40 +0200 R Smith wrote: > > Ok this does not work of any scale of numbers. But a solution with > > integers neither does > > I think the bit that Keith tried to highlight is that we should > always refrain from storing errors. Keith recommended against storing *rounded* values. If you store $0.30 in SQLite as REAL, you store a binary approximation. It's a fine thing to keep unless you care about picodollars. > Calculated errors are fine because we can at any time revisit the > calculation procedures, we can refine and perhaps opt for more > significant digits - but we can ALWAYS guarantee the accuracy-level > of the calculated result. However, storing wrong values (or let's > call them "approximate" values if you like) is pure evil. I'm not sure what you mean. There's no problem storing a C double from memory and later fetching it. The same 64 bits pass through the interface unchanged. (Well, maybe not the *same* bits, but who can tell?!) Once replaced back in C memory, the computation can resume where it left off unaffected. What you usually don't want to do is compute based on rounded numbers. If you store a rounded number to the database, you may lose information. Even if you don't -- even when the rounded number is the right one -- such errors as accumulate at the edge of accuracy normally wind up not mattering. That's why C does all computation in double precision, even when the operands are single-precision. The opposite mistake -- losing information -- can easily lead to results that are spectacularly wrong. --jkl
[sqlite] Problem with accumulating decimal values
16 dec 2015, 16:17, Bernardo Sulzbach: > > On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma wrote: > >> Ok this does not work of any scale of numbers. But a solution with >> integers >> neither does >> >> E.Pasma >> > ...I like integer better than floating points and text for > currencies ... Good taste. I now see a counter example where a solution with rounded floating point columns goes wrong. This is with aggregate functions. Using SUM adds up the errors before rounding and that may be too late. Tnanks.
[sqlite] Problem with accumulating decimal values
On 16 Dec 2015, at 3:46pm, Adam Devita wrote: > As a matter of interest, when calculating interest on a sum of money > expressed in pennies, how do you handle int arithmetic truncating? > Is that an accounting design rule thing when dealing with fractions of > a penny to round? When writing accounting software, there will be a specific rule for rounding attached to each calculation. For instance a process for working out a mortgage will include its own instruction "once you have multiplied by the number of days, round down to the next dollar". But the rules for working out interest rates might state "round to the nearest cent, round half to even". Unfortunately there is no world-wide standard for these. There can be one rule for one country (State, industry, etc.) and another for another. Except in Europe where they are all meant to agree with one another. Simon.
[sqlite] Problem with accumulating decimal values
16 dec 2015, Keith Medcalf: >> BEGIN; >> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; >> (repeat a 1.000.001 times >> END; >> SELECT bal FROM fmtemp; >> 123450123.45 > > You should NEVER round as you have done above. You may get lucky > and the errors may cancel each other out, or you may get more usual > results where the error equals the theoretical max of the sum of the > absolute value of all the truncated values, which can be quite > significant depending on the scale of the number you are dealing > with (and theior scales relative to each other). Hello, I was only trying to digest JKL's post and the result looks good. The example prints the value as it is in the database and shows that there is no accumulated error there. I do not see a counter example (not yet). Ok this does not work of any scale of numbers. But a solution with integers neither does E.Pasma
[sqlite] Problem with accumulating decimal values
On Wed, Dec 16, 2015 at 1:54 PM, Simon Slavin wrote:
>
> On 16 Dec 2015, at 3:46pm, Adam Devita wrote:
>
> When writing accounting software, there will be a specific rule for rounding
> attached to each calculation. For instance a process for working out a
> mortgage will include its own instruction "once you have multiplied by the
> number of days, round down to the next dollar". But the rules for working
> out interest rates might state "round to the nearest cent, round half to
> even".
>
Exactly as Simon said, the rules **will** (or at least should) be part
of the requirement. Also, some cases tell you to preserve fractions
until a final rounding. So you will have to go with decimals or
"scale" your integers (multiply them by a power of ten) somewhere.
At least here in Brazil there seems to be a lot of: if it is money
going away {round down as many times as possible} if it is money
coming our way {round up as many times as possible}. I think many
other places will use this too.
--
Bernardo Sulzbach
[sqlite] Problem with accumulating decimal values
On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf wrote: > >> 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 > > Absolutely not! You should NEVER round the value and store it back in the > datastore. Rounding is ephemeral for the convenience of > ugly-bags-of-mostly-water who are fixed in their world-view so that data can > be DISPLAYED to them in a format that fits their limited view. > Although I agree about not rounding and updating the store with "corrected" values. I don't think there is a need to call the ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't want myself to see 22.99 instead of 23.00 in the frontends I use either. In a practical sense, I believe the latter reduces the amount of processing my brain has to do and I can better focus on what matters. But then again, just use string formatting on the view of the project. On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma wrote: > Ok this does not work of any scale of numbers. But a solution with integers > neither does > > E.Pasma > Preferences aside, no solution ever devised will work with **any** scale with numbers as we have finite data storage. That is very pedantic, but just to be clear. I like integer better than floating points and text for currencies, some will have other preferences, it does not really matter as long as we are not working together. -- Bernardo Sulzbach
[sqlite] Problem with accumulating decimal values
Hello ! I said once and I'll say again for some applications it would make sense to use _Decimal64 (_Decimal32, _Decimal128) instead of floating points. Even if it's done in software the performance is acceptable on most common cases. See a sqlite3.c/sqlite3.h modified to use "_Decimal64" instead of "double" at https://github.com/mingodad/squilu/tree/master/SquiLu-ext using it we can easily swap between "double"/"_Decimal64" by defining a macro "-DSQLITE_USE_DECIMAL=1". I wish it would be part of the official sqlite3 ! Cheers ! ?
[sqlite] Problem with accumulating decimal values
16 dec 2015, James K. Lowden: > On Fri, 11 Dec 2015 16:21:30 +0200 > "Frank Millman" wrote: > >> sqlite> UPDATE fmtemp SET balance = balance + 123.45; >> sqlite> SELECT bal FROM fmtemp; >> 5925.599 > > 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.599 rounds off to 5925.60; it even rounds off to > 5925.60, 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
[sqlite] Problem with accumulating decimal values
Good day, As a matter of interest, when calculating interest on a sum of money expressed in pennies, how do you handle int arithmetic truncating? Is that an accounting design rule thing when dealing with fractions of a penny to round? Is this an arbitrary quantization? Once upon a time there existed the Ha'penny https://en.wikipedia.org/wiki/Halfpenny_%28British_pre-decimal_coin%29 https://en.wikipedia.org/wiki/Half_cent_%28United_States_coin%29 I think the ugly-bags-of-mostly-water indirection was humorous. I found it funny. https://en.wikipedia.org/wiki/Home_Soil live long and prosper. Adam On Wed, Dec 16, 2015 at 10:17 AM, Bernardo Sulzbach wrote: > On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf wrote: >> >>> 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 >> >> Absolutely not! You should NEVER round the value and store it back in the >> datastore. Rounding is ephemeral for the convenience of >> ugly-bags-of-mostly-water who are fixed in their world-view so that data can >> be DISPLAYED to them in a format that fits their limited view. >> > > Although I agree about not rounding and updating the store with > "corrected" values. I don't think there is a need to call the > ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't > want myself to see 22.99 instead of 23.00 in the frontends I > use either. In a practical sense, I believe the latter reduces the > amount of processing my brain has to do and I can better focus on what > matters. But then again, just use string formatting on the view of the > project. > > On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma wrote: > >> Ok this does not work of any scale of numbers. But a solution with integers >> neither does >> >> E.Pasma >> > > Preferences aside, no solution ever devised will work with **any** > scale with numbers as we have finite data storage. That is very > pedantic, but just to be clear. I like integer better than floating > points and text for currencies, some will have other preferences, it > does not really matter as long as we are not working together. > > -- > Bernardo Sulzbach > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Problem with accumulating decimal values
> 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 Absolutely not! You should NEVER round the value and store it back in the datastore. Rounding is ephemeral for the convenience of ugly-bags-of-mostly-water who are fixed in their world-view so that data can be DISPLAYED to them in a format that fits their limited view. You should NEVER round as you have done above. You may get lucky and the errors may cancel each other out, or you may get more usual results where the error equals the theoretical max of the sum of the absolute value of all the truncated values, which can be quite significant depending on the scale of the number you are dealing with (and theior scales relative to each other).
[sqlite] Problem with accumulating decimal values
On Fri, 11 Dec 2015 16:21:30 +0200 "Frank Millman" wrote: > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5925.599 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.599 rounds off to 5925.60; it even rounds off to 5925.60, 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
[sqlite] Problem with accumulating decimal values
On Sat, Dec 12, 2015 at 6:51 AM, Darren Duncan wrote: > > Per another suggestion, the best workaround is to use an INTEGER type > instead, and store an even multiple of whatever your smallest currency unit > size is, eg cents rather than dollars. > As I understood, he is doing the math in Python and saving strings (that are produced by Python decimal arbitrary precision classes) to the database, what should work perfectly.
[sqlite] Problem with accumulating decimal values
From: Frank Millman Sent: Friday, December 11, 2015 4:21 PM To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] Problem with accumulating decimal values > Hi all > > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 interactive > terminal. > Many thanks for all the replies. I understand what is happening now. I have found an effective workaround. The Python sqlite3 module allows you to create a user-defined function that you can use from within SQL statements. I wrote a function that uses the Python Decimal module to perform the arithmetic and return the result, and it seems to work just fine. Thanks again. Frank
[sqlite] Problem with accumulating decimal values
Hello !then I did a modification to sqlite3 that basically define a new type "sqlite_double" and use it instead of "double" (#define sqlite_double? double) then I can redefine it to _Decimal64 (#define sqlite_double? _Decimal64) this way with a modern C compiler we can have sqlite3 using decimal arithmetic. There is some more macros not mentioned here (see at https://github.com/mingodad/squilu/tree/master/SquiLu-ext), I just compiled it with gcc 4.9.3 and executed the examples from this original thread and it works fine. I'll say again that I still think that is a good idea to replace "double" by "sqlite_double" to allow alternative decimal/floating point implementations (on my case _Decimal64). Cheers ! ?
[sqlite] Problem with accumulating decimal values
Frank, The problem you are having is due to SQLite not following the SQL standard regarding non-integral numeric types. The SQL standard specifies that the DECIMAL type is exact numeric and able to represent decimal numbers exactly. However, when you ask SQLite for a DECIMAL column, that is not what it will give you; instead, it will silently "succeed" but give you an inexact numeric type instead, a floating point number, as if you had said FLOAT/etc instead of DECIMAL. So the problem you are having is due to the actual numbers in the database not being what you told it to store, but just an approximation. Per another suggestion, the best workaround is to use an INTEGER type instead, and store an even multiple of whatever your smallest currency unit size is, eg cents rather than dollars. -- Darren Duncan On 2015-12-11 6:21 AM, Frank Millman wrote: > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 interactive > terminal. > > SQLite version 3.8.6 2014-08-15 11:46:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); > sqlite> INSERT INTO fmtemp VALUES (1, 0); > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 123.45
[sqlite] Problem with accumulating decimal values
Hi all I am having a problem accumulating decimal values. I am actually using Python, but I can reproduce it in the sqlite3 interactive terminal. SQLite version 3.8.6 2014-08-15 11:46:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); sqlite> INSERT INTO fmtemp VALUES (1, 0); sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 123.45 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 246.9 I repeat this a number of times, and it runs fine, until this happens - sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 5802.15 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 5925.599 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 6049.049 Can anyone explain what is going on, and is there a way to avoid it? Thanks Frank Millman
[sqlite] Problem with accumulating decimal values
Frank Millman Friday, December 11, 2015 9:21 AM > > I am having a problem accumulating decimal values. > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; SELECT bal FROM > sqlite> fmtemp; > 6049.049 > > Can anyone explain what is going on, and is there a way to avoid it? > It's because of the way that floating point math and display happens. Simply put, there's no way to avoid it if you really want to use floating point numbers. Bernardo's suggestion about using integer math may be a fairly easy workaround, especially if you're just adding numbers. -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
[sqlite] Problem with accumulating decimal values
I *think* this is due to you creating an integer when you first create the entries Try changing from INSERT INTO fmtemp VALUES (1, 0); to INSERT INTO fmtemp VALUES (1, 0.0); Just did macpro:js rwillett$ sqlite3 SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); sqlite> INSERT INTO fmtemp VALUES (1, 0.0); sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; Error: no such column: bal sqlite> SELECT balance FROM fmtemp; 123.45 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 1234.5 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 1357.95 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 4567.65 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45;
[sqlite] Problem with accumulating decimal values
Looks like you thought you could have a DECIMAL type (such as MySQL DECIMAL) here. But SQLite does not allow for this. My workaround usually is: create table accounts(account_number integer, balance integer); create view accounts_view as select account_number, balance / 100.0 from accounts; You may want to use text (or another relational system) if you get to gargantuan values as integer is limited to signed 8 bytes (which I think means up to positive 9223372036854775807, needs confirmation).
[sqlite] Problem with accumulating decimal values
Hi Frank, You want to store an INTEGER type using the lowest used unit (cents or mills). This page https://www.sqlite.org/datatype3.html may be of assistance next time -- Bernardo Sulzbach
[sqlite] Problem with accumulating decimal values
On Fri, Dec 11, 2015 at 8:18 AM, Adam Devita wrote: > A good start at the long answer can be found in the archives of this list. > > > http://sqlite.1065341.n5.nabble.com/Simple-Math-Question-td85140.html#a85157 > also found at > > https://www.mail-archive.com/sqlite-users at > mailinglists.sqlite.org/msg04587.html > (web search sqlite "simple math question") > > It has background, theory, and they show how the conversions of > decimals to floating point and how they add works, using several > examples. > +1 -- Scott Robison
[sqlite] Problem with accumulating decimal values
A good start at the long answer can be found in the archives of this list. http://sqlite.1065341.n5.nabble.com/Simple-Math-Question-td85140.html#a85157 also found at https://www.mail-archive.com/sqlite-users at mailinglists.sqlite.org/msg04587.html (web search sqlite "simple math question") It has background, theory, and they show how the conversions of decimals to floating point and how they add works, using several examples. regards, Adam D. On Fri, Dec 11, 2015 at 9:55 AM, Richard Hipp wrote: > On 12/11/15, Frank Millman wrote: >> >> Can anyone explain what is going on, and is there a way to avoid it? >> > > Short answer: https://www.sqlite.org/faq.html#q16 > > I don't have a longer answer readily at hand, but as questions about > floating point numbers come up a lot, probably I should write up a > tutorial. I'll try to get that done before the end of the year... > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Problem with accumulating decimal values
On 12/11/15, Frank Millman wrote: > > Can anyone explain what is going on, and is there a way to avoid it? > Short answer: https://www.sqlite.org/faq.html#q16 I don't have a longer answer readily at hand, but as questions about floating point numbers come up a lot, probably I should write up a tutorial. I'll try to get that done before the end of the year... -- D. Richard Hipp drh at sqlite.org
[sqlite] Problem with accumulating decimal values
On Fri, Dec 11, 2015 at 8:21 AM, Frank Millman wrote: > Hi all > > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 > interactive terminal. > > SQLite version 3.8.6 2014-08-15 11:46:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); > sqlite> INSERT INTO fmtemp VALUES (1, 0); > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 123.45 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT balance FROM fmtemp; > 246.9 > > I repeat this a number of times, and it runs fine, until this happens - > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5802.15 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5925.599 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 6049.049 > > Can anyone explain what is going on, and is there a way to avoid it? > > Thanks > > Frank Millman > > ?This is a common problem. It has been discussed here, and elsewhere, quite a bit. Basically, you want _decimal_ accuracy from a _binary_ floating point format. But _decimal_ floating point numbers may not have an exact _binary_ floating point representation. Perhaps these will be of some help: http://dba.stackexchange.com/questions/62491/why-does-sqlite-return-incorrect-sum http://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html (generic despite being from Oracle Corp.) The real solution is IEEE 754-2008 decimal floating point implementation. https://en.wikipedia.org/wiki/Decimal_floating_point https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library The only _hardware_ implementation that I know of for this format is from IBM, on their Power6 (and after) and z9 (and after) series machines. It is definitely not (yet) available on an Intel based machine. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
