I need to store bitcoin value and other currencies values together , I have a decimal column (16,8) for amounts. It behaves normally for bitcoin values , but when I want to store other currencies like Usd or Eur It becomes like this eg. 100 Usd becomes 100.00000000 . 0.50 Usd becomes 0.50000000

My question is should I store bitcoin values and other curriencies values in same column? I will have millions of rows, is it bad for performance (read , write , count ) or this is just cosmetic issue?

  • There is also a "correctness" question. But without knowing how you plan to use the data, we can address any of the questions. – Rick James Dec 24 '17 at 22:17

As far as you use a DECIMAL(16, 8) field, values are stored with 6 decimal digits. It doesn't matter if they are bitcoins, euros, dollars, ...

If you want to use a monetary type, then you should take care and properly configure the server just to hold all values.

8.2. Monetary Types

The money type stores a currency amount with a fixed fractional precision; see Table 8.3. The fractional precision is determined by the database's lc_monetary setting. The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as '$1,000.00'. Output is generally in the latter form but depends on the locale.

CREATE TABLE Amounts(ID int, Value decimal(16,8), Value2 money);

(1, 100, 100), (2, 12.22636, 12.22636), (3, 0.1, 0.1);

SELECT * FROM Amounts;

3 rows affected

id |        value |   value2
-: | -----------: | -------:
 1 | 100.00000000 | £100.00
 2 |  12.22636000 |  £12.23
 3 |   0.10000000 |   £0.10

dbfiddle here

| improve this answer | |

No good answer...

DECIMAL(16,8) takes 8 bytes, as do DOUBLE and BIGINT.

8 integer digits (in decimal(16,8)) is sufficient for most monetary transactions, but not sufficient to talk about national GDP when expressed in USD, etc. If that is needed, raise the 16 to make more room, at the expense of more bytes (approx 1 byte per 2 digits).

DOUBLE (no (m,n) please), can handle very large values, but suffers some roundoff issues.

BIGINT needs to be divided by the sub-units (10**8 for Bitcoin, 100 for USD/Euro, 10000 for a few other currencies.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.