5

What's the decimal storage requirement in ms sql for bitcoin?

GAAP standard quotes decimal (13, 4) for money.

Is decimal (13,8) preferred for bitcoin or something else?

Improve this question
1
  • 1
    It's interesting that GAAP would say DECIMAL(13,4) as the largest amount representable is $999,999,999.9999. Every company in the Fortune 1000 has revenues higher than that.
    – Nate Eldredge
    Oct 14, 2014 at 14:34

3 Answers 3

Reset to default
3

Since Bitcoin's precision is 8 decimal numbers, decimal (13, 4) won't cut it. So you better go with (13, 8).

Improve this answer
3
  • excellent thanks, currently future proofing a web site and I've managed to sneak this in. they'll thank me later :D
    – Choco Smith
    Oct 14, 2014 at 12:47
  • 1
    The first number is the total number of decimal digits available, and the second number is the number to the right of the decimal point. So DECIMAL(13,8) will fail should you ever encounter an amount of BTC 100000.00000000 or larger. As the other answer says, DECIMAL(16,8) would be safe until/unless the protocol changes to further subdivide the bitcoin or expand the money supply.
    – Nate Eldredge
    Oct 14, 2014 at 14:24
  • Just as a side note, if we're already going that direction, even DECIMAL(16,8) might not be enough, theoretically speaking. For example, you may want to express a debt of 25M bitcoins, regardless of the total amount of bitcoins out there. Just like you may do so with any other currency. So, a better metric would probably be the maximum number you think you may want to use in your application.
    – knaperek
    Oct 14, 2014 at 15:50
4

You need at least DECIMAL(16,8) to store any valid Bitcoin amount (i.e. up to just over 20 Million Bitcoin, requiring 8 digits to the left of the decimal point) precisely (i.e. down to a single Satoshi, requiring 8 digits to the right of the decimal point).

Depening on your application and how you (or your collaborators) think of Bitcoin amounts, it may be preferable to process them in Satoshi, enabling you to treat them as integers (BIGINT would then be the SQL datatype). Of course, then you have traded the dangers around non-integer handling for those associated with large integer handling.

Improve this answer
1
  • thanks, I ended up going 16,8 route, we needed the 9 points of precision in the leading numbers. We avoided the bigint simply cause the site deals with more than just bitcoins and to keep easy backwards compatibility.
    – Choco Smith
    Oct 20, 2014 at 14:12
1

I would take a different approach and simply use an 8-byte integer value. This is because all bitcoin transaction values are stored as integers and since float decimal math can vary from architecture to architecture, I'd recommend using integer values whenever possible.

Bitcoin has a fixed decimal place of 8-digits. If you want to display this information to an end-user just format the integer value to represent the appropriate number of decimals, when the value needs to be displayed.

Improve this answer
5
  • While using an integer variable representing satoshis is a perfectly viable option, please note that the discussion here was considering Decimals, not floats. Unlike float/double types that are commonly used for scientific calculations, Decimal types have a perfectly clear and deterministic precision, intended to be used in financial applications where fixed decimal precision is desirable. Floats should never be used for this purpose. So, it is OK to use Decimals when we're in fact dealing with them :-)
    – knaperek
    Oct 14, 2014 at 17:18
  • Regardless, your type is still converted from a base-2 number to a numeric type that is a data structure containing a mantissa, an exponent and a value. Since bitcoin values are stored in ints, you will have to convert to decimal and then back to int if you intend to use these values within the client. I still recommend using an int type in sql and only format the value when it needs to be displayed to the end-user. Surely any accountant within any computational background would recommend the same approach. Especially if they've worked int he field of transactional processing. ;)
    – RLH
    Oct 15, 2014 at 12:04
  • Actually, you're wrong here. Decimal types do not use base-2 representations with mantissa and exponent, but rather encodings like BCD. They're specifically designed for accounting purposes, and thus should be preferred over other types. For one, there's no hidden logic behind them - what you see is what they mean (this is a good thing in sw engineering). Also, they're far more flexible; say you want to increase the precision in your application - you can't do this without rewriting all the old records if using integers.
    – knaperek
    Oct 15, 2014 at 13:00
  • Slip-of-the-finger-- yes I know decimals base-10, not 2. My point is simple. If the bitcoin "eco-system" operates in a integer world, why would you diverge from that? The only exceptions that I see are two context-- you need to store values at the sub-satoshi level, or you are calculating LARGE values like market caps which can potentially be larger than the total number of BTC in existence. Within finance, numbers can get very large and very small. However, within BTC, we have a fixed range of values if you are dealing with transactional numbers that are part of the blockchain.
    – RLH
    Oct 15, 2014 at 13:15
  • I wouldn't go as far as saying that the Bitcoin ecosystem operates in an integer world. Yes, it was the implementation choice for the original client written in C, which makes sense for that particular language. From a higher level point of view though, I would incline to natural types. Another argument pro can be the formatting - if it's a Decimal, than the right decimal separator can be automatically used everywhere in your frontend, according to your user location/language settings. The same goes with default rounding (if needed) and so on... But I agree it might be a matter of preference.
    – knaperek
    Oct 15, 2014 at 13:38

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.