Money Related Data - Do you Use Decimals or Integers?

Coffee Lounge
1

So a big part of my app handles money and stripe payments, and I am debating with myself whether to do all my money related calculations and data storage as Decimal types or Integer types…

So as a decimal, 1.23€ would be 1.23, and as an Integer it would be 123 (as Stripe represents it).

I’d be interested to hear your views and experiences!

Best wishes,
Antony.

1 Like
2

Floats can have rounding problems. This is also a problem in JavaScript. Try in the console 0.10 + 0.20, it will return 0.30000000000000004. Using only integers will prevent this kind of problems.

2 Likes
3

Would using a fixed-point numeric type solve that?

Such as decimal(13,2)

4

Good question Max… I have my database fields defined like that, but am just working through some challenges in doing the maths in server actions… especially as I am working in multiple currencies so doing a lot of multiplication and division by an exchange rate.

… so it feels like just rounding everything to 2 decimal places ought to fix that kind of problem, but the round() function in Wappler doesn’t take a number of decimal places to round to :frowning:

So at the moment am trying to find a syntax in server actions to x100 -> round -> /100 which is work in progress.

I’d like to work in decimals in Wappler so my numbers are easily used in inputs and <p> tags without gazillions of multiplication and division by 100 all over the place!

5

We store as decimal(10, 2) to avoid Gaussian Rounding (or Bankers Rounding). When a total shows a couple of cents more or less than it’s actual value (over a period of time, depending on the shear amount of calculations). When we first came across this phenomenon it caused a huge amount of confusion as so many accounts were such minimal amounts out of place… Total head-feckery!

https://www.databasejournal.com/features/mysql/rounding-down-bankers-rounding-and-random-rounding-in-mysql.html

Now you know to watch out for it!

:wink:

6

Interesting Dave!

So you store as DECIMAL(10,2) in the database… but if you are doing an equation like this:

amount_accounting = amount_local / exchange_rate

Which needs rounding to 2DP, do you do it in a server action or in a stored procedure, and what issues have you found in each method?

7

To be honest Antony that is something we have not had to deal with as we deal with final amounts in Euros with no exchange rates involved. Working with more decimal places increase the Bankers rounding. I did read somewhere it obtains a natural balance at some point but it totally threw us in to a brick wall at first. I’d think to display the value to the client including the extra decimal places would make sense in the case of exchange rates as they are dynamic and changing every moment, so maybe actually storing the exchange rate at the time of conversion may help? Am interested in following any responses to this thread as it does interest me and is somewhat of a conundrum in my head…

8

Well I'll keep playing with it and see where I get to...

at the moment I've hit a wall/bug in terms of actually doing any rounding at all in a server action, so I'll get that fixed first...

Thanks for your help Dave! :slight_smile:

9

Interesting, I haven’t yet needed to deal with multiple currencies in Wappler but I have been dealing with carts, checkouts and payment gateways.

For example, £8.00 would be stored as 8.00. I’m then using an expression like {{(price.toNumber() * qty).formatCurrency("£", ".", ",", "2")}} for formatting which works nicely.

Are you using a third-party for currency conversion? I’ve used (not with Wappler) Math.js and also Currency.js in the past to deal with more complex equations and currencies.