4 ms·

Why wouldn't you just store the value in cents? And divide by 100 to display it.

fauigerzigerk 9y ago
As I said in my reply to asaph, it doesn't work well with interest rates and exchange rates. Also, it simply adds to the mental load in many places (e.g ad hoc queries)
- TeMPOraL 9y ago
  And floats do? With all the (implicit) rounding and precision issues, and some base-10 numbers not being representable in an exact form? Serious question, what's the proper way to handle this case? I'm having hard time to believing it's IEEE-754 floats.
  - fauigerzigerk 9y ago
    No, binary floating point doesn't work at all. Decimal floating point can work if the representation is large enough.
  - icebraining 9y ago
    Arbitrary-precision numbers. DBs like Postgres support them, as do most languages (e.g. Ruby has BigDecimal, Python has Decimal).
    - aeorgnoieang 9y ago
      Neither `BigDecimal` in Ruby or `Decimal` in Python are quite what I'd expect for "arbitrary-precision". From the docs for [`BigDecimal`](http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigDecimal.html http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigD...): > Ruby provides built-in support for arbitrary precision integer arithmetic. For example: > > 4213 -> 1265437718438866624512 > > BigDecimal provides similar support for very large or very accurate floating point numbers. My takeaway from the above is that, if arbitrary precision is needed, you should still just use integers. Tho, in practice, it may not be significant.
      - fauigerzigerk 9y ago
        Integers are not magic. If you want arbitrary precision on top of integers you're going to need arbitrarily long integers as well.
        aeorgnoieang 9y ago
        I was referring to Ruby and based on the first sentence I quoted: > Ruby provides built-in support for arbitrary precision integer arithmetic. [I'm also assuming that it's really arbitrary precision and not just 'really big but limited' precision.]
  - elteto 9y ago
    OP mentioned a decimal data type, which in postgres can have a very large precision: https://www.postgresql.org/docs/10/static/datatype-numeric.html https://www.postgresql.org/docs/10/static/datatype-numeric.h.... Not the same as a floating point.
    - anarazel 9y ago
      16383 digits ought to be enough for everyone: "up to 131072 digits before the decimal point; up to 16383 digits after the decimal point" Non jokingly: I think precision wise that's really plenty enough for pretty much any halfway realistic usecase (no, don't do public key crypto using SQL datatypes). There's some issues with the numeric / decimal type, but lack of potential precision isn't one of them.
  - aldanor 9y ago
    The proper way to handle this is fixed-point representation (whether the DB natively supports this or not). E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges. A typical scaling factor is 10^8, so if the exchange wants to tell you that the price is now 101.27, they send you an int64 value of 10127000000. You may then choose to take it further and store e.g. in 128-bit decimal. Of course, there's an issue of how to handle mul/div (i.e., rounding/truncation), but that's a separate question -- at least you'd be making decisions yourself rather than relying on how IEEE-754 floats work. Some DBs like PG, do, as a matter of fact, support decimals natively which makes things quite a bit simpler.
    - KMag 9y ago
      Certainly for decimalized exchanges, binary floating point is the wrong choice. (On a side note, it's also the wrong choice for spread sheets, at least as the default. The average freshly graduated accountant can have a reasonable grasp on decimal floating point rounding after a 5 minute explanation, whereas most professional software engineers with a decade of experience have a very weak understanding of IEEE 754 binary floating point.) > E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges. That's true of NASDAQ's OUCH, but some (many?) other protocols use decimal floating point. NYSE's UTP Direct[0] (See section 4.15) Is not fixed point, but actually their own decimal floating point system with a 32-bit significand and single ASCII character exponent ("0" to "6", indicating negated base 10 exponent, not allowing "5"). FIX SBE uses an 8 byte significand and a 1 byte (power-of-10) exponent, if I remember correctly. It's a bit unfortunate that (as far as I know) there aren't any major messaging formats supporting IEEE 754-2008 decimal64 values. [0]https://www.nyse.com/publicdocs/nyse/markets/nyse/NYSEUTPDirect_Specification.pdf https://www.nyse.com/publicdocs/nyse/markets/nyse/NYSEUTPDir...
dragonwriter 9y ago
Serious monetary work often needs values smaller than cents. Exact decimals or, better, exact fractions are often what you want.
- lsaferite 9y ago
  What is the typical use-case of a unit smaller than the smallest tradable currency unit? Mind you, I understand there are smaller units than the circulated currency (For the USD you have a mill which is 1/10 of a cent).
  - tracker1 9y ago
    Bitcoin currency trades? Large fractions. Convert to/from yuan/yen, large whole numbers, back to US, somewhere in the middle. Tracking transactional values for large stock and currency trades would be an example. That said, could use integers to represent arbitrary precision.

As I said in my reply to asaph, it doesn't work well with interest rates and exchange rates. Also, it simply adds to the mental load in many places (e.g ad hoc queries)

TeMPOraL 9y ago
And floats do? With all the (implicit) rounding and precision issues, and some base-10 numbers not being representable in an exact form? Serious question, what's the proper way to handle this case? I'm having hard time to believing it's IEEE-754 floats.
- fauigerzigerk 9y ago
  No, binary floating point doesn't work at all. Decimal floating point can work if the representation is large enough.
- icebraining 9y ago
  Arbitrary-precision numbers. DBs like Postgres support them, as do most languages (e.g. Ruby has BigDecimal, Python has Decimal).
  - aeorgnoieang 9y ago
    Neither `BigDecimal` in Ruby or `Decimal` in Python are quite what I'd expect for "arbitrary-precision". From the docs for [`BigDecimal`](http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigDecimal.html http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigD...): > Ruby provides built-in support for arbitrary precision integer arithmetic. For example: > > 4213 -> 1265437718438866624512 > > BigDecimal provides similar support for very large or very accurate floating point numbers. My takeaway from the above is that, if arbitrary precision is needed, you should still just use integers. Tho, in practice, it may not be significant.
    - fauigerzigerk 9y ago
      Integers are not magic. If you want arbitrary precision on top of integers you're going to need arbitrarily long integers as well.
      - aeorgnoieang 9y ago
        I was referring to Ruby and based on the first sentence I quoted: > Ruby provides built-in support for arbitrary precision integer arithmetic. [I'm also assuming that it's really arbitrary precision and not just 'really big but limited' precision.]
- elteto 9y ago
  OP mentioned a decimal data type, which in postgres can have a very large precision: https://www.postgresql.org/docs/10/static/datatype-numeric.html https://www.postgresql.org/docs/10/static/datatype-numeric.h.... Not the same as a floating point.
  - anarazel 9y ago
    16383 digits ought to be enough for everyone: "up to 131072 digits before the decimal point; up to 16383 digits after the decimal point" Non jokingly: I think precision wise that's really plenty enough for pretty much any halfway realistic usecase (no, don't do public key crypto using SQL datatypes). There's some issues with the numeric / decimal type, but lack of potential precision isn't one of them.
- aldanor 9y ago
  The proper way to handle this is fixed-point representation (whether the DB natively supports this or not). E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges. A typical scaling factor is 10^8, so if the exchange wants to tell you that the price is now 101.27, they send you an int64 value of 10127000000. You may then choose to take it further and store e.g. in 128-bit decimal. Of course, there's an issue of how to handle mul/div (i.e., rounding/truncation), but that's a separate question -- at least you'd be making decisions yourself rather than relying on how IEEE-754 floats work. Some DBs like PG, do, as a matter of fact, support decimals natively which makes things quite a bit simpler.
  - KMag 9y ago
    Certainly for decimalized exchanges, binary floating point is the wrong choice. (On a side note, it's also the wrong choice for spread sheets, at least as the default. The average freshly graduated accountant can have a reasonable grasp on decimal floating point rounding after a 5 minute explanation, whereas most professional software engineers with a decade of experience have a very weak understanding of IEEE 754 binary floating point.) > E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges. That's true of NASDAQ's OUCH, but some (many?) other protocols use decimal floating point. NYSE's UTP Direct[0] (See section 4.15) Is not fixed point, but actually their own decimal floating point system with a 32-bit significand and single ASCII character exponent ("0" to "6", indicating negated base 10 exponent, not allowing "5"). FIX SBE uses an 8 byte significand and a 1 byte (power-of-10) exponent, if I remember correctly. It's a bit unfortunate that (as far as I know) there aren't any major messaging formats supporting IEEE 754-2008 decimal64 values. [0]https://www.nyse.com/publicdocs/nyse/markets/nyse/NYSEUTPDirect_Specification.pdf https://www.nyse.com/publicdocs/nyse/markets/nyse/NYSEUTPDir...

dragonwriter 9y ago

Serious monetary work often needs values smaller than cents. Exact decimals or, better, exact fractions are often what you want.

lsaferite 9y ago
What is the typical use-case of a unit smaller than the smallest tradable currency unit? Mind you, I understand there are smaller units than the circulated currency (For the USD you have a mill which is 1/10 of a cent).
- tracker1 9y ago
  Bitcoin currency trades? Large fractions. Convert to/from yuan/yen, large whole numbers, back to US, somewhere in the middle. Tracking transactional values for large stock and currency trades would be an example. That said, could use integers to represent arbitrary precision.