Find date inside each month-CodePudding

In my project, in the repository, this select finds the nearest date with a currency id that is less than or equal to the date whose range I set.

@Query(value = "select e. *from exchange_rates e where e.date <=:currentDate and e.local_currency_id = :localCurrencyId order by e.downloaddate desc limit 1", nativeQuery = true)
    Optional<ExchangeRate> findAllByDateAndLocalCurrency_Id(Date currentDate, Long localCurrencyId);

How can I find a date in each month that is greater than or equal to the date I want to bind, but with the condition that this date must be the closest to the end of each month. That is, you need an ID whose date (exactly the date, not downloaddate) is closest to the end of the month.

Tried doing something like this, but it didn't work

    @Query(value = "select e.date, e.id, e.date, e.local_currency_id, e.rate, e.downloaddate"  
                " from exchange_rates e "  
                "join (select local_currency_id, max(date) as max_date "  
                "from exchange_rates group by local_currency_id, date_trunc('month', date)) as m_date on e.date = m_date.max_date "  
                "where e.date >=:currentDate and e.date < m_date.max_date and e.local_currency_id = :localCurrencyId "  
                " order by e.downloaddate desc limit 1", nativeQuery = true)
        Optional<ExchangeRate> findAllByDateAndLocalCurrency_Id1(Date currentDate, Long localCurrencyId);

Formatted query:

SELECT
    e.date,
    e.id,
    e.date,
    e.local_currency_id,
    e.rate,
    e.downloaddate
FROM
    exchange_rates e
    JOIN (
        SELECT
            local_currency_id,
            max(date) AS max_date
        FROM
            exchange_rates
        GROUP BY
            local_currency_id,
            date_trunc('month', date)) AS m_date ON e.date = m_date.max_date
WHERE
    e.date >= :currentDate
    AND e.date < m_date.max_date
    AND e.local_currency_id = :localCurrencyId
ORDER BY
    e.downloaddate DESC
LIMIT 1

CodePudding user response：

Here it worked for me:

    @Query(value = "select e.date, e.id, e.date, e.local_currency_id, e.rate, e.downloaddate"  
                    " from exchange_rates e "  
                    "join (select local_currency_id, max(date) as max_date "  
                    "from exchange_rates group by local_currency_id, date_trunc('month', date)) as m_date on e.date = m_date.max_date "  
                    "where e.date >=:currentDate and e.local_currency_id = :localCurrencyId "  
                    " order by date_trunc('month', e.downloaddate) desc limit 1", nativeQuery = true)
            Optional<ExchangeRate> findAllByDateAndLocalCurrency_Id1(Date currentDate, Long localCurrencyId);