4 ms·

>(that's all a language or DB does anyway) Well, that's my point. Postgres does it for me. SQLite doesn't. I'm lazy. Also, SQLite's largest integer type is 64

>(that's all a language or DB does anyway) Well, that's my point. Postgres does it for me. SQLite doesn't. I'm lazy. Also, SQLite's largest integer type is 64 bits. That's awfully tight for some use cases.

cdelsolar 9y ago
import decimal; decimal.Decimal('3.14159')
- fauigerzigerk 9y ago
  A few words of explanation wouldn't hurt. Are you saying that I shouldn't ever do monetary calculations in the database at all? If so then I have to disagree.
  - sigzero 9y ago
    No, they are giving ways to do it outside of the db. You really answered your own bias on this. You want the db to handle it, so you wouldn't use sqlite in the first place.
prewett 9y ago
I'm curious how 64-bits would be tight for monetary data. A 64-bit integer is +/- 9e18, which is so big I don't even know how to actually say that number. Given that economies are only in the trillions, you have at least three orders of magnitude left when representing the largest amounts of money on the planet. Anything smaller would be a piece of cake. Also, seems like the obvious thing to do to store money in an integer would be to store it in pennies. Or if the numbers get too big, do what corporate financial reports do and use thousands.
- qznc 9y ago
  Pennies (2 decimals) is not fine enough. Exchanges and other financial services use 4, 6, or maybe even 8 decimals. With 6 decimals, a few trillion is the limit. If you implement multiplication and division with integer arithmetic, you need the additional decimals to keep the error small. See https://en.wikipedia.org/wiki/Salami_slicing https://en.wikipedia.org/wiki/Salami_slicing
- eesmith 9y ago
  The largest Zimbabwean banknote during hyperinflation was Z$100,000,000,000,000 or 1E14. I assume they printed more than 10,000 of those. I recall something about Oracle sending people down there to help with the bank or perhaps government databases deal with those numbers. I can't find confirmation of that, but https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe says "Computers could not handle the amount of zeros such that other forms of money had to be used to act as normal money (bearer’s cheques)."
- fauigerzigerk 9y ago
  64 bits can be tight if you have to set aside, say, 7 digits for decimal places in order to perform a lot of divisions/multiplications by small numbers, like exchange rates, without rounding errors adding up. But if you sacrifice 7 places you can no longer represent the market cap of Toyota Motor at the Tokyo stock exchange (24 trillion Yen) or the US GDP (~19 trillion USD). So if you don't have a data type that can represent all of these quantities as well as the results of any calculations up to the required precision then your code will become a lot more complex and error prone.

cdelsolar 9y ago

import decimal; decimal.Decimal('3.14159')

fauigerzigerk 9y ago
A few words of explanation wouldn't hurt. Are you saying that I shouldn't ever do monetary calculations in the database at all? If so then I have to disagree.
- sigzero 9y ago
  No, they are giving ways to do it outside of the db. You really answered your own bias on this. You want the db to handle it, so you wouldn't use sqlite in the first place.

prewett 9y ago

I'm curious how 64-bits would be tight for monetary data. A 64-bit integer is +/- 9e18, which is so big I don't even know how to actually say that number. Given that economies are only in the trillions, you have at least three orders of magnitude left when representing the largest amounts of money on the planet. Anything smaller would be a piece of cake. Also, seems like the obvious thing to do to store money in an integer would be to store it in pennies. Or if the numbers get too big, do what corporate financial reports do and use thousands.

qznc 9y ago
Pennies (2 decimals) is not fine enough. Exchanges and other financial services use 4, 6, or maybe even 8 decimals. With 6 decimals, a few trillion is the limit. If you implement multiplication and division with integer arithmetic, you need the additional decimals to keep the error small. See https://en.wikipedia.org/wiki/Salami_slicing https://en.wikipedia.org/wiki/Salami_slicing
eesmith 9y ago
The largest Zimbabwean banknote during hyperinflation was Z$100,000,000,000,000 or 1E14. I assume they printed more than 10,000 of those. I recall something about Oracle sending people down there to help with the bank or perhaps government databases deal with those numbers. I can't find confirmation of that, but https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe says "Computers could not handle the amount of zeros such that other forms of money had to be used to act as normal money (bearer’s cheques)."
fauigerzigerk 9y ago
64 bits can be tight if you have to set aside, say, 7 digits for decimal places in order to perform a lot of divisions/multiplications by small numbers, like exchange rates, without rounding errors adding up. But if you sacrifice 7 places you can no longer represent the market cap of Toyota Motor at the Tokyo stock exchange (24 trillion Yen) or the US GDP (~19 trillion USD). So if you don't have a data type that can represent all of these quantities as well as the results of any calculations up to the required precision then your code will become a lot more complex and error prone.