5

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.

gene b.
  • 10,512
  • 21
  • 115
  • 227
  • `interval '1 hour) * :hours'` should be `interval '1 hour') * :hours` (note the single quotes around `'1 hour'` –  Sep 04 '19 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. Sep 04 '19 at 21:07

1 Answers1

-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");
Edward Aung
  • 3,014
  • 1
  • 12
  • 15