Postgres Interval Spring Data Dynamic Parameter not working [duplicate]

Question

I need to pass in a dynamic param to a Spring Data Postgres Native Query. The param is in an interval expression.

At the time of running the code, I verify from pgAdmin that the following returns 1 result only (correctly):

select * from recalls_t where created_date <= (now() - interval '18 hour')

PROBLEM:

1) In the Spring Data code, the following ?1 notation returns 2 results incorrectly:

@Query(value="select * from recalls_t where created_date <=  (now() - interval '?1 hour')", 
       nativeQuery=true)
public List<RecallsT> findActiveRecallsInLastHrs(@Param("hours") int hours);

2) Then I tried the solution in this thread: Postgres Interval not working with native spring data JPA query

They said to use a multiple of a single-unit interval. But this also returns 2 results incorrectly:

@Query(value="select * from recalls_t where created_date <=  (now() - 
       (interval '1 hour') * :hours)", 
       nativeQuery=true)
public List<RecallsT> findActiveRecallsInLastHrs(@Param("hours") int hours);

The only way I found to fix this is to hard-code the number "18" in my Spring Data query. So now there's no dynamic param, and I get 1 result correctly. None of the Dynamic Param implementations work.

interval '1 hour) * :hours' should be interval '1 hour') * :hours (note the single quotes around '1 hour' — user330315
– user330315, Commented Sep 4, 2019 at 21:04
Yes that's what I tried. Sorry for the typo, I corrected it in my post. I tried (interval '1 hour') * :hours and the result were incorrect (although there was no exception). I think there's a bug somewhere. — gene b.
– gene b., Commented Sep 4, 2019 at 21:07
Your question is old but I've got the same problem and found your question on Google. My turnaround is to combine 'make_interval' and 'date_add' postgres function. You could change your condition like : created_date <= date_add(now(),make_interval(hours => ?1) ) — Geoffrey Boulay
– Geoffrey Boulay, Commented Sep 22 at 8:20

Edward Aung · Accepted Answer · 2019-09-05 02:43:08Z

-1

Try by passing a String ?

@Query(value="select * from recalls_t where created_date <=  (now() - interval ?1)", 
       nativeQuery=true)
public List<RecallsT> findActiveRecallsInLastHrs(String interval);

// ... 

findActiveRecallsInLastHrs("1 hour");

answered Sep 5, 2019 at 2:43

Edward Aung

3,6222 gold badges14 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Postgres Interval Spring Data Dynamic Parameter not working [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related