Follow

Should you store money as DECIMAL? As an INTEGER in the most basic unit of a currency?

@celia it would prevent rounding errors that you get when storing money as double or something similar.

But in one system that dealt with money, we had problem of decimals overflowing and not being able to store large values. That was related on conversion between currencies when exchange rate was really large and converted amount of money was large too.

Some languages have arbitrary precision numbers, but the problem is actually saving them in a database.

@celia no worries at all.

At database level same idea applies. Keep things precise to prevent rounding and try to find datatype that can handle large enough values. Decimal looks like a good idea.

@celia I heard using integers are the best, floating point math is wonky at times, of course if you do js you don't have a choice and just have to deal with wonky math.

@celia I think the asker is the one that accepts the answer, and this is only a poll, groups of people can be wrong in many cases.

@sotolf I wish I had your level of confidence in general. :)

@celia It's mostly just that I come across as way more confident and right than what I am, I have found that I have a talent to make people think I'm competent and confident, while usually I'm just very good at faking it, My ex-wife thought I was great at english, the people here at work thought I knew what I was doing the first years :p mostly I just have found small things that work for me :p

@lil5 @celia bigint is also awfully slow, and how many are usually using a workaround such as that, it's kind of stupid that the standard number type is really unaccurate.

@sotolf @celia slow depends on how you are using it, I can't see much of a use case for using 64bit numbers client side at such a scale that the user would notice.

@lil5 @celia Well, I mean if you're using js for anything that is dealing with numbers it's going to be slow no matter what, it's not a language that is supposed to be used to anything else than basic website logic, it's so overused that it's kind of really bad lately, a program that is written in a real programming language is always going to be better.

@celia

As a NUMERIC with a suitable scale (digits after the decimal point).

For instance, Interest can create money amounts with arbitrary scale.

@celia If you're using Postgres, you probably want to use the NUMERIC type to store currency. It's not necessarily decimal that you want to avoid, but you should be wary of float/double types when precision is important. Like with currency values.

@celia Yes, this would be equivalent, and I would bet it maps to Postgres NUMERIC.

@celia "Fixed-precision" is the key here. If you don't have access to something like DECIMAL or NUMERIC then integer would be the alternative.

@celia For that SO question, DECIMAL is the SQL Server equivalent of NUMERIC. (I think, it's been several years since I've worked with SQL Server)

@celia IMO, actually, there is no way to decide because it depends of the actual business case you want to represent. What does your "amount of money" represent. If it's a price for something, then go for cents as internet, if it's account balance, if it's an average cost of something, etc. you might get diffrent answer depending if you need precise rounding, precision < 0.01...

@celia In part it depends how well you know your application. Decimal is often the best answer IMO, but then your have to be careful about things like the fact that USD goes down 100ths of a dollar, but gasoline is priced in thousandths. 🙄🤦🏻

(Stock markets used to use 32nds of a dollar (‼️), but that hasn’t been true since the 80s or so, and I haven’t seen any more recent examples of that.)

@hjertnes @celia Amen! But it could be worse; at least you aren’t asking about calendars! :-)

@celia FWIW, my Django+Postgres app uses numeric(6,2), because I know the application space will always be okay with that. I probably could’ve gotten away with integer dollars!

@celia From working with WooCommerce (stores prices as strings representing double) I’d say store the prices as integers. You’ll also need to store information on the currency being used but it ends up being much cleaner.

If you’re worried about int overflows you can always store them as strings too.

But generally I’d say avoid any floating point precision numbers when storing prices :)

@celia I forgot to add to this: my opinion on this also stems from substantial experience working with stripe.com APIs — they store all monetary amounts as integers.

Having worked in both systems (string + double vs. integers) using integers is ultimately vastly superior. At least in my experience.

Sign in to participate in the conversation
Fosstodon

Fosstodon is an English speaking Mastodon instance that is open to anyone who is interested in technology; particularly free & open source software.