Money, Dates and the Decisions You Cannot Undo

Most of what an application gets wrong is fixable. A bad controller is refactored, a slow query is indexed, an ugly interface is redesigned.

Two decisions are not like that, because they are decisions about what was written down. If the amounts in your database are approximations, they stay approximations. If a timestamp lost the information needed to interpret it, that information is gone.

Both are made in the first week, usually by whoever writes the first migration, usually without anyone noticing a decision was being made.

Money is not a number

A float cannot hold 0.1 exactly, because it is binary and 0.1 is not. Every arithmetic operation carries a small error, and the errors accumulate.

$total = 0.1 + 0.2;            // 0.30000000000000004
$total === 0.3;                // false

This is not a PHP quirk; it is how binary floating point works everywhere. Applied to money it produces an invoice that is one cent out, a balance that never quite reaches zero, and a reconciliation report nobody can close.

Store integer minor units. The amount in the smallest unit of the currency, as an integer. Nothing in the path can be a float, so nothing can drift.

$table->bigInteger('amount_minor');     // 1050 = 10.50
$table->char('currency', 3);
$table->unsignedTinyInteger('currency_exponent')->default(2);

Store the currency, always. An amount without a currency is a number, not money. Multi-currency arrives later in more products than plan for it, and adding the column afterwards means deciding what every existing row meant.

Store the exponent rather than assuming two. Not every currency has two decimal places - some have none, some have three. Code that multiplies by a hundred is wrong for those, in both directions.

Decide rounding once, explicitly. Tax on three lines rounded per line gives a different total from tax on the sum. Neither is wrong; having both in the same codebase is. Write down which one this system does.

Never let a float near it. A decimal column read into a PHP float has undone the work. Cast it to a string or an integer and do arithmetic on integers.

Time has three different shapes

The mistake is treating them as one type.

An instant - when something happened. One correct value, stored in UTC, converted for display. Created, paid, logged in. This is what most timestamp columns are and where UTC is unambiguously right.

A date - a day with no time in it. A birthday, an invoice date, a public holiday. Storing it as a timestamp introduces a time zone into something that has none, and produces the classic bug where a date shifts by a day for users on one side of the world.

$table->date('invoice_date');           // not timestamp

A future wall-clock time - a scheduled event that must happen at a local time. This is the one that breaks, and it is why "always store UTC" is incomplete advice.

A reminder set for 09:00 next March, converted to UTC today, is stored as a specific instant. If the rules for that zone change before March - and governments change them, sometimes with weeks of notice - the stored instant is no longer 09:00 local. It will fire at the wrong time and nothing will report an error.

What has to be stored is the intention: the local time, the zone name, and the rule. The instant is computed when it is needed.

$table->time('local_time');
$table->string('timezone');            // 'Europe/Istanbul', not '+03:00'

The zone is a name, not an offset. An offset is what a zone happened to be at some moment; the name is the rule, and the rule is what survives a change.

The one that catches everyone

Carbon::now() returns the application's configured timezone. If that is set to something local and a column is cast to datetime, the value written is local time in a column everything else assumes is UTC.

It works perfectly until the clocks change, or until a second server has a different configuration, and then there is a table containing two kinds of timestamp with nothing to distinguish them.

Set the application timezone to UTC and convert at the edges - when displaying, and when accepting input. The user's zone belongs on the user record, not in a global.

Why these are worth an argument in week one

Everything else in an application is behaviour, and behaviour can be replaced. These two are what was recorded.

A float column migrated to integers means deciding what an inexact value should have been, row by row, and each of those rows is somebody's money. A timestamp that lost its zone cannot have it inferred - the information is not there to recover.

Ten minutes of disagreement while writing the first migration is the entire cost of getting both right.

If the columns are already wrong, the repair is a data migration with judgement in it, not a schema change. We do that work, and it runs in the same careful steps as a schema change that would otherwise lock the table.

The same argument turns up on two of the market pages. In Australia it is real-time payments, which land without warning and have to be reconciled by design. In Qatar it is a tax that does not exist yet and will.