Postgres Interval not working with native spring data JPA query
java, postgresql, spring-data-jpa
Solution
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 => ..)`
Problem
I have created a native query with interval. The query works fine when i hard code `day` in query: ``` @Query(value="select * from orders where created_date < clock_timestamp() - interval ' 5 days'",nativeQuery=true) ``` But when i provide data with `@Param` like this: ``` @Query(value="select * from orders where created_date < clock_timestamp() - interval :day 'days'",nativeQuery=true) List<Order> getData(@Param("day") String day) ``` I got this error: Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"