An invoice shows Rs 1,247.99. The sum of its lines is Rs 1,248.00. One paisa, and it will take somebody an afternoon to find, because nothing is wrong with the arithmetic — the arithmetic is doing exactly what it was told.
The column is a FLOAT.
What the computer thinks 0.1 is
A float stores a number in binary, the way a decimal fraction stores one in base ten. In base ten you cannot write one third exactly — 0.3333 forever. In base two you cannot write one tenth exactly, for the same reason.
So 0.1 becomes the nearest representable binary value, which is slightly more than 0.1. Every arithmetic operation carries that difference along, and the errors accumulate:
php > var_dump(0.1 + 0.2 == 0.3);
bool(false)
php > printf("%.20fn", 0.1 + 0.2);
0.30000000000000004441That is not a PHP quirk. It is IEEE 754 and it is the same in JavaScript, Python, Java and MySQL. The language is not wrong; the type is wrong for the job.
For a temperature reading or a distance it does not matter. For money it matters, because money is exact by definition. There is no such thing as approximately Rs 1,248 on an invoice.
Where it actually shows up
Rarely in one calculation. Always in an aggregate.
One line of Rs 99.99 is fine. Two hundred lines summed in SUM() drift, and the drift is a few paise. Then the total is compared to a payment, the comparison fails by 0.01, and an order sits unreconciled.
Three symptoms that are all the same bug:
An invoice whose lines do not add to its total. A WHERE amount = 1247.99 that returns nothing when the row is visibly there. A balance that is off by one paisa and nobody can reproduce it.
The last one is the worst, because it is intermittent: it depends on the exact sequence of values, so it appears for one customer and not another and looks like a data problem rather than a type problem.
The two correct answers
There are two, and both are right. The choice is about what you are storing, not about which is better.
Integer paise. Store Rs 1,247.99 as the integer 124799. Arithmetic is exact because integers are exact. It is the fastest, the smallest, and impossible to get subtly wrong.
ALTER TABLE invoices
ADD COLUMN total_paise BIGINT NOT NULL DEFAULT 0;Use BIGINT, not INT. A signed INT tops out at about Rs 2.1 crore in paise, which is a limit a growing business will reach at the worst possible moment.
DECIMAL. DECIMAL(19,4) stores the number as decimal digits, so it is also exact, and it reads naturally in SQL.
ALTER TABLE invoices
ADD COLUMN total DECIMAL(19,4) NOT NULL DEFAULT 0;Four decimal places rather than two, because intermediate values need them. A unit price of Rs 0.0125 across a thousand units is a real thing, and rounding to two at that point loses money in a way that compounds.
Which to pick: integers if your application does the arithmetic and you want it impossible to get wrong. DECIMAL if the database does the arithmetic — a lot of SUM, reporting queries, people writing SQL against it by hand. A rough rule: application-heavy leans integer, report-heavy leans DECIMAL.
What is not acceptable is mixing them across tables. Pick one and use it everywhere, because every boundary between the two is a conversion and every conversion is a place to drop a factor of a hundred.
Keep the units in the name
If you go with integers, the single most useful thing you can do is put the unit in the column name and the variable name.
$total = 124799; // rupees? paise? nobody knows
$total_paise = 124799; // no ambiguity, everEvery bug in an integer-money system is a factor-of-100 error, and every one of them comes from somebody reading a bare amount and guessing. amount_paise cannot be misread. It is free and it prevents the entire class.
Convert at the edges only — once when it enters, once when it is displayed:
function to_paise(string $input): int {
// "1,247.99" -> 124799, without ever touching a float
$clean = preg_replace('/[^0-9.-]/', '', $input);
[$rupees, $paise] = array_pad(explode('.', $clean, 2), 2, '0');
$paise = str_pad(substr($paise, 0, 2), 2, '0');
return (int) $rupees * 100 + (int) $paise * ($rupees[0] === '-' ? -1 : 1);
}
function format_rupees(int $paise): string {
return number_format($paise / 100, 2, '.', ',');
}Note that to_paise never multiplies by 100 as a float. (int) (1247.99 * 100) gives 124798 on some inputs, because the float was slightly under and the cast truncates. That single line is one of the most common money bugs there is.
Rounding is a decision, not a detail
18% GST on Rs 99.99 is Rs 17.9982. You have to store something, and the choice of what is a business decision that somebody should make on purpose.
Round half up is what most people expect and what most tax rules specify. round() in PHP does this. Banker’s rounding, which rounds halves to even, is fairer over many operations and will surprise an accountant reading one line.
The part that causes real disputes is when you round. Rounding each line and summing gives a different total from summing and rounding once, and both are defensible:
// Per line, then sum
17.9982 -> 18.00, three lines -> 54.00
// Sum, then round once
17.9982 * 3 = 53.9946 -> 53.99One paisa, and it is the paisa somebody will ask about. Pick one, write it down, and apply it in one function that everything calls. The failure is not picking the wrong rule — it is different parts of the codebase picking different ones.
Migrating a float column that is already live
You cannot cast in place and trust it. The values in the column are already the wrong numbers; casting carries the error across.
Add alongside, backfill, verify, then switch:
-- 1. Add the new column
ALTER TABLE invoices ADD COLUMN total_paise BIGINT NULL;
-- 2. Backfill THROUGH DECIMAL, never through the float directly
UPDATE invoices
SET total_paise = CAST(ROUND(CAST(total AS DECIMAL(19,4)) * 100) AS SIGNED);
-- 3. Find every row where the round-trip does not match
SELECT id, total, total_paise, total_paise / 100 AS back
FROM invoices
WHERE ABS(total - total_paise / 100) > 0.001;Step 3 is the one that must not be skipped. Rows that fail it are rows whose stored value was already wrong, and they need a human to decide what the number should have been. Usually there are very few, and they are usually the ones somebody already complained about.
Then write both columns for a release, compare them in production for a week, and only drop the float once nothing has diverged. Dropping it the same day is how a migration turns into an incident.
The ledger question, once amounts start moving
Storing a single amount correctly is the easy half. The harder one arrives the first time money moves between two places, and it is worth knowing before you build it rather than after.
The instinct is a balance column that you add to and subtract from. It works until two requests update it at once, or a process dies halfway through a transfer, and then there is no way to find out what the balance should have been.
The alternative is to store the movements and derive the balance. Every change is an immutable row — amount, direction, what caused it, when:
CREATE TABLE ledger_entries (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
account_id BIGINT UNSIGNED NOT NULL,
amount_paise BIGINT NOT NULL, -- negative for a debit
reference VARCHAR(64) NOT NULL, -- invoice id, payment id
created_at DATETIME NOT NULL,
INDEX idx_account (account_id, created_at)
) ENGINE=InnoDB;The balance is SUM(amount_paise). Nothing is ever updated or deleted, so the history explains itself: when somebody asks why an account shows Rs 4,320, the answer is a list of rows rather than an argument.
The obvious objection is that summing millions of rows is slow, and it is — which is what a periodic snapshot is for. Store a balance as of a date, and sum only the entries after it. You keep the audit trail and the fast read.
This is more work than a balance column and it is not worth it for a shopping cart. It is very much worth it for anything with a wallet, credits, refunds or payouts, because those are the systems where somebody eventually asks where a specific rupee went, and a single mutable number cannot answer.
Three more places money leaks
JSON. json_encode of a float gives 1247.9899999999998, and JavaScript’s JSON.parse has the same binary problem. Send money as an integer of minor units, or as a string. Never as a JSON float.
Currency stored nowhere. A bare amount column assumes one currency forever. Add a currency CHAR(3) beside it even if you only have one today — retrofitting it once orders exist in two currencies is a much larger job than adding a column now. And note that not every currency has two decimal places.
== on money. Even with exact types, comparing across a conversion boundary reintroduces the problem. Compare integers to integers, or use a tolerance and be explicit about it.
The short version
Never FLOAT or DOUBLE for money. BIGINT in minor units, or DECIMAL(19,4). Put the unit in the name. Convert at the edges and nowhere else. Decide the rounding rule once, in one function.
It is about twenty minutes of care at the start of a project and it removes an entire category of bug that is genuinely miserable to debug later — because the symptom is always one paisa, and one paisa never looks like a type error.
