Storing monetary amounts in db? Use decimals, not floats!

Lot’s of people will be surprised (or shocked, when it happens in production) when they see that mysql can work like this:

Query:

SELECT transaction_id, amount FROM transactions WHERE amount>20.5

Response:

123|20.5

Query:

SELECT transaction_id, amount FROM transactions WHERE amount=20.5

Response:

No rows

So, 20.5 is really greater than 20.5 in sql database. Sometimes.

Oh yeah, it looks like balance_tracker developers were not aware of that (until today). So, it’s like a bomb with timer for hundreds of websites which rely on this and other modules which use floats to store monetary-like amounts in db and are doing some sql selects which compare values stored in db against something. In our case, it was 20.563333333… stored in balance, which prevented our auto-payments system (built on top of balance_tracker+ubercart) in our big project from working properly.

How does it happen?

When you calculate some value in PHP (and you use floats in PHP, of course):

<?php
$total
= 3 / 2; // total is now 1.5555555555... in PHP
?>

and store it in sql table in cell of type float, it is stored as 1.55555555555..
(but it shows as 1.5 when you are looking at the value using phpMyAdmin or SqlYog - which is understandable, but really adds confusion!)

That’s why you need decimals. When you put 1.55555555.. in cell of type decimal, it is stored as 1.55. So, it works as most usual people expect it to work when dealing with financial stuff.
And when you use Drupal fields to store monetary amounts in your nodes, use decimal as field type, too!

UPD: another good (best, I guess - but requiring more work) solution (as suggested in comments) would be to use integer column in mysql and operate with integers in php/sql, only rendering values as floats to website users.

Comments

Drupal Commerce uses integers - see commerce_currency_decimal_to_amount()

13 December, 2012

I'm not questioning the gist of your article and the dangers of precision and unexpected rounding.
Thanks for reminding us.
But is your specific example really correct? Does 43.3 get stored as 43.3333333333333 ...?
I find that hard to believe...

13 December, 2012

Thanks. Tell me if updated example looks better now :)

14 December, 2012

My Money CCK field module for Drupal 5 also used integers: http://drupalcode.org/project/money.git/blob/refs/heads/5.x-1.x:/money.m....

13 December, 2012

+1 for using integers as much as possible. Just have "1" equal one of the lowest unit in the given currency (one cent, one yen, one pre-decimalization British pence) and do the math from there. Or even have "1" equal one hundredth of a cent, if necessary. Then all the problems of dealing with decimal numbers exist only when you need to do the multiplication to display the amount in a human-readable amount (for example, multiply the number of cents by 100 to get the dollar amount).

13 December, 2012

I agree that storing integers can be a good solution.
But when we talk about fields, I find it way easier to use decimals.

13 December, 2012

bojanz's post is great, and I'd follow-up your point about fields with cross-database server compatibility. MySQL has decimals, but does every other system Drupal users use? If not, your field module would not be usable for them, right?

13 December, 2012

Ryan :)
let’s pretend you’re a Drupal user with no programming skills.
How can you use integers in fields in your entity? You need to

  1. Prepare field formatter (since I don’t see any D7 modules for that - correct me if I’m wrong)
  2. If you have some programming skills, but you build something on top of another balance or ubercart module, you need to convert integers to floats back and forth when you deal with these modules. Every time something goes from external module to your module.

That’s what I was trying to say.

14 December, 2012

What Garett Albright describes here is exactly the way the Money CCK Field dealt with it, and I suspect Drupal Commerce does too :)

13 December, 2012

Exactly, your best bet is storing the amount as an integer in one column, and the precision in the other column.
This is what GnuCash does as well.

MySQL might allow you to use decimals, but PHP has no such concept by default, so as soon as you get your amount from the db, it's once again prone to rounding errors.
This is why PHP has BCMath and GMP and we are in fact evaluating using phpseclib.sourceforge.net/math/intro.html for Commerce 2.x (wraps both libraries, and provides a userspace fallback if both are missing).

13 December, 2012

Yes, its definitely easier using integers to store and do the math on, then format the currency/value on the display side. Drupal Commerce does this. It is -way- easier than dealing with floats/decimals.

13 December, 2012

Another vote for integers, but I'd also point out (as some people above have hinted at) that you should keep the currency as an integer throughout your application.

This is the big benefit of integers over MySQL decimals - you can be confident that as your value gets passed between database, PHP, XML service, web framework or whatever that there are no rounding errors being introduced.

As part of our migration strategy from using floats we also made it super-clear what we were doing by ensuring we added a _pence suffix to all our variables (you might prefer _cents). It wouldn't be a surprise to see a line like this in our code:

$price_pence = intval(($_GET['price'] * 100) + 0.5);

There are a couple of things worth noting here - first is the use of intval() rather than round(). That is because round() will return a float! We add 0.5 to emulate the "round to closest" behaviour of round().

Second, we've got price_pence on the left, and price with a multiplier on the right. I'm changing the data, so I'm changing the identifier. If I saw

$price_pence = $foo['price']

then I would immediately check that it was doing the correct thing and if it was then add a comment for the confusing variable names.

13 December, 2012

Oh, I forgot about the conversion to pounds for display. We used a modifier in a smarty template:

{$price_pence|pence_as_pounds)

Or for our multi-currency sites:

{$price_pence|pence_as_pounds:$currency_code)

This would add the decimal place in the correct location and add the currency symbol. We even made it locale-aware, so an Irish site would might show €12.34 where the same site in Spain would show 12,34€

13 December, 2012

Post new comment

Private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

Note for potential spammers: all links in your comment will not be indexed by search engines.

Anton Sidashin

Anton Sidashin senior developer, Pixeljets co-founder

I'm a web developer specializing in PHP and Javascript, and Drupal, of course. I'm building Drupal projects since 2005, and I was working as full-time senior engineer in CS-Cart for a while, building revolutionary e-commerce software. In my free time, I enjoy playing soccer, building my body in gym, and playing guitar.

Drupal.org ID: restyler
Drupal association member