Postgres Interval not working with native spring data JPA query

Question

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"

score 23 · Accepted Answer · 2019-09-04 21:06:57Z

23

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

edited Sep 4, 2019 at 21:06

answered May 3, 2017 at 18:18

user330315

Sign up to request clarification or add additional context in comments.

8 Comments

Ajit Soman Over a year ago

Thanks for your help

Karl.S Over a year ago

what about interval '2 hour'... ?

Mike Quoro Over a year ago

You mean (interval '1' hour) * :hours ?

gene b. Over a year ago

I tried the (interval '1 hour') * :hours, but my results are incorrect, although there is no exception anymore. stackoverflow.com/questions/57795441/…

Philippe Over a year ago

Nice, and works in a Query annotation, but does not work in a Formula annotation. There, I ended up having make_interval(0,0,0,1) to specify a 1-day interval.

|

Sergio Gonzalez · Accepted Answer · 2019-02-12 10:29:06Z

7

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

answered Feb 12, 2019 at 10:29

Sergio Gonzalez

2331 gold badge4 silver badges11 bronze badges

3 Comments

Phillip Over a year ago

This is what solved my issue - escaping the double colons since JPA was interpreting that as substitution variables

Philippe Over a year ago

This works in a Query annotation but does not work in a Formula annotation. In the Formula annotation, I ended up writing it as make_interval(0,0,0,1) to specify a 1 day interval.

James Over a year ago

@sergio could you please tell me how i can pass my sql correctly in the query annotation : DELETE FROM TABLE WHERE my_column < (NOW() - INTERVAL '30 minutes') and i want to pass '30 minutes' as a param in my java method

Collectives™ on Stack Overflow

Postgres Interval not working with native spring data JPA query

2 Answers 2

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related