Postgres Interval not working with native spring data JPA query

by

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

More Related Contents:

  1. Mapping PostgreSQL JSON column to a Hibernate entity property
  2. proper hibernate annotation for byte[]
  3. Using generics in Spring Data JPA repositories
  4. POSTing a @OneToMany sub-resource association in Spring Data REST
  5. How to map a PostgreSQL array with Hibernate
  6. What is difference between CrudRepository and JpaRepository interfaces in Spring Data JPA?
  7. Spring Data JPA – “No Property Found for Type” Exception
  8. Unable to connect to Postgres DB due to the authentication type 10 is not supported
  9. Postgres data type cast
  10. How to get a value from the last inserted row? [duplicate]
  11. What are the pros and cons of performing calculations in sql vs. in your application
  12. Adding Spring Dependency Injection in JavaFX (JPA Repo, Service)
  13. Spring boot JPA insert in TABLE with uppercase name with Hibernate
  14. Driver JDBC PostgreSQL with Android [duplicate]
  15. Hibernate, Postgres & Array Type
  16. How to map sql native query result into DTO in spring jpa repository?
  17. Check date between two other dates spring data jpa
  18. Spring Data JPA: Query by Example?
  19. Persisting UUID in PostgreSQL using JPA
  20. JpaRepository caches newly created object. How to refresh it?
  21. Apparent connection leak detected with Hikari CP
  22. Spring Data – Multi-column searches
  23. How to enable LockModeType.PESSIMISTIC_WRITE when looking up entities with Spring Data JPA?
  24. Autowiring fails: Not an managed Type
  25. Spring JPA native query with Projection gives “ConverterNotFoundException”
  26. Spring Data + JPA with multiple datasources but only one set of Repositories
  27. How to instrument / advice a Spring Data (JPA) repository?
  28. Externalize query in spring boot application
  29. How to use Postgres JSONB datatype with JPA?
  30. spring data jpa limit pagesize, how to set to maxSize

Leave a Comment