A 99.99 EUR invoice created in Paris displays as "100.00" for a customer in New York, and a monthly sales report counts a transaction from January 31st as belonging to February, depending on the timezone of the server generating the report. Both bugs share the same structural cause: a database schema that models amounts and dates as "neutral" numbers and timestamps, when neither is ever actually neutral once an application grows past a single country and a single currency.
The currency problem: never store a float
The most dangerous reflex is storing a monetary amount as FLOAT or DOUBLE. Base-2 floating point doesn't exactly represent most decimal values: 0.1 + 0.2 doesn't equal exactly 0.3 under IEEE 754 floating point arithmetic, and accumulated across thousands of transactions, that drift becomes a real accounting problem, not an academic curiosity.
-- BAD: FLOAT accumulates rounding errors
CREATE TABLE invoices_bad (
id BIGSERIAL PRIMARY KEY,
amount FLOAT NOT NULL
);
-- GOOD: NUMERIC has exact precision, or store the amount as an integer minor unit (cents)
CREATE TABLE invoices (
id BIGSERIAL PRIMARY KEY,
amount_minor_units BIGINT NOT NULL, -- amount in cents, never a floating-point unit
currency_code CHAR(3) NOT NULL REFERENCES currencies(code)
);
Two solid approaches coexist: storing a NUMERIC(19, 4) (exact precision, but watch for rounding during arithmetic operations) or storing an integer in the minor unit (cents for the euro, but careful: not every currency has two decimal places - the Japanese yen (JPY) has none, the Kuwaiti dinar (KWD) has three). The integer minor-unit approach, popularized by libraries like Stripe's or Martin Fowler's Money pattern, avoids floating-point rounding issues entirely and forces you to make the currency associated with every amount explicit.
CREATE TABLE currencies (
code CHAR(3) PRIMARY KEY, -- ISO 4217
minor_unit_digits SMALLINT NOT NULL -- 2 for EUR/USD, 0 for JPY, 3 for KWD
);
The trap of an amount with no attached currency
An amount stored without its currency (an amount column in a table where the currency is "implicit" or defined globally for the application) becomes a latent bug the moment the application expands into a second market. The structural rule is simple: every stored amount carries its own currency, in the same row, never inferred from global config or a fragile join.
-- BAD: currency is implicit, assumed fixed for the whole application
CREATE TABLE payments_bad (
id BIGSERIAL PRIMARY KEY,
amount NUMERIC(19,4) NOT NULL
);
-- GOOD: currency travels with the amount
CREATE TABLE payments (
id BIGSERIAL PRIMARY KEY,
amount_minor_units BIGINT NOT NULL,
currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
exchange_rate_to_base NUMERIC(19,10), -- rate at transaction time, for reporting
recorded_at TIMESTAMPTZ NOT NULL
);
Capturing exchange_rate_to_base at transaction time (rather than recomputing it after the fact) is essential for any historical financial reporting: a January sales report must use January's exchange rate, not whatever rate is current on the day the report happens to be generated. Recomputing after the fact with the current rate produces reports whose numbers silently change depending on when you happen to view them.
The timezone problem: TIMESTAMP vs TIMESTAMPTZ
PostgreSQL offers two distinct types, and conflating them is the single most common source of time-related bugs:
TIMESTAMP(without timezone): stores a "naked" date and time, with no timezone information at all. PostgreSQL performs no conversion whatsoever.TIMESTAMPTZ(with timezone): actually stores an absolute instant internally as UTC, and displays/converts it based on the session's timezone at read time.
-- BAD: TIMESTAMP loses the timezone information at write time
CREATE TABLE events_bad (
id BIGSERIAL PRIMARY KEY,
occurred_at TIMESTAMP NOT NULL -- "2025-11-20 14:30:00" in which timezone?
);
-- GOOD: TIMESTAMPTZ stores an unambiguous absolute instant
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
SET TIME ZONE 'Europe/Paris';
INSERT INTO events (occurred_at) VALUES ('2025-11-20 14:30:00');
-- Stored internally as 2025-11-20 13:30:00 UTC
SET TIME ZONE 'America/New_York';
SELECT occurred_at FROM events;
-- Displayed as 2025-11-20 08:30:00-05
TIMESTAMPTZ never actually stores the timezone itself: it always converts to UTC on write and converts back on read according to the session's timezone. That's exactly what you want for an event timestamp (payment processed, order created): an absolute instant, displayed locally for whoever is looking at it.
The specific trap: timeless business dates (birthdays, due dates)
For a birth date or a contractual due date, which has no time-of-day component and must stay identical regardless of the reader's timezone, TIMESTAMPTZ is the wrong choice: converting "1990-05-15" to UTC and back through a different timezone can shift the date by a day. The right type is DATE, which carries no timezone notion at all:
CREATE TABLE contracts (
id BIGSERIAL PRIMARY KEY,
due_date DATE NOT NULL, -- no time, no timezone: "2025-12-01" stays "2025-12-01" everywhere
created_at TIMESTAMPTZ NOT NULL DEFAULT now() -- precise creation instant, with timezone
);
Diagnosing an existing timezone bug
The typical symptom: a "per day" activity report counts events near midnight on the wrong day depending on who's viewing it. To check, compare the session's timezone at aggregation time against the business timezone the end user actually expects:
SHOW timezone;
-- Correct aggregation: explicitly convert to the business timezone
-- BEFORE truncating to the day, never after
SELECT
date_trunc('day', occurred_at AT TIME ZONE 'Europe/Paris') AS business_day,
count(*)
FROM events
GROUP BY business_day
ORDER BY business_day;
Truncating occurred_at directly without an explicit conversion to the business timezone silently falls back to the session's default timezone, which depends on server or connection driver configuration - a detail that's completely invisible in the application code calling the query.
Rounding during conversion: where the money "disappears"
Even with amounts correctly stored as integer minor units, converting between currencies introduces a new risk: rounding. Splitting an amount into equal shares, or converting an amount through an exchange rate, almost always produces a result that doesn't land exactly on a whole minor unit.
-- Converting 100.00 EUR to USD at a rate of 1.0847
-- 100.00 * 1.0847 = 108.47 -> simple rounding, no issue here
-- But splitting 100.01 EUR across 3 equal beneficiaries:
-- 100.01 / 3 = 33.336666... -> impossible to split exactly into cents
The rule to apply consistently is to never let a split or conversion calculation silently lose or create money. The standard technique (used notably by Stripe for multi-recipient payment splits) is to compute each share by truncation, then assign the rounding remainder to the last share or to a designated beneficiary, so that the sum of the shares equals exactly the original amount, down to the cent:
-- Splitting 10001 cents (100.01 EUR) into 3 equal shares
-- Share 1: floor(10001 / 3) = 3333
-- Share 2: floor(10001 / 3) = 3333
-- Share 3: 10001 - 3333 - 3333 = 3335 (absorbs the remainder)
-- Total: 3333 + 3333 + 3335 = 10001 -> exact, nothing disappeared
This detail looks minor at the scale of a single transaction, but becomes a genuine accounting reconciliation problem at the scale of millions of transactions, where cents systematically lost or created by a poor rounding strategy end up representing significant, hard-to-explain discrepancies during a financial audit.
When the simple fix isn't enough
For an application that needs to display recurring schedules (opening hours, recurring reminders) across daylight saving time transitions, storing a fixed TIMESTAMPTZ isn't enough: a reminder for "every day at 9am, Paris time" has to be recomputed against the IANA timezone name (Europe/Paris), not against a frozen UTC offset, because that offset changes twice a year. Storing Europe/Paris as a string and recomputing the UTC instant for each occurrence, rather than freezing an offset, is the only approach that stays correct over time.
Takeaway
An amount with no attached currency and a timestamp with no explicit timezone are both bugs that only surface with international usage, never in local development with a single user in a single timezone. The structural rule to apply from the moment you design the schema: every stored amount carries its currency on the same row, every stored instant uses TIMESTAMPTZ, and every timeless business date uses DATE, never a bare TIMESTAMP.
