Looks like this will do the trick. I’ll give it a try later today:
# From the examples:
Organization
|> where([o], amount_ge(o.payroll, 0))
sergio
thiagomajesk
@kip out of curiosity, have you considered supporting micro-dollars? I think this is a must for anyone creating currency-agnostic applications.
0
kip
ex_cldr Core Team
slouchpie
This “micro-dollar” abstraction seems “hacky” to me. This article mentions storing the value as bigint but ex_money stores the amount as a string.
In the referenced Google article, they using micro-dollar for their API return type. This makes sense for an API since a naive client will expect money to be a number, not a string, and it preserves a moderate amount of precision when we multiply by 1 million.
However, for internal storage and computation, using strings for persistence and Decimal for the amount results in as much precision as anybody could want.
0
thiagomajesk
Otherwise, you have to deal with different formats… The micro-dollars notation stores it without that information, it makes the data “agnostic” and formatting becomes a view concern.
I don’t have a lot of practical experience in this field, but I have read many articles in the past that don’t recommend using decimals/floats/doubles to represent currency - I think it has to do with precision.
0
D4no0
Floating point yes, this includes both float and double. Decimal however can store the number in the exact form, it is widely used to deal with currency.
1
LostKobrakai
That only works until a currency comes around, which requires higher precision. E.g. some of those crypto currencies subdivide to tiny fractions.
1
thiagomajesk
Thanks, @D4no0 and @LostKobrakai it seems I was putting decimals, floats, and doubles in the same bag. If someone stumbles across this thread, here’s a good article comparing the different approaches: Storing currency values: data types, caveats, best practices ·. I still think it might be useful to support micro-unit notation if you are looking into making the ecto type of the library parameterized though.
0
kip
ex_cldr Core Team
@thiagomajesk, its a good conversation and always appropriate to talk about storage of and calculations on money. Here are some of my thoughts on microdollars, floating point and serialising money in general.
Floating point is a bad idea for representing money
As you noted, there is a general recommendation not to use floats/doubles. Floating point is, by definition, making tradeoffs between size, performance and precision and accuracy that are different to the requirements for money. At the simplest level, floating point representations have issues with precision and associativity. For example:
# Accuracy cannot be guaranteed
iex> 0.1 + 0.2
0.30000000000000004
# Not associative
iex> a = 1.0000001
iex> b = 2.0000002
iex> c = 3.0000003
iex> (a + b) + c
6.0000006
iex> a + (b + c)
6.000000600000001
Integers aren’t perfect either
Integers are a better fit, they can at least represent a set of real numbers up to a certain precision (limited by the machine word size typically). It works appropriately (for money) for addition, subtraction and multiplication - but we still have issues with division. A good example is what happens when we divide 10 by 3:
# The infinite series of 3.3333...... is rounded
iex> 10 / 3
3.3333333333333335
# Integer division won't round trip
iex> div(10, 3) * 3
9
The issue of integer division isn’t solved by microdollars. This is one of the reasons why the Money.split/2 exits. It does division but also returns any remainder so that (money / number) * number + remainder == money is a guarantee.
Decimals
Decimal implementations, in computers, make different tradeoffs to precision, scale and speed. Whereas floating point emphasises speed, decimal tends to prioritise precision and scale. In many implementations, including the Decimal, the precision can be arbitrarily large but defaults to 28 digits.
iex> Decimal.Context.get()
%Decimal.Context{
precision: 28,
rounding: :half_up,
flags: [],
traps: [:invalid_operation, :division_by_zero]
}
Note too the rounding: :half_up. There are several rounding strategies in Decimal and its worth being familiar with them if you’re working with money. ex_money uses :half_even as its default rounding since that is most common in financial applications and is even known as banker’s rounding.
So why can Decimal preserve prevision better than floating point? In part because many decimal libraries, including Decimal represent the decimal number as an integer with an associated scale factor (to indicate where the decimal place is located). Decimal also stores an exponent which can optimise the size of the integer part without losing precision. Postgres which has a very similar type called numeric stores only the integer and scale.
Serialisation
Not all interchange formats or storage formats support decimal data. Thankfully Postgres and its many descendant databases do with the numeric data type. In addition ex_money_sql provides Ecto and Postgres data types that combines the currency code with the money amount into a single data type to maximise data integrity - the design goal is to never be in a position where the currency type is unknown or ambiguous.
For interchange formats that have no decimal data type, like JSON, the amount is cast to a string since thats the only JSON data type that can preserve precision and scale. In fact money is cast to a JSON object of the form {\"currency\":\"USD\",\"amount\":\"100\"}.
On Precision and Scale
The microdollar format has the advantage of performance while maintaining a fixed scale or 6 digits. However financial calculations are expected to retain 7 or 8 digits of scale during calculations - think stock market, home loan interest. And as @LostKobrakai points out, Digital Tokens (crypto) may require more than 6 digits of scale. Bitcoin calculations may need up to 10 digits of scale.
Finally
Money and Float make different tradeoffs amongst performance, precision, scale and accuracy. Float emphasises performance. ex_money prioritises precision, scale and accuracy. microdollar aims for a middle ground. Each approach has its benefits and compromises.
When it comes to money, I believe precision, scale and accuracy are paramount and thats why ex_money uses Decimal for Elixir representation, a composite data type called money_with_currency for Postgres and a string-based object format for JSON (including embedded Ecto schemas.
10
thiagomajesk
That’s pure gold, thanks for posting your thoughts @kip! Since you are also the author of CLDR and friends I would assume that localization or representation of those values is not something that particularly concerns you, right!? How are you dealing with localization today if you don’t mind me asking? (I also don’t want to hijack the thread, so maybe we should open another one to discuss this - it could be useful to others in the future). Cheers!
0