PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL query for currency conversion rates

Let’s consider the database table for currency conversion looks as below:

fromCurrency toCurrency effective date conversion rate
USD INR 1-Mar-2024 80
USD INR 1-Jan-2024 85
USD GBP 1-Oct-2023 .80
USD GBP 1-Mar-2024 .85
USD AUD 1-Mar-2023 1.55
USD AUD 1-Mar-2024 1.60

If database is queried on 1-Feb-2024, to get the conversion rate where fromCurrency=’USD’, it should return

fromCurrency toCurrency effective date conversion rate
USD INR 1-Jan-2024 85
USD GBP 1-Oct-2023 .80
USD AUD 1-Mar-2023 1.55

The same query on 15-April-2024 should return

fromCurrency toCurrency effective date conversion rate
USD INR 1-Mar-2024 80
USD GBP 1-Mar-2024 .85
USD AUD 1-Mar-2024 1.60

Basically for all possible combinations of (fromCurrency, toCurrency) there many many records in the database and on a particular date when the database is queried only one of those many records is applicable based on the effective date. I am trying to figure out the best possible postgres query to get the records.

Tags:

3

Answers


  1. 0

    Try the following PostgreSQL query to get the applicable conversion rates based on the query date (or pickup system date):

    SELECT
  from_currency,
  to_currency,
  MAX(effective_date) FILTER (WHERE effective_date <= '2023-12-05') AS effective_date,
  MAX(conversion_rate) FILTER (WHERE effective_date <= '2023-12-05') AS conversion_rate
FROM conversion_rates
WHERE from_currency = 'USD'
GROUP BY from_currency, to_currency
ORDER BY from_currency, to_currency;

    I have kept effective_date and from_currency configurable, which you might want to change (ie. pickup system date) as needed.

    My Fiddle for this to play around.

    Login or Signup to reply.
  2. 0

    Both in jOOQ and native PostgreSQL, you can use DISTINCT ON, which is a clause where you can request only the first record per DISTINCT ON expression, given the ORDER BY clause:

    With SQL:

    SELECT DISTINCT ON (fromCurrency, toCurrency) 
  fromCurrency, toCurrency, effectiveDate, conversionRate
FROM conversions
WHERE effectiveDate <= :currentDate
ORDER BY fromCurrency, toCurrency, effectiveDate DESC

    With jOOQ:

    var c = CONVERSIONS.as("c");

ctx.select(c.FROM_CURRENCY, c.TO_CURRENCY, c.EFFECTIVE_DATE, c.CONVERSION_RATE)
   .distinctOn(c.FROM_CURRENCY, c.TO_CURRENCY)
   .from(c);
   .where(c.EFFECTIVE_DATE.le(currentDate))
   .orderBy(c.FROM_CURRENCY, c.TO_CURRENCY, c.EFFECTIVE_DATE.desc())
   .fetch();

    See this db-fiddle

    Login or Signup to reply.
  3. 0

    Here is a query that can get you the conversion rate on a specific system date. If you don’t want to use the system date, you are going to need to replace current_date, with a parameter.

    WITH currList AS (
   SELECT *,
          max(effectiveDate) FILTER(WHERE effectiveDate <= CURRENT_DATE) OVER(PARTITION by fromCurrency, toCurrency) as maxEffDate
   FROM ConversionRates
)
SELECT *
FROM currList 
WHERE effectiveDate = maxEffDate
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search