banker’s round for mysql
for some reason, nobody has ever exposed the different rounding methods via mysql’s built-in ROUND() function, so if you want something different, you need to add it via a stored function. the function below is based on the T-SQL version here.
CREATE FUNCTION ROUND_TO_EVEN(val DECIMAL(32,16), places INT)
RETURNS DECIMAL(32,16)
BEGIN
RETURN IF(ABS(val - TRUNCATE(val, places)) * POWER(10, places + 1) = 5
AND NOT CONVERT(TRUNCATE(ABS(val) * POWER(10, places), 0),
UNSIGNED) % 2 = 1,
TRUNCATE(val, places), ROUND(val, places));
END;
use at your own risk. there may be edge conditions where this fails. but this matches up with the python and postgres based system i was crunching data from, except in cases where that system gets it wrong for some reason.
one thing you might notice is that it does not use any string-handling functions like the other “correct” solution floating around out there.
Comments
the reason to use something like ROUND_TO_EVEN() is that a banker’s round is “more fair” because it will round up a value that is exactly half-way between the two possibilities only half of the time, compared to the built-in ROUND() that will always do so. if your sales tax is 5%, how much is the total if the subtotal is $0.90. what if it was $0.70?
i wouldn’t say it is critical that you use a banker’s round, the important thing to me is that you know which one you’ve chosen. and if you are integrating with other systems, you may need to make sure you are using the same rounding method that those do. in that sense, banker’s round is a best and common practice for accounting systems.
the other question that comes up is “when do you round?” because that can drastically impact the results of a calculation. if someone has a 25% off coupon, you could choose to round values after applying the discount to each item price, the price of each line, or the subtotal.
Add a comment
Sorry, comments on this post are closed.
I think it is generally considered safe with financial data to use a DECIMAL(n,4) for storage as well as for intermediate calculations and use ROUND() to 2 decimals for display of the final result.
If not then why not and what particular problem does your routine solve?