Search code examples
javasqlitecurrencycryptocurrency

math operations on currencies (crypto) stored in sqlite as bigint

I'm trying to store cryptocurrencies values inside a sqlite database. I read that is not correct to store those values as float nor double because the loss of precision caused by the IEEE754. For this reason I saved this values as biginteger in my database. (And I multiply or divide by 10^8 or 10^(-8) in my app before reading or storing the values).

create table Transactions
(
    id                INTEGER not null  primary key autoincrement unique,
    crypto_name         TEXT    not null,
    total_fiat_amount BIGINT default 0 not null,
    crypto_fiat_price   BIGINT default 0 not null,
    commission        BIGINT default 0 not null,
    date              BIGINT not null
);

But now I have a problem: Suppose I want to calculate the amount of coin using this formula:

SELECT "coin_name", ("total_fiat_amount"-"commission")/("crypto_fiat_price")) AS crypto_amount FROM Transactions GROUP BY "coin_name";

But the result is always wrong because is always in integer (the decimal part is always truncated and omitted). I need a real number, but without the loss of precision, because I need affordable results.

For a workaround I could cast to double or multiply for 1.0, but that's not what I want because doing so I think I will have wrong results in double! (and so I could have used double from the beginning).

Are there any viable solutions? (Except avoid sql for this kind of calculations and doing the math in the app, because it's not a valid solution)

Solution

  • The key is to just multiply the divident instead of multiplying the result.

    If both total_fiat_amount-commission and crypto_fiat_price are mononitery values with a maximum of two digits after the comma, you don't need to multiply both with 10^8 but only with 10^2.

    In that case, the result would be accurate to 0 decimal points of precision after the comma.

    If you want to have 8 decimal pieces of precision after the comma, you can multiply the divident with 10^8 before running the division.

    If you store total_fiat_amount, commission and crypto_fiat_price in cents, you could use this:

    SELECT "coin_name", (("total_fiat_amount"-"commission")*100000000)/("crypto_fiat_price")) AS crypto_amount FROM Transactions GROUP BY "coin_name";

    This limits total_fiat_amount-commission to the 64bit integer limit divided by 10^8 so you would get a maximum limit of 92233720368 (cents) for total_fiat_amount-commission.