Postgres Interval not working with native spring data JPA query
You can't provide a value for an interval like that. You need to multiple the parameter value with your interval base unit:
"select * from orders
where created_date < clock_timestamp() - (interval '1' day) * :days"
As you are dealing with days, you can simplify that to:
"select * from orders
where created_date < clock_timestamp() - :days"
Another option is the make_interval()
function. You can pass multiple parameters for different units.
"select * from orders
where created_date < clock_timestamp() - make_interval(days => :days)"
The notation days => ...
is a named parameter for a function call. If the variable represents hours, you could use make_interval(hours => ..)
One solution is provided in this entry Spring Boot Query annotation with nativeQuery doesn't work in Postgresql
Basically:
@Query(value="select * from orders where created_date < clock_timestamp() - ( :toTime )\\:\\:interval",nativeQuery=true)
'toTime' is a Param from your repository and could be days, hour, minute... etc(review interval doc in Postgres) @Param("toTime") String toTime