DoublePrecision

[riccardo.farabi]

Help me!
I have this number 1726.16723958045672098127.
How Can I register in my db MySql.
Why is the number truncated?

[rproffitt]

Share the number type. Read https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html
Given this is usually a floating point number, almost every language will convert that to an internal floating point format and you'll lose digits after the decimal point. That's normal but let's hear why you need all the digits.

Found a fiddle for you to test with: http://www.sqlfiddle.com/#!9/a23a9/1

[riccardo.farabi]

In financial mathematics is important max decimal precision.
23 numbers after the decimal point is used

[rproffitt]

Finance? As in money? Then use Integer or Fixed Point types. No one in finance would select a floating point until it's about statistics.

[riccardo.farabi]

In fact my work is to create databases for financial statistics.
My problem is this calculation:
0.00000001 / (15638.3627834621516782673486583 - 15638.3627834621516782672736583)
error division by zero
15638.3627834621516782673486583 - 15638.3627834621516782672736583 is not zero but 0.000000000000000000075
then the result is 0.00000001 / 0.000000000000000000075 = 133333333333,33333333333333333333
How can I solve the problem
Thanks

[Reverend Jim]

To illustrate in Python

>>> a = 15638.3627834621516782673486583
>>> b = 15638.3627834621516782672736583
>>> a - b
0.0
>>> a == b
True
>>> a
15638.362783462151

You can see that Python does not keep all the digits.

[rproffitt]

This is the rather old issue of how numbers are represented in these standard number formats. I won't pretend to hold class about what numbers fit into each number type but when you get into this you have to use a number format that works, adjust your own scaling or move to systems that support your numbers.

It's been many years since I've use BIGNUM in C but for Python it's over at https://pypi.org/project/BigNumber/
The PHP BIGNUM library might be at https://github.com/hpez/PHPBignum

However, in statistics any number past a few decimal places is not telling us much. You didn't make it clear how many digits would be the correct value for 1/3 or even Pi. Those are never ending so how do you intend to deal with this?

[riccardo.farabi]

Yes,
I know python and problem is some.
Only in excel the result is perfect.
So is it unsolved in php?

Thanks

[JamesCherrill]

Just out of curiosity, what are you modelling that needs an accuracy like 1 part in 10^20?

[pritaeas]

https://php-decimal.io/#introduction

[jwenting]

In finance, or anywhere where precision is important, you use fixed point numbers (integer math), not floating point.
In finance moreover, typically (and this is an internationally recognised standard employed by banks) only the first 5 digits are considered significant.
Anything over that is irrelevant.

[riccardo.farabi]

I am studying a complex model of investment funds in a graph model and this model has a lot of calculations which is very difficult to explain.
In one of these calculations there is a difference between quantities that differ from the eighteenth decimal place.
Difference is the denominator of a division.
If the denominator is equal to zero I have the "error division by zero", while the denominator is not zero!
Considering the number to the eighteenth decimal, it is really different from zero.
I must have a non-zero denominator value.

[Reverend Jim]

If the numbers only differ at the 18th decimal, unless the numerator is sufficiently small, dividing a number by the difference could generate an overflow.

[rproffitt]

This IS why we must understand computer number formats and along that the limitations. As RJ and jwenting specifically pointed out that banks and such consider so many digits.

You wrote "So is it unsolved in php?" but did you:

1. Implement BIGNUM.
2. Scale your numbers.

Maybe you don't know what or how to scale your numbers.

Let's take US Currency. I can only give you a penny and nothing less in a physical transaction. So to avoid issues in Accounting. Wait, let me share I was one of many that authored accounting apps decades ago on dBase II. OK it's number formats are on the web at https://www.promotic.eu/en/pmdoc/Subsystems/Db/dBase/DataTypes.htm so that was at the time 16 digits or 1 quadrillion. So that number was large enough to notate most countries total currency to the penny without a decimal point.

Point: The entire system was set to compute down to the penny. When you would display or print you would use a function to format the output. This is just one example of scaling.

'3. For the number 0.000000000000000000075 did you try using scientific notation? (7.5e-20)

There are solutions.

[Biiim]

I have this number 1726.16723958045672098127.
How Can I register in my db MySql.
Why is the number truncated?

should be a column type decimal, DECIMAL(24,20) would store the number you just mentioned without truncating.

24 - how many digits in the number
20 - how many digits are after the decimal point

if the numbers could be even more exact then increase values.

MySQL Documentation - Decimal

[riccardo.farabi]

Hi,
the problem isn't Mysql (I defined th number in Mysql decimal(30,20)).
The problem is PHP.
The number is the result of a calculation in PHP procedure, but with PHP float number isn't possible to have 20 decimals.
When I register the number with PDO class in PHP the number is truncated by PHP before the registration in Mysql db.
For example, Excel doesn't truncate the number.
Thanks
Riccardo

[rproffitt]

It appears you are dismissing BIGNUM for PHP.

Beyond that: "Sorry, but the SQL server is an SQL server, not a calculator." This means that numbers going in and out of the database will have to be managed to fit what MySQL offers.

I consider the problems you laid out here have been addressed. But the solution is you working the code and database to get there.

[Dani]

I'm not sure, but maybe this could help you?

[Biiim]

the problem isn't Mysql (I defined th number in Mysql decimal(30,20)).
The problem is PHP.
The number is the result of a calculation in PHP procedure, but with PHP float number isn't possible to have 20 decimals.
When I register the number with PDO class in PHP the number is truncated by PHP before the registration in Mysql db.

1. Could try multiplying it by 10^x and doing the calculation then reversing it back at the end by 10^x to give the decimal again.

2. Otherwise you could use the route pritaeas mentioned php decimal

3. You could also look at just doing the calculations straight on the mysql side, it could likely be faster too.

   SELECT 0.00000001 / (15638.3627834621516782673486583 - 15638.3627834621516782672736583);

or something like

SELECT CASE WHEN (figure2 - figure3) = 0 THEN 0 ELSE figure1 / (figure2 - figure3) END my_val
FROM some_table

[riccardo.farabi]

Thanks
I had already done it as in point 3.
In financial sector PHP is very limited.
In the financial sector PHP is very limited.
Now I have problems for XIRR calculation too.
The PHP result is different from the Python and excel one
Sincerely
Riccardo

[pritaeas]

You repeatedly say that PHP is (too) limited, but then why still use it? Pick a different language!

It sounds too much like an XY problem to me. You're trying to fix this by using PHP (the Y), but PHP might be the problem. Fix X by using a better suited language.

[riccardo.farabi]

I am using PHP because I know this code very well.
Now I am rewriting to PL Mysql
I said that PHP is limited in Financial procedure (I.e. XIRR calculation and decimal fields
I didn't say PHP is limted