Question about floating point

classic Classic list List threaded Threaded
58 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Question about floating point

Frank Millman
Hi all

I know that floating point is not precise and not suitable for financial uses. Even so, I am curious about the following -

SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open /sqlite_db/ccc
sqlite> select sum(amount_cust) from ar_trans where cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31';
211496.26

Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.26.0'
>>> conn = sqlite3.connect('/sqlite_db/ccc')
>>> cur = conn.cursor()
>>> cur.execute("select sum(amount_cust) from ar_trans where cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31'")
<sqlite3.Cursor object at 0x000002C1D6BBCF80>
>>> cur.fetchone()
(211496.25999999992,)

With the same version of sqlite3 and the same select statement, why does python return a different result from sqlite3.exe?

Thanks

Frank Millman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Jay Kreibich

> On Dec 15, 2018, at 12:49 AM, Frank Millman <[hidden email]> wrote:
>
> Hi all
>
> I know that floating point is not precise and not suitable for financial uses. Even so, I am curious about the following -
>
> SQLite version 3.26.0 2018-12-01 12:34:55
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open /sqlite_db/ccc
> sqlite> select sum(amount_cust) from ar_trans where cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31';
> 211496.26
>
> Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import sqlite3
>>>> sqlite3.sqlite_version
> '3.26.0'
>>>> conn = sqlite3.connect('/sqlite_db/ccc')
>>>> cur = conn.cursor()
>>>> cur.execute("select sum(amount_cust) from ar_trans where cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31'")
> <sqlite3.Cursor object at 0x000002C1D6BBCF80>
>>>> cur.fetchone()
> (211496.25999999992,)
>
> With the same version of sqlite3 and the same select statement, why does python return a different result from sqlite3.exe?


Because the shell is altering the output to make it easier to read.  Consider:

$ sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 211496.25999999992;
211496.26


-j



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Frank Millman
On Dec 15, 2018, at 08.58, Jay Kreibich wrote:

>
> > On Dec 15, 2018, at 12:49 AM, Frank Millman <[hidden email]> wrote:
> >
> > I know that floating point is not precise and not suitable for financial uses. Even so, I am curious about the following -
> >
[...]

> >
> > With the same version of sqlite3 and the same select statement, why does python return a different result from sqlite3.exe?
>
> Because the shell is altering the output to make it easier to read.  Consider:
>
> $ sqlite3
>
> SQLite version 3.16.0 2016-11-04 19:09:39
>
> Enter ".help" for usage hints.
>
> Connected to a transient in-memory database.
>
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select 211496.25999999992;
> 211496.26
>

 That makes sense.

Thanks, Jay
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Darren Duncan
In reply to this post by Frank Millman
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. -- Darren Duncan

On 2018-12-14 10:49 PM, Frank Millman wrote:

> Hi all
>
> I know that floating point is not precise and not suitable for financial uses. Even so, I am curious about the following -
>
> SQLite version 3.26.0 2018-12-01 12:34:55
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open /sqlite_db/ccc
> sqlite> select sum(amount_cust) from ar_trans where cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31';
> 211496.26
>
> Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import sqlite3
>>>> sqlite3.sqlite_version
> '3.26.0'
>>>> conn = sqlite3.connect('/sqlite_db/ccc')
>>>> cur = conn.cursor()
>>>> cur.execute("select sum(amount_cust) from ar_trans where cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31'")
> <sqlite3.Cursor object at 0x000002C1D6BBCF80>
>>>> cur.fetchone()
> (211496.25999999992,)
>
> With the same version of sqlite3 and the same select statement, why does python return a different result from sqlite3.exe?
>
> Thanks
>
> Frank Millman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Simon Slavin-3
On 15 Dec 2018, at 9:24am, Darren Duncan <[hidden email]> 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.

This.  Currency amounts should be stored as integers.  The problem you spotted is just one of many bad consequences of using floating arithmetic on currencies.  Please please consider changing the way your database works.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Frank Millman
In reply to this post by Darren Duncan
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?

Thanks

Frank
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Niall O'Reilly
On 15 Dec 2018, at 10:15, Frank Millman wrote:

> Simon Slavin says ‘Currency amounts should be stored as integers’.
> Does this apply to sqlite3 specifically, or is that your
> recommendation for all databases?

It doesn't matter whether a database is involved.

Using integers for currency amounts is long established as
the only way to do the arithmetic needed for accounting.
I'm not sure when I first learned this; it was surely more
than 40 years ago. I wish I could cite a reference.

You need to count farthings, pennies, centimes, millièmes,
or whatever the smallest denomination of the currency is,
and arrange appropriate display filtering.

In 1972 or so, I learned PL/I, which then had a "STERLING"
data type for representing sums as pounds, shillings, and
pence. I gather from what a quick web search shows of
current IBM documentation that this data type is no longer
(advertised as) supported.

Best regards,

Niall O'Reilly
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Simon Slavin-3
In reply to this post by Frank Millman
On 15 Dec 2018, at 10:15am, Frank Millman <[hidden email]> wrote:

> Simon Slavin says ‘Currency amounts should be stored as integers’. Does this apply to sqlite3 specifically, or is that your recommendation for all databases?

For anything that goes near a computer.  I used to work with international banking software.  Any time we put out a tender for an interface and the bidder treated currencies as REAL we knew we'd be able to find a calculation which invisibly lost or gained a fraction. Even with division we could trip up their software.  It's an inherent problem with using binary operations on a base-10 fraction.

The only time you treat currency units as real is when you're doing calculations which are inherently real.  As when you're given a yearly interest rate of 4% and want to know how much interest you'd get for 820 days.  And even with that you do the calculation and convert the answer to an integer, using whatever rounding/truncating algorithm your banking authority has approved.

It doesn't involve much less work once you've decided to do it.  The big problem occurs only when taking a system which was designed (possibly by someone else years ago) for fractions and converting all the data.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Keith Medcalf
In reply to this post by Frank Millman

On Friday, 14 December, 2018 23:49, Frank Millman <[hidden email]> wrote:

> I know that floating point is not precise and not suitable for
> financial uses.

This is debatable.  In the "old days" of what was called "fast floating point" it was certainly true since the epsilon of a "fast floating point" number is about 2.2e-08 which meant that representation errors became significant at about $1000.0000 assuming that the floating point operations were carried out to the maximum epsilon of the representation and with a maximum error of a single ULP (and the specifications did not require 1 ULP accuracy, the accuracy of the floating point calculations often being up to about 20 to 200 ULP even for simple arithmetic).  Add in the propensity for the proletariat to do questionable "rounding" of intermediates, and you quite quickly end up with huge errors.

Those of us who "cared" about accuracy bought computers with "math co-processors" that could perform proper floating point arithmetic in accordance with the IEEE-754 standard using "double precision" floating point numbers which had an epsilon of 2.22044604925031e-16 and guaranteed the accuracy of all arithmetic operations within 1 ULP (or even longer precision with the same 1 ULP guarantee).  These little devices would minimally DOUBLE the cost of the computer and cut its performance merely in half.

Outside of the scientific and engineering worlds this was little known and the MBA types would use inaccurate "fast floating point" and "intermediate rounding" to do monetary calculations with the expected result:  the answer was not that which what would be obtained via paper and pencil methods.  Due to the difficulties inherent in explaining the why and wherefor of this, the general proletariat simply summarized as "don't use floating point for money instead use paper and pencil methods such as scaled integer (fixed point) or packed BCD arithmetic" because it was much easier to remember than the actual reason for the difficulty, and generally cheaper (cost wise and compute time wise) than using "proper" floating point properly (plus the fact that a degree in mathematics and an understanding of how computers worked was not required, significantly reducing the cost of implementation).

Fast forward 25 years and you would these days be hard pressed to find a computer that DOES NOT use proper IEEE-754 floating point and that DOES NOT default to a minimum of double precision representation and that DOES NOT use extended precision internally to ensure that the 1 ULP guarantee is ensured.  However, the memory of the proletariat is long:  Even though the original issue leading to the quaint "do not use floating point for money" aphorism no longer exists the problem of knowing "why this was so" still exists.  And the propensity to apply intermediate rounding still exists.

> Even so, I am curious about the following -

>SQLite version 3.26.0 2018-12-01 12:34:55
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .open /sqlite_db/ccc
>sqlite> select sum(amount_cust) from ar_trans where cust_row_id = 4
>and tran_date between '2015-05-01' and '2015-05-31';
>211496.26

>Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914
>64 bit (AMD64)] on win32
>Type "help", "copyright", "credits" or "license" for more
>information.
>>>> import sqlite3
>>>> sqlite3.sqlite_version
>'3.26.0'
>>>> conn = sqlite3.connect('/sqlite_db/ccc')
>>>> cur = conn.cursor()
>>>> cur.execute("select sum(amount_cust) from ar_trans where
>cust_row_id = 4 and tran_date between '2015-05-01' and '2015-05-31'")
><sqlite3.Cursor object at 0x000002C1D6BBCF80>
>>>> cur.fetchone()
>(211496.25999999992,)

>With the same version of sqlite3 and the same select statement, why
>does python return a different result from sqlite3.exe?

They do not.  The value is the same.  The SQLite3 shell is simply applying some type of "rounding for display" which is using different rules than the "rounding for display" that is being used by the Python interpreter.  

Neither is the "actual value" but is a base-10 aproximate representation of the base-2 number.

Given that the number is an IEEE-754 double precision floating point number with a 53 bit mantissa, Python is displaying the number rounded to 17 digits of base-10 decimal precision, and the SQLite3 interpreter is "rounding out" the result to 8 digits of base-10 decimal precision.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Simon Slavin-3
On 15 Dec 2018, at 5:35pm, Keith Medcalf <[hidden email]> wrote:

> Fast forward 25 years and you would these days be hard pressed to find a computer that DOES NOT use proper IEEE-754 floating point and that DOES NOT default to a minimum of double precision representation and that DOES NOT use extended precision internally to ensure that the 1 ULP guarantee is ensured. However, the memory of the proletariat is long:  Even though the original issue leading to the quaint "do not use floating point for money" aphorism no longer exists the problem of knowing "why this was so" still exists.  And the propensity to apply intermediate rounding still exists.

And yet ... here we are.  The post which started this thread summed currency amounts and reached a total of 211496.25999999992 .

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..

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Keith Medcalf

On Saturday, 15 December, 2018 10:54, Simon Slavin <[hidden email]> wrote:
>On 15 Dec 2018, at 5:35pm, Keith Medcalf <[hidden email]> wrote:

>> Fast forward 25 years and you would these days be hard pressed to
>find a computer that DOES NOT use proper IEEE-754 floating point and
>that DOES NOT default to a minimum of double precision representation
>and that DOES NOT use extended precision internally to ensure that
>the 1 ULP guarantee is ensured. However, the memory of the
>proletariat is long:  Even though the original issue leading to the
>quaint "do not use floating point for money" aphorism no longer
>exists the problem of knowing "why this was so" still exists.  And
>the propensity to apply intermediate rounding still exists.

>And yet ... here we are.  The post which started this thread summed
>currency amounts and reached a total of 211496.25999999992 .

>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.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

wmertens
On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf <[hidden email]> wrote:

>
> >And yet ... here we are.  The post which started this thread summed
> >currency amounts and reached a total of 211496.25999999992 .
>
> >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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Simon Slavin-3
On 16 Dec 2018, at 2:54pm, Wout Mertens <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Keith Medcalf
In reply to this post by wmertens

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.00000000000000001e16 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-
>[hidden email]] 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 <[hidden email]>
>wrote:
>
>>
>> >And yet ... here we are.  The post which started this thread
>summed
>> >currency amounts and reached a total of 211496.25999999992 .
>>
>> >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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Thomas Kurz
In reply to this post by wmertens
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

wmertens
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 <[hidden email] wrote:

> > 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Gary R. Schmidt
In reply to this post by Simon Slavin-3
On 2018-12-17 02:41, Simon Slavin wrote:

> On 16 Dec 2018, at 2:54pm, Wout Mertens <[hidden email]> 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,
                 Gary    B-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Darren Duncan
In reply to this post by Frank Millman
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

D Burgess
In reply to this post by Gary R. Schmidt
> 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,
>                  Gary    B-)
>
As do insurance companies and many in the manufacturing world (inventory).
There is a lot to like about BCD.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about floating point

Darren Duncan
In reply to this post by wmertens
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123