CODEWITHSUNDEEP
javapostgresqlspring-data-jpa
Ajit Soman
Ajit Soman

Reputation: 4069

Postgres Interval not working with native spring data JPA query

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"

Upvotes: 9

Views: 18236

Answers (2)

user330315
user330315

Reputation:

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 => ..)

Upvotes: 23

Sergio Gonzalez
Sergio Gonzalez

Reputation: 223

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

Upvotes: 7

Related Questions