Get records which are last updated in n minutes in jpa

Question

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

Answer 1

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.

Answer 2

The accepted answer failed with syntax error on my end but I was able to rewrite it and this worked, query would return all user records modified X minutes ago.

    @Query(value = """
    SELECT * from user u WHERE 
    u.last_modified <= DATE_SUB(NOW(), INTERVAL :timeInMinutes minute)
    """, nativeQuery = true)
    SomeType getRecord(Long timeInMinutes);