[sqlite] [SQLite] Support for 23,10 Precision number format

[Dinesh Navsupe]

Hi,

Does any of SQLite data Type support 23,10 precision format for Number?

If yes, could you pleas help with right data type or approach to achieve
this.

If No, then is there something that can be added to SQLite and how quickly?

Thanks,
Dinesh Navsupe

[Simon Slavin]

Post by Dinesh Navsupe
Does any of SQLite data Type support 23,10 precision format for Number?
If yes, could you pleas help with right data type or approach to achieve
this.

SQL stores REAL numbers in a REAL field which conforms to 64-bit IEEE 754 (as much as SQL permits). This allows 16 decimal digits of precision.

You can store numbers of greater precision than that -- by storing them as strings or BLOBs. You just can't have SQLite do its own maths on them.

Post by Dinesh Navsupe
If No, then is there something that can be added to SQLite and how quickly?

It is unlikely that the developer team would be interested in doing this. Given that the source code for SQLite is open, you might want to implement them yourself. However, the test library for having the developer team do this this would probably have to be huge.

SQLite4, which is not released yet, uses an 18-digit decimal number with a 3-digit base-10 exponent. It is possible that this might change if you are able to produce a good argument for doing so

Simon.

[Dinesh Navsupe]

Hi,

My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.

We want to use SQLite for local disk data store and calculations.

Thanks,
Dinesh Navsupe

Post by Simon Slavin

Post by Dinesh Navsupe
Does any of SQLite data Type support 23,10 precision format for Number?
If yes, could you pleas help with right data type or approach to achieve
this.

SQL stores REAL numbers in a REAL field which conforms to 64-bit IEEE 754
(as much as SQL permits). This allows 16 decimal digits of precision.
You can store numbers of greater precision than that -- by storing them as
strings or BLOBs. You just can't have SQLite do its own maths on them.

Post by Dinesh Navsupe
If No, then is there something that can be added to SQLite and how

quickly?
It is unlikely that the developer team would be interested in doing this.
Given that the source code for SQLite is open, you might want to implement
them yourself. However, the test library for having the developer team do
this this would probably have to be huge.
SQLite4, which is not released yet, uses an 18-digit decimal number with a
3-digit base-10 exponent. It is possible that this might change if you are
able to produce a good argument for doing so
Simon.
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[RSmith]

Post by Dinesh Navsupe
Hi,
My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.

I do not think that re-stating your need suffices as a good enough argument. We heard you the first time and understand the need and
understand exactly that you are working with numbers in that precision so you would like to be able to store them efficiently.
SQLite does not do this in binary terms (it can produce 64 bit Integers/Reals at best in accordance with IEEE as some others already
explained). No other Engine does this natively either (meaning that in your code there is no way to exchange that size number with
the prepared statement/api/object within a standard C data type other than a string or blob). Using strings is preferable because
they will be human readable in DB dumps, but you will need to use a BigInt type library or create your own conversion routines to
actually translate those numbers to and from strings for the purposes of communicating it to/from any DB engine, SQLite included.

Post by Dinesh Navsupe
We want to use SQLite for local disk data store and calculations.

That's a good decision, but it comes with a bit of work, same as any other chosen RDBMS.

As an aside, do you really need that precision? You can accurately state the American National Debt in dollars and cents within 16
digits of precision (That's 18 trillion dollars btw, or 18^12). I can't imagine a payout ever needing to be much higher than that,
or by another 8 or so digits more precise? (well, that would allow you to express the entire World's collective national debts in
Zimbabwe dollars). 16 digits can very well be represented by a Float in SQLite and transferred to your program in standard 64-bit
floats via the API. Above that you will need to make the routines.

[Dominique Devienne]

Post by Dinesh Navsupe
My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.

If IEEE double is not good enough, you can use already suggested string or
blob based representation, or store the integral part and decimal part in
separate integer-typed columns, which have also the advantage to be smaller
for small values than larger one (varint format [1]). Or if your exact
arithmetic package is based on rational numbers, store
numerator/denominator in separate columns similarly. Both remaining
human-readable like string, and it might be faster to re-instantiate your
custom integer type than parsing a string. FWIW. --DD

[1] https://www.sqlite.org/fileformat2.html#varint

[Simon Slavin]

Post by Dinesh Navsupe
My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.

If you're working with floating-point numbers, you will get roundoff [1]. Integer arithmetic really is how big banks work. That way you don't have to test for rounding problems and never get accused of Salami Slicing. If you really need money precision you'll be working in integers representing paise, pennies, halalas, whatever. You'd have asked for 23 digit integers, not 23,10.

Since the abandonment of the Lira, no decimal country currency has needed anything more than three places of decimals for manipulation. So for 23 digits of accuracy you seem to have a requirement to manipulate

100,000,000,000,000,000,000

units of currency with perfect accuracy. Even the World Bank Group doesn't need that. And I don't think the total wealth of any country in its own currency requires that many digits.

I've worked with international financial organisations and we never did anything that needed 23 digits of precision, and that includes complicated cumulative interest calculations and those unbelievable asset value predictions that require integration and antilogs.

In summary, if you need ultimate precision, use integers. If not, use 64-bit IEEE-571 like everyone else does without being sued. If you somehow really need 23,10 maths, then you're going to have to write your own mathematical library anyway, because I'm not aware of any usable libraries which actually support 23,10 outside the world of physics.

Simon.

[1] This is a little hand-waving but only a little. I'm only mentioning that because I don't want someone in the industry to dig this up and use it against me.

[Dominique Devienne]

Post by Dinesh Navsupe
My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.

[...]. So for 23 digits of accuracy you seem to have a requirement to
manipulate
100,000,000,000,000,000,000

Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more

9,999,999,999,999.9999999999

i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
100 million trillion.

FWIW. --DD

[1] http://www.orafaq.com/wiki/Number

[Dinesh Navsupe]

You are right Dominique.

I mean Oracle's NUMBER(23, 10), and given [1], that's more

9,999,999,999,999.9999999999
Thanks.

Post by Dominique Devienne

Post by Dinesh Navsupe
My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers and
clients do not want to round off.

[...]. So for 23 digits of accuracy you seem to have a requirement to
manipulate
100,000,000,000,000,000,000

Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more
9,999,999,999,999.9999999999
i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
100 million trillion.
FWIW. --DD
[1] http://www.orafaq.com/wiki/Number
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Stephan Beal]

Post by Dinesh Navsupe
I mean Oracle's NUMBER(23, 10), and given [1], that's more

Post by Dinesh Navsupe
My need is 23 decimal digits of precision. We work on complex payout
calculation engine where in formula outputs are quite large numbers

and

Post by Dinesh Navsupe
clients do not want to round off.

The first answer to this thread might be helpful (but also probably not
what you want to hear):

http://sqlite.1065341.n5.nabble.com/How-point-numbers-are-they-stored-in-sqlite-td35739.html

[Simon Slavin]

Post by Dominique Devienne

Post by Simon Slavin
100,000,000,000,000,000,000

Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more
9,999,999,999,999.9999999999
i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
100 million trillion.

But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency requires more than three places.

Simon.

[RSmith]

Post by Simon Slavin

Post by Simon Slavin
100,000,000,000,000,000,000

But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency requires more than three places.

He mightn't be storing the actual money, but the working factors and figures used in the formulas seeding the money calculations,
conversions, etc. which may have a bit higher accuracy required. Though if this is the case, I would suggest getting back to your
original suggestion of saving the money as integer cents (or milli-dollars if you like) and storing the factors and figures as good
old 64 bit IEEE floats which will give you up to 15 digits after the decimal point accurately, twice the required accuracy - not to
mention the luxury of being able to access both types natively in the api /and/ represent it easily in human-readable format in data
dumps.

By the way, my Oracle friends should intersect here if need be, but I believe the oracle method of /decimal(n,m)/ is simply a
representation directive and constraint, there is no native datatype that actually stores or communicates such a value. Oracle
stores it internally in a very specific arrangement of bytes and you need to still interpret it in your software.

Nor do I think the Oracle SQL engine would be able to do (without any add-on modules):
SELECT (B.Money*B.Factor) FROM BigMoney B;
from said table with Money and Factor both as decimal(38,6) each containing >30 decimals - or would it?

[Dominique Devienne]

Post by RSmith
By the way, my Oracle friends should intersect here if need be, but I
believe the oracle method of /decimal(n,m)/ is simply a representation
directive and constraint, there is no native datatype that actually stores
or communicates such a value. Oracle stores it internally in a very
specific arrangement of bytes and you need to still interpret it in your
software.

Not so. OCI has native support for Number client side via OCINumber and
associated functions to convert to native C types, and can do 128-bit
integer arithmetic for example even when the C/C++ native types cannot. And
it's a value type, not an opaque type, so you can decode the internal
well-known byte rep if you want to even. --DD

[Hick Gunter]

IIRC there was a programmer working for a bank that managed to siphon off the sub-unit fractions that the interest calculating software generated (how much interest is owed for $10000 at 0,25% p.a. for 2 days*) onto his own account and temporarily got rich quick.

$10000 * 0,25% = $25 (interest for 1 year)
$25 * 2 / 360 = $0,13888889 (interest for 2 days)

This is split into 13 cents for the client and nearly 0,9 cents that the bank keeps

-----Ursprüngliche Nachricht-----
Von: Simon Slavin [mailto:***@bigfraud.org]
Gesendet: Donnerstag, 13. November 2014 18:07
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] [SQLite] Support for 23,10 Precision number format

Post by Dominique Devienne

Post by Simon Slavin
100,000,000,000,000,000,000

Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more
9,999,999,999,999.9999999999
i.e. "just" under 10 trillion max, with 10 decimal digits accuracy, and not
100 million trillion.

But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency requires more than three places.

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-***@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: [phone]
E-Mail: ***@scigames.at

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.

[James K. Lowden]

On Thu, 13 Nov 2014 14:38:10 +0000

Post by Simon Slavin
In summary, if you need ultimate precision, use integers. If not,
use 64-bit IEEE-571 like everyone else does without being sued. If
you somehow really need 23,10 maths, then you're going to have to
write your own mathematical library anyway, because I'm not aware of
any usable libraries which actually support 23,10 outside the world
of physics.

http://www.mpfr.org/#free-sw

I'm sure you're aware of such things. I don't believe the OP's problem
statement, but if he's bound and determined to go that route, I don't
see how he's obliged to write the math functions. Why are none of
these suitable?

--jkl

[Simon Slavin]

Post by James K. Lowden

Post by Simon Slavin
I'm not aware of
any usable libraries which actually support 23,10 outside the world
of physics.

http://www.mpfr.org/#free-sw
I'm sure you're aware of such things.

Hey, you're right. I was thinking about 128-bit stuff. I forgot about arbitrary-precision libraries. Thanks for the correction.

Simon.

[Hick Gunter]

Data types are 64bit integer (~18 decimal digits) and 64 Bit IEEE Float(11 bit exponent, 52 bit fraction), so no.

Store the numbers as TEXT (human readable) or BLOB (e.g. 128Bit binary) and write user-defined functions to manipulate them.

-----Ursprüngliche Nachricht-----
Von: Dinesh Navsupe [mailto:***@gmail.com]
Gesendet: Donnerstag, 13. November 2014 13:23
An: sqlite-***@sqlite.org
Betreff: [sqlite] [SQLite] Support for 23,10 Precision number format

Hi,

Does any of SQLite data Type support 23,10 precision format for Number?

If yes, could you pleas help with right data type or approach to achieve this.

If No, then is there something that can be added to SQLite and how quickly?

Thanks,
Dinesh Navsupe
_______________________________________________
sqlite-users mailing list
sqlite-***@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: [phone]
E-Mail: ***@scigames.at

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.