Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table exchange_rates(dt timestamp, curr1 text, curr2 text, rate numeric);
insert into exchange_rates values
('2018-04-01 00:00:00', 'EUR', 'GBP', 0.89),
('2018-04-01 01:30:00', 'EUR', 'GBP', 0.92),
('2018-04-01 01:20:00', 'USD', 'GBP', 1.23);
3 rows affected

create table transactions (dt timestamp, usr int, curr text, amount numeric);
insert into transactions values
('2018-04-01 18:00:00', 1, 'EUR', 23.12),
('2018-04-01 14:00:00', 1, 'USD', 15.00),
('2018-04-01 01:00:00', 2, 'EUR', 55.00);
3 rows affected

select t.dt, t.usr, t.amount * e.rate as conv_amount
from   transactions t
join lateral (select *
              from   exchange_rates er
              where  t.curr = er.curr1
              and    er.dt <= t.dt
              order by dt desc
              limit 1) e on true;


dt
usr
conv_amount


2018-04-01 18:00:00
1
21.2704

2018-04-01 14:00:00
1
18.4500

2018-04-01 01:00:00
2
48.9500

dt	usr	conv_amount
2018-04-01 18:00:00	1	21.2704
2018-04-01 14:00:00	1	18.4500
2018-04-01 01:00:00	2	48.9500