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 19 · Accepted Answer · 2019-09-04T21:06:57.343

19

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 04 '19 at 21:06

answered May 03 '17 at 18:18

Thanks for your help – Ajit Soman May 03 '17 at 18:30
what about `interval '2 hour'`... ? – Karl.S Jan 30 '19 at 21:50
You mean `(interval '1' hour) * :hours` ? – Mike Quoro Jun 06 '19 at 06:13
I tried the `(interval '1 hour') * :hours`, but my results are incorrect, although there is no exception anymore. https://stackoverflow.com/questions/57795441/postgres-interval-spring-data-dynamic-parameter-not-working – gene b. Sep 04 '19 at 21:03
1

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. – Philippe Sep 29 '20 at 17:29
@Philippe answer is the correct way to do it. Much cleaner to pass simple ints. – mvd Dec 04 '20 at 01:36
`ERROR: function make_interval(days => character varying) does not exist` – DragonKnight Nov 04 '21 at 02:44
You need to pass numbers, not strings – Nov 04 '21 at 06:52

score 6 · Answer 2 · answered Feb 12 '19 at 10:29

6

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 '19 at 10:29

Sergio Gonzalez

215
1
4
11

This is what solved my issue - escaping the double colons since JPA was interpreting that as substitution variables – Phillip Nov 12 '19 at 17:29
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. – Philippe Sep 29 '20 at 17:27
@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 – James Nov 08 '22 at 14:54

Postgres Interval not working with native spring data JPA query

2 Answers2

Linked