I have the following PostgreSQL table into which I insert values every hour.

 <?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <gesmes:subject>Reference rates</gesmes:subject>
    <gesmes:Sender>
        <gesmes:name>European Central Bank</gesmes:name>
    </gesmes:Sender>
    <Cube>
        <Cube time='2024-05-15'>
            <Cube currency='USD' rate='1.0832'/>
            <Cube currency='JPY' rate='168.43'/>
            <Cube currency='BGN' rate='1.9558'/>
            <Cube currency='CZK' rate='24.766'/>
            <Cube currency='DKK' rate='7.4617'/>
            <Cube currency='GBP' rate='0.85840'/>
            <Cube currency='HUF' rate='386.00'/>
            <Cube currency='PLN' rate='4.2690'/>
            <Cube currency='RON' rate='4.9758'/>
            <Cube currency='SEK' rate='11.6751'/>
            <Cube currency='CHF' rate='0.9800'/>
            <Cube currency='ISK' rate='150.30'/>
            <Cube currency='NOK' rate='11.6490'/>
            <Cube currency='TRY' rate='34.9765'/>
            <Cube currency='AUD' rate='1.6308'/>
            <Cube currency='BRL' rate='5.5827'/>
            <Cube currency='CAD' rate='1.4763'/>
            <Cube currency='CNY' rate='7.8212'/>
            <Cube currency='HKD' rate='8.4578'/>
            <Cube currency='IDR' rate='17340.14'/>
            <Cube currency='ILS' rate='4.0030'/>
            <Cube currency='INR' rate='90.4445'/>
            <Cube currency='KRW' rate='1471.93'/>
            <Cube currency='MXN' rate='18.2710'/>
            <Cube currency='MYR' rate='5.0986'/>
            <Cube currency='NZD' rate='1.7860'/>
            <Cube currency='PHP' rate='62.284'/>
            <Cube currency='SGD' rate='1.4609'/>
            <Cube currency='THB' rate='39.580'/>
            <Cube currency='ZAR' rate='19.9073'/>
        </Cube>
    </Cube>
</gesmes:Envelope>

Entity:

@Entity
@Table(name = "FXRATES")
public class FxRates {

  @Id
  @Column(name = "id", nullable = false)
  private Long id;

  @Column(name = "currency")
  private String currency;

  @Column(name = "rate")
  private Float rate;

  @Column(name = "created_at")
  private LocalDateTime createdAt;
}

I insert the currencies every hour from this XML file using SQL queries:

RestTemplate restTemplate = new RestTemplate();
String url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml";
ResponseEntity<Envelope> response = restTemplate.getForEntity(url, Envelope.class);

Envelope envelope = response.getBody();

List<FxRates> list = new ArrayList<>();
String time = envelope.getOuterCube().getCube().getTime();

ZonedDateTime zdt = ZonedDateTime.parse(time);
LocalDateTime ldt = zdt.toLocalDateTime();

for (CubeCurrency cubeCurrency : envelope.getOuterCube().getCube().getCube())
{
  FxRates fxRates = FxRates.builder()
          .currency(cubeCurrency.getCurrency())
          .rate(cubeCurrency.getRate())
          .createdAt(ldt)
          .build();
  list.add(fxRates);
}

fxRatesRepository.saveAllAndFlush(list);

I need to calculate Bid, Ask and Mid prices for currencies.

Using a SQL query I need to send as params sourceCurrency, targetCurrency and time and as a result I want to get:

private BigDecimal askRate;
private BigDecimal midRate;
private BigDecimal bidRate;
private String effectiveTimestamp;

ref https://www.moneydero.com/blog/bid-ask-mid-and-last-prices

How I can implement these calculations?