Re: [sqlite] Question about floating point
On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf wrote: > > >And yet ... here we are. The post which started this thread summed > >currency amounts and reached a total of 211496.252 . > > >Yes, you can say 'that would have been rounded before it was > >printed'. But then you're into the old questions: do you round at > >every step, or only at the end ? Do you round or truncate ? Where > >does the fraction go ? etc. etc.. > > You apply half-even rounding (not elementary school 4/5 rounding) only for > display (output) and never round intermediates. The "fraction" does not > exist ... Though if you do 4/5 rounding rather than half-even rounding the > accumulated errors will amount to quite a sum. > TIL, thanks! I'd also like to point out a problem with integer money: inflation. For USD it's been OK so far, but imagine having to handle the Zimbabwean Dollar, which ended up having 100 trillion dollar notes. Good way to overflow your integers. With floating point, that's not a problem. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
On 16 Dec 2018, at 2:54pm, Wout Mertens wrote: > imagine having to handle the Zimbabwean Dollar, which ended up having 100 > trillion dollar notes. Good way to overflow your integers. Indeed. But when the crisis started in the early 2000s, the currency was devalued by 1000. Then ten zeros were wiped out at a stroke. And then another twelve zeros were slashed to make the "fourth Zimbabwe dollar". So if you were going to keep track of an account with Z$1 old and Z$1 new you'd need a precision capable of keeping track through 3+10+12 = 25 zeros, or amounts like 10,000,000,000,000,000,000,000,001 The precision of double-precision floats, 52-bit significand, is 2^52 = 4.5e15. Just to store one new Z$. If someone has ten new dollars in their account you could no longer keep their account even in double-precision floating point. You would have needed quad-precision (2^112 = 5.2e33), and banks weren't using that at the time even to do calculations. (I don't know what they use now, I'm not in the industry.) Didn't matter, anyway, since Zim banks just agreed to truncate amounts less than newZ$0.01 . Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
Just remember however that you only have 53-bits of precision, so in floating point adding a "small" number (eg, 0.01) to a big number (1e16) the result will be 1e16 not 1.1e16 whereas with scaled decimal the result will be precise and that unless special precautions are taken that the floating point sum of 1.0, 1e100, 1.0, -1e100 is 0, not 2.0, because the result is in the scale of the largest input, not the smallest. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens >Sent: Sunday, 16 December, 2018 07:55 >To: SQLite mailing list >Subject: Re: [sqlite] Question about floating point > >On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf >wrote: > >> >> >And yet ... here we are. The post which started this thread >summed >> >currency amounts and reached a total of 211496.252 . >> >> >Yes, you can say 'that would have been rounded before it was >> >printed'. But then you're into the old questions: do you round at >> >every step, or only at the end ? Do you round or truncate ? >Where >> >does the fraction go ? etc. etc.. >> >> You apply half-even rounding (not elementary school 4/5 rounding) >only for >> display (output) and never round intermediates. The "fraction" >does not >> exist ... Though if you do 4/5 rounding rather than half-even >rounding the >> accumulated errors will amount to quite a sum. >> > >TIL, thanks! > >I'd also like to point out a problem with integer money: inflation. >For USD >it's been OK so far, but imagine having to handle the Zimbabwean >Dollar, >which ended up having 100 trillion dollar notes. Good way to overflow >your >integers. > >With floating point, that's not a problem. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
> Good way to overflow your integers. > With floating point, that's not a problem. With int64, it shouldn't be a problem either. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
Ah, the luxuries of not programming in JavaScript ;) Anyway, using int64 would not have been sufficient to represent, say, tax numbers for the country, especially if you worked with cents. Whereas 53 bits of precision gets you a very long way and can even handle deflation The Wout. On Sun, Dec 16, 2018, 9:26 PM Thomas Kurz > Good way to overflow your integers. > > With floating point, that's not a problem. > > With int64, it shouldn't be a problem either. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
On 2018-12-17 02:41, Simon Slavin wrote: On 16 Dec 2018, at 2:54pm, Wout Mertens wrote: imagine having to handle the Zimbabwean Dollar, which ended up having 100 trillion dollar notes. Good way to overflow your integers. Indeed. But when the crisis started in the early 2000s, the currency was devalued by 1000. Then ten zeros were wiped out at a stroke. And then another twelve zeros were slashed to make the "fourth Zimbabwe dollar". So if you were going to keep track of an account with Z$1 old and Z$1 new you'd need a precision capable of keeping track through 3+10+12 = 25 zeros, or amounts like 10,000,000,000,000,000,000,000,001 The precision of double-precision floats, 52-bit significand, is 2^52 = 4.5e15. Just to store one new Z$. If someone has ten new dollars in their account you could no longer keep their account even in double-precision floating point. You would have needed quad-precision (2^112 = 5.2e33), and banks weren't using that at the time even to do calculations. (I don't know what they use now, I'm not in the industry.) Banks still use, as they have for a very long time, Binary Coded Decimal, or some equivalent that does not suffer from a loss of accuracy, so all this foofaraw to do with floating point representation of various amounts of currency does not apply to the real world. Cheers, GaryB-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
On 2018-12-15 2:15 AM, Frank Millman wrote: On 2018-12-14 11:24 AM, Darren Duncan wrote: If yours is a financial application then you should be using exact numeric types only, such as integers that represent multiples of whatever quantum you are using, such as cents; fractional numbers are a display or user input format only, and in those cases they are character strings. Thanks, Darren. In principle I agree with you, but I am experimenting with a different approach. My application supports SQL Server and PostgreSQL as well as sqlite3, and those databases do have exact numeric types for monetary use, and I am trying to stick to one code base as much as possible. The python sqlite3 module allows you to convert sqlite3 data to a python object, so my approach is to store decimal data as text in sqlite3, and convert it to a python Decimal object when reading it in. I find that this works ok. I do have a bit of trouble when using sqlite3 to ‘sum’ a column, as it then switches to floating point and can lose precision, but provided I convert the result back to a Decimal object with appropriate rounding it also works. Having said that, I am still testing, and I may decide that I have to bite the bullet and store everything as integers, in which case I will use the same approach for the other databases as well. Simon Slavin says ‘Currency amounts should be stored as integers’. Does this apply to sqlite3 specifically, or is that your recommendation for all databases? I think that can reasonably apply to all DBMSs, even ones supporting exact fractional numeric types. Keeping the money as integers everywhere for storage or where you do math, and character strings only for display to users, is a reasonable consistent strategy, that also scales more easily to handling multiple currencies. Its not just about the DBMSs. Some programming languages don't support exact fractional numbers either, and Javascript doesn't on the web client side if you do that. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
> Banks still use, as they have for a very long time, Binary Coded > Decimal, or some equivalent that does not suffer from a loss of > accuracy, so all this foofaraw to do with floating point representation > of various amounts of currency does not apply to the real world. > > Cheers, > GaryB-) > As do insurance companies and many in the manufacturing world (inventory). There is a lot to like about BCD. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
On 2018-12-16 6:54 AM, Wout Mertens wrote: I'd also like to point out a problem with integer money: inflation. For USD it's been OK so far, but imagine having to handle the Zimbabwean Dollar, which ended up having 100 trillion dollar notes. Good way to overflow your integers. With floating point, that's not a problem. What you're talking about is NOT a problem with integer money. Integers have unlimited precision, they are as large as you need them to be, there is no such thing as overflowing them. When you're using a computer to represent the integers, you just use a data type capable of representing the largest integers you could ever possibly need to use for storage or intermediate calculations, if necessary a variable size representation such as BigInt or binary-coded-decimal. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Question about floating point
That is why integer money amounts have a value/scale pair instead of mantissa/exponent. I don't expect you would be given change in the dimension of 1 "zimba" if you need 100 trillion to pay for a tank of gas. The principle behind money = v * 10 ^ -s is just the same as float = m * 2 ^e, but avoids the rounding inherent in transforming from base 2 to base 10. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Wout Mertens Gesendet: Sonntag, 16. Dezember 2018 15:55 An: SQLite mailing list Betreff: [EXTERNAL] Re: [sqlite] Question about floating point On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf wrote: > > >And yet ... here we are. The post which started this thread summed > >currency amounts and reached a total of 211496.252 . > > >Yes, you can say 'that would have been rounded before it was > >printed'. But then you're into the old questions: do you round at > >every step, or only at the end ? Do you round or truncate ? Where > >does the fraction go ? etc. etc.. > > You apply half-even rounding (not elementary school 4/5 rounding) only > for display (output) and never round intermediates. The "fraction" > does not exist ... Though if you do 4/5 rounding rather than half-even > rounding the accumulated errors will amount to quite a sum. > TIL, thanks! I'd also like to point out a problem with integer money: inflation. For USD it's been OK so far, but imagine having to handle the Zimbabwean Dollar, which ended up having 100 trillion dollar notes. Good way to overflow your integers. With floating point, that's not a problem. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
