javapostgresqlspring-bootjpanativequery

Get records which are last updated in n minutes in jpa

I am working on a jpa query which requires to fetch all the records whose last updated time is more than 15 minutes My query goes like

Select * from user u where u.last_modified >= NOW() - INTERVAL '5 minutes'

I will be passing the minutes dynamically ie in my jpa

@Query("Select * from user u where u.last_modified >= NOW() - INTERVAL ':timeInMinutes minutes'")
getRecord(String timeInMinutes);

This does not work. I want to pass the minute dynamically. Can someone please help me with this

Solution

  • You can use this trick:

    @Query("""
        SELECT * from user u WHERE 
        u.last_modified >= (NOW() - (INTERVAL '1' minutes) * :timeInMinutes)
        """, nativeQuery = true)
SomeType getRecord(Long timeInMinutes);

    The idea here is to multiply timeInMinutes by 1, which will give you always timeInMinutes.

    Note: the type of timeInMinutes should be a number, and also you have to use nativeQuery = true because your query is not a JPA syntax.