Pilotbase on PostgreSQL 18 converting payments in seven currencies to EUR with the latest ECB rate on or before each payment date

A shop that sells in euros, dollars, yen and Bahraini dinars has three kinds of amounts in one table: yen have no decimals, dollars have two and dinars have three. On top of that, the exchange rate depends on the day, and the European Central Bank doesn't publish one on weekends or bank holidays. Most of the bugs in multi-currency reports come from those two facts, not from the arithmetic.

We built the setup below on PostgreSQL 18.6, MySQL 8.4.11 and SQLite 3.54.1 on 2026-11-02: one million made-up payments in eight currencies across 2024 and 2025, and the ECB's real reference rates from 1999 to 2026-10-30. Everything here was measured on that data, and the scripts are short enough to run on your own.

How many decimals a currency has: ISO 4217

ISO 4217 gives every currency a code and a minor unit, the number of digits after the decimal point. The list maintained by SIX (published 2026-09-17) has 178 codes. Here is how they split:

text
minor unit  codes  which
2             139  USD, EUR, GBP, HUF, IDR, INR and most others
0              17  BIF CLP DJF GNF ISK JPY KMF KRW PYG RWF UGX UYI VND VUV XAF XOF XPF
3               7  BHD IQD JOD KWD LYD OMR TND
4               2  CLF UYW (units of account, not cash)
none          13  XAU, XAG, XDR and other metals, funds and test codes

Payment providers don't always follow the list. Stripe takes every amount in minor units, but it still sends Icelandic krónur (ISK) and Ugandan shillings (UGX) as two-decimal values that always end in 00, "for backward compatibility", even though ISO gives both currencies 0 decimals. To charge 5 ISK you send 500. For payouts in Hungarian forints and New Taiwan dollars, Stripe wants amounts that divide by 100. If you import provider amounts, map each provider's exponent per currency. Don't assume it matches ISO.

The tables: whole minor units and a currency table

Store the amount as an integer count of the currency's smallest unit (cents, yen, fils) and keep the exponent in a table of currencies. 12.345 BHD is 12345 with exponent 3, 1,500 JPY is 1500 with exponent 0, 19.99 USD is 1999 with exponent 2. Integers sum exactly in all three engines, including SQLite, which has no exact decimal type.

sql
create table currencies (
  code       char(3) primary key,
  minor_unit smallint not null check (minor_unit between 0 and 4)   -- ISO 4217 exponent
);
insert into currencies values ('EUR',2),('USD',2),('GBP',2),('JPY',0),('HUF',2),('ISK',0),('KRW',0),('BHD',3);

create table fx_rates (            -- ECB style: units of the currency per 1 EUR
  rate_date date    not null,
  currency  char(3) not null references currencies,
  per_eur   numeric(18,6) not null check (per_eur > 0),
  source    text not null default 'ECB',
  primary key (currency, rate_date)
);

create table payments (
  id           bigint primary key,
  paid_on      date    not null,
  currency     char(3) not null references currencies,
  amount_minor bigint  not null         -- cents, yen, fils
);

If you'd rather keep a decimal amount (it reads better in a SQL editor), you can still stop someone storing half a yen. Copy the exponent into the row and tie it to the currency with a two-column foreign key, then check the amount against it:

sql
alter table currencies add unique (code, minor_unit);

create table invoices (
  id         bigint generated always as identity primary key,
  currency   char(3)  not null,
  minor_unit smallint not null,
  amount     numeric(20,4) not null,
  foreign key (currency, minor_unit) references currencies (code, minor_unit),
  check (amount = round(amount, minor_unit))
);

insert into invoices (currency, minor_unit, amount) values ('JPY', 0, 1500.5);   -- violates check constraint
insert into invoices (currency, minor_unit, amount) values ('JPY', 2, 1500.50);  -- violates foreign key: JPY has 0
insert into invoices (currency, minor_unit, amount) values ('USD', 2, 19.999);   -- violates check constraint

MySQL 8.4 rejected the same three rows with the same table (tinyint and auto_increment instead). Check constraints are enforced from MySQL 8.0.16. SQLite enforced the two-column foreign key once pragma foreign_keys = on was set. There we kept integer minor units, because a numeric column in SQLite stores 19.99 as a binary float.

The ECB's rates: one CSV, wide, with N/A and a trailing comma

The ECB publishes euro reference rates for about 30 currencies around 16:00 CET on every working day, except TARGET closing days. The full history is one file, eurofxref-hist.zip, with one row per day and one column per currency. Each rate is units of the currency per 1 EUR. When we downloaded it, it had 7,126 days, from 1999-01-04 to 2026-10-30. Three details of the file get in the way of loading it:

- Currencies that no longer exist or have joined the euro are N/A, not empty. The Bulgarian lev's last rate is 2025-12-31 (1.9558), because Bulgaria adopted the euro on 1 January 2026.
- Every line ends with a comma, so a CSV reader finds a 42nd column with no name and no values.
- The file is wide: to join on currency you want one row per day and currency.

DuckDB turns it into a long file in one statement. Pilotbase opens DuckDB files too, so you can run this from the same editor:

sql
copy (
  select "Date"::date as rate_date, currency, per_eur::decimal(18,6) as per_eur
  from (unpivot (select * exclude (column42) from read_csv('eurofxref-hist.csv', all_varchar = true))
        on columns(* exclude ("Date")) into name currency value per_eur)
  where per_eur <> 'N/A'
) to 'ecb_rates.csv' (header false);
-- 221,702 rows: date, currency, rate

Then \copy fx_rates (rate_date, currency, per_eur) from 'ecb_rates.csv' csv in psql, LOAD DATA LOCAL INFILE in MySQL or .import --csv in SQLite. If you'd rather look at the file before you trust it, open it in csvProfiler first. It profiles every column in the browser, and the columns that are N/A from top to bottom stand out.

The ECB doesn't publish a rate for every currency. The Bahraini dinar isn't on its list, but the Central Bank of Bahrain pegs it at 0.376 BHD per US dollar, so a euro rate for BHD is the ECB's USD rate times 0.376. Mark rows like that so nobody takes them for ECB figures:

sql
insert into fx_rates (rate_date, currency, per_eur, source)
select rate_date, 'BHD', round(per_eur * 0.376, 6), 'ECB USD x 0.376 peg'
from fx_rates where currency = 'USD';

One more thing: the ECB says its reference rates are for information only and advises against using them for transactions. They suit reporting, VAT conversions where your tax office accepts them, and dashboards. What a customer was actually charged comes from your payment provider, and that amount is the one to store.

Joining on the date loses 30% of the payments

Payments happen every day, but rates exist only on ECB working days. In 2024 and 2025 that left 220 of the 731 days without a rate: 208 weekend days and 12 TARGET holidays (New Year, Good Friday, Easter Monday, 1 May, 25 and 26 December, in both years). The obvious join on the same date silently drops every payment made on those days:

sql
select count(*) as payments_non_eur,
       count(r.per_eur) as matched_on_same_day,
       count(*) - count(r.per_eur) as lost
from payments p left join fx_rates r on r.currency = p.currency and r.rate_date = p.paid_on
where p.currency <> 'EUR';

-- payments_non_eur | matched_on_same_day |  lost
--           600165 |              419061 | 181104

181,104 of 600,165 payments (30.2%) found no rate. With an inner join they vanish from the report without any error, and the converted total comes out about 30% short.

The latest rate on or before the date

The usual rule is: use the most recent rate published on or before the payment date. Also set a limit on how old that rate may be. This data shows why: the ECB stopped quoting Icelandic krónur from 2008-12-09 (290 per euro) to 2018-02-01 (125.01), a gap of 3,341 days. Without a limit, a 2015 ISK payment would be converted at the 2008 rate. With a 7-day window it gets NULL, and a NULL is easy to spot.

On PostgreSQL, a LATERAL subquery that walks the (currency, rate_date) primary key backwards does it in one step:

sql
select p.currency, count(*) as payments,
       sum(p.amount_minor) / 10::numeric ^ min(c.minor_unit) as total_in_currency,
       sum(round(p.amount_minor * 100 / (10::numeric ^ c.minor_unit * r.per_eur))) / 100 as total_eur
from payments p
join currencies c on c.code = p.currency
left join lateral (
  select per_eur from fx_rates r
  where r.currency = p.currency
    and r.rate_date <= p.paid_on and r.rate_date > p.paid_on - 7
  order by r.rate_date desc limit 1) r on true
where p.currency <> 'EUR'
group by p.currency order by total_eur desc;
Pilotbase result grid on PostgreSQL 18: payments, total in each currency and total in EUR for USD, GBP, JPY, HUF, KRW, ISK and BHD

amount_minor * 100 / (10^exponent * per_eur) gives euro cents for any source currency. A 3.971 BHD payment (3971 fils) at 0.427136 BHD per euro is 3971 × 100 / (1000 × 0.427136) = 929.68, so 930 cents, or €9.30. Here is the same lookup over the Easter weekend of 2025, when the ECB published nothing from Good Friday to Easter Monday:

Pilotbase result grid: payments from 2025-04-18 in USD, JPY and BHD, each matched to the ECB rate of 2025-04-17 (USD 1.136, JPY 161.98, BHD 0.427136) and converted to euro cents

The same query on three engines, measured

All 600,165 non-euro payments, converted and summed per currency. Times are wall clock on an Apple M4 Mac mini with default settings, the median of three runs where a run took under a minute:

text
approach                                         PostgreSQL 18.6   MySQL 8.4.11              SQLite 3.54.1
LATERAL / correlated subquery, latest rate       0.33 s            ~10 min (6.2 s per 6,047)  0.76 s
ranges from LEAD(), joined with >= and <         stopped after 2 min   -                      -
daily table (one row per currency and day)       0.10 s            0.98 s                     -
  building it for 2024-2025 (5,848 rows)         0.01 s            18.7 s

MySQL doesn't use the date range inside the LATERAL. EXPLAIN FORMAT=TREE shows Index lookup on r using PRIMARY (currency=p.currency) with about 7,139 rows, then a filter and a sort for every payment. In effect it re-reads the currency's whole rate history 600,000 times. We ran it on the first 10,000 payments (6,047 of them not in euros): 6.2 seconds, so about ten minutes for the full set. The LEAD() version is worse on PostgreSQL: the planner hashed the rate ranges on currency alone and checked each payment against every range for its currency, about four billion comparisons. We stopped it after two minutes.

The approach that is fast everywhere is a daily rate table: one row per currency per calendar day, filled forward from the last published rate, built once and then joined on equality. For two years and eight currencies it has 5,848 rows. Building it on PostgreSQL:

sql
create table fx_daily as
select r.currency, g.day::date as day, r.per_eur, r.rate_date
from (select distinct currency from fx_rates) c
cross join generate_series(date '2024-01-01', date '2025-12-31', interval '1 day') g(day)
cross join lateral (
  select currency, per_eur, rate_date from fx_rates r
  where r.currency = c.currency and r.rate_date <= g.day::date and r.rate_date > g.day::date - 7
  order by r.rate_date desc limit 1) r;
alter table fx_daily add primary key (currency, day);

On MySQL, generate the days with a recursive CTE (set session cte_max_recursion_depth = 1000 for two years). Fill each day with a correlated (select per_eur ... order by rate_date desc limit 1). That build took 18.7 s, and every query after it is a plain join:

sql
select p.currency, count(*) as payments, count(d.per_eur) as converted,
       sum(round(p.amount_minor * 100 /
           (case c.minor_unit when 0 then 1 when 2 then 100 when 3 then 1000 end * d.per_eur))) as eur_cents
from payments p
join currencies c on c.code = p.currency
left join fx_daily d on d.currency = p.currency and d.day = p.paid_on
where p.currency <> 'EUR'
group by p.currency;

Keeping the rate_date column in the daily table pays off later: anyone can see that a Sunday payment used Friday's rate. If you already have a calendar table, build the daily rates from it instead of generating the days.

The CASE in that query is not decoration

On MySQL we first wrote the scale as power(10, c.minor_unit). POWER() returns a double, so the whole expression became floating point, and MySQL rounds a double tie to even. Euro totals came out different from PostgreSQL's for five currencies, and 54 payments were a cent off. One of them:

text
payment 7827: 216.55 USD on Sunday 2025-04-20, ECB rate of Thursday 2025-04-17: 1.1360
216.55 / 1.1360 = 190.625 EUR exactly

DECIMAL arithmetic (CASE ... 100 ...)   round(19062.5) = 19063   -> 190.63
double (POWER(10, 2))                   round(19062.5) = 19062   -> 190.62

With the CASE, MySQL's DECIMAL arithmetic matched PostgreSQL's numeric to the cent in every currency. SQLite has only doubles: its totals matched in six currencies, and 2 USD payments came out a cent lower. To make SQLite exact, keep rates as integers too (micro-units, per_eur * 1000000) and round with integer division, (2 * a * 100 * 1000000 + f * r) / (2 * f * r). That matched PostgreSQL on all 600,165 rows. The money in SQL article explains why each engine rounds differently.

Convert once, round once

To get from one non-euro currency to another, the ECB table gives you a cross rate: yen per dollar on a day = JPY per EUR ÷ USD per EUR. The tempting shortcut is to convert to euro cents first, round, then convert to yen. That rounds twice. We did it both ways on all 249,813 USD payments:

sql
with x as (
  select p.amount_minor as usd_cents, u.per_eur as usd, j.per_eur as jpy
  from payments p
  join fx_daily u on u.currency = 'USD' and u.day = p.paid_on
  join fx_daily j on j.currency = 'JPY' and j.day = p.paid_on
  where p.currency = 'USD')
select count(*) filter (where round(usd_cents / 100.0 * jpy / usd)
                           <> round(round(usd_cents / usd) / 100.0 * jpy)) as differ
from x;

-- differ: 104092 (41.7%), never more than 1 yen
-- total: 4,061,622,478 yen converted once, 4,061,622,256 yen rounded twice

41.7% of the payments ended up a yen apart, and the two-year total was 222 yen apart. Neither number is huge, but they won't reconcile with a system that converts directly, and someone will spend an afternoon finding out why. Do the whole conversion in exact arithmetic and round once, to the target currency's exponent: 0 for yen, 3 for dinars. In general:

sql
-- amount in currency A (minor units) -> currency B (minor units) on one day
round(a.amount_minor * 10::numeric ^ cb.minor_unit * rb.per_eur
      / (10::numeric ^ ca.minor_unit * ra.per_eur))

A second choice is where the rounding happens: per payment, or on the converted total. Over the 600,165 non-euro payments, the sum of per-payment euro amounts and the rounded sum of exact amounts differed by €4.16 in total. By currency it ranged from −€0.69 (BHD) to +€2.00 (GBP). Store the converted amount per payment once, with the rate and rate date you used, and make every report add up those stored amounts. If a report converts again later, it will drift by a few cents from the stored figures.

Totals per currency, then converted totals

Adding amounts in different currencies gives a number that means nothing. Adding minor units makes it worse, because a yen and a cent count the same. Our table's sum(amount_minor) is 219,305,230,678. A GROUP BY ROLLUP (currency) on the native amounts prints that number as its total row, right under real figures. Report each currency in its own column with its own decimals, and give a grand total only in one converted currency:

text
currency  payments  total in currency        total in EUR
EUR        399,835  43,026,338.03            43,026,338.03
USD        249,813  26,972,939.94            24,425,441.98
GBP        100,114  9,079,901.71             10,662,219.01
JPY        100,634  1,574,495,679            9,472,129.92
HUF         49,649  2,025,182,834.00         5,107,915.56
KRW         40,295  5,569,175,710            3,627,815.75
ISK         29,834  500,668,897              3,405,882.63
BHD         29,826  1,234,689.024            2,974,679.36
all      1,000,000  (not a number)           102,702,422.24

Format each amount with its own exponent (to_char(..., 'FM999,999,990.000') for BHD, no decimals for JPY, KRW and ISK). If a report lists HUF with two decimals, someone will ask what 0.50 forint is. Forint coins stopped below 5, and our generated HUF amounts are whole forints, as real prices are, even though ISO still gives HUF two decimals.

Checklist

- Amounts as integers in minor units, or a decimal checked against the currency's exponent with a two-column foreign key.
- One currencies table with the ISO 4217 exponent, plus your payment provider's exponent if it differs (ISK, UGX on Stripe).
- Rates in long form, (currency, rate_date) as the key, with a source column for derived rates such as a peg.
- Latest rate on or before the date, with a maximum age. Never an equality join on the date.
- On MySQL, and for large reports anywhere, use a daily rate table filled forward and joined on equality.
- Exact arithmetic (numeric/DECIMAL, or integers in SQLite), no POWER() or other doubles in the expression.
- Convert directly with one cross rate, round once, to the target currency's exponent, and store the result with its rate and rate date.
- Totals per currency, and a grand total only after conversion.

Run these queries against your own tables: Pilotbase connects to PostgreSQL, MySQL, SQLite, DuckDB and more, with a SQL editor and a results grid you can export to CSV. Free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Loading a rates file or a payments export? csvToSql writes the CREATE TABLE and INSERTs for PostgreSQL, MySQL, SQLite or SQL Server, with a type override per column, so an amount column can stay an integer.

Open csvToSql

Related: Money in SQL: numeric vs float vs integer cents, and the rounding you can measure · A calendar table in SQL: one row per day for reports that don't skip empty days

Sources: ISO 4217 list one as published by SIX, list-one.xml (published 2026-09-17, counts by minor unit from our parse). ECB euro foreign exchange reference rates (publication time, TARGET days, information only) and the full history file, downloaded 2026-11-02, last date 2026-10-30. Central Bank of Bahrain, monetary policy (peg of 0.376 BHD per USD). Stripe, supported currencies (minor units, zero-decimal currencies, special cases for ISK, HUF, TWD, UGX). MySQL 8.4 reference manual: LATERAL derived tables, CHECK constraints. DuckDB UNPIVOT. The payments are generated (seed 603, eight currencies, 2024-01-01 to 2025-12-31). All results are from our own run on 2026-11-02 on PostgreSQL 18.6, MySQL 8.4.11, SQLite 3.54.1 and DuckDB 1.5.6, default settings, Apple M4 Mac mini.