0

I have tried make a different method in Spring Data JPA Repository which should adding the given hours to timestamp.

public interface ActivationCodeRepository extends CrudRepository<ActivationCode, Long> {

  @Query(value = "select a from ActivationCode a where a.creationTime + INTERVAL '1 hour' * :hoursAgo  <=  CURRENT_TIMESTAMP and a.type = :type and a.account = account")
  List<ActivationCode> getAllAddedAtLeastXHoursAgo(@Param("account") Account account, @Param("type") int type, @Param("hoursAgo") int hoursAgo);


}

It is not working because of it:

  • INTERVAL '1 hour' * :hoursAgo

exactly:

'1 hour'

The IDE underscores and given this error:

<'expression'>, <'operator'>, GROUP, HAVING or ORDER expected.

I have tried do some research and find how exactly I should adding the given hours to the creationTime but not found anywhere.

Warmix
  • 217
  • 8
  • 15
  • I don't think obfuscation layers like JPA support `INTERVAL` literals. You will need a native query. –  Aug 21 '18 at 06:32

3 Answers3

2

I don't believe PostgreSQL allows passing the INTERVAL statements require a hard-coded string with an interval '1 day'-style input; however you can achieve this by casting a string to an interval.

Try changing the SQL query in your code to:

select a from ActivationCode a where a.creationTime + (:hoursAgo||' hour')::interval <=  CURRENT_TIMESTAMP and a.type = :type and a.account = account

Alternatively, I've just found this previous StackOverflow answer, this would be worth a try but might have the same issue (presuming it's related to Spring Data JPA's query parser):

select a from ActivationCode a where a.creationTime + :hoursAgo * INTERVAL '1 hour' <=  CURRENT_TIMESTAMP and a.type = :type and a.account = account
Timshel
  • 1,653
  • 12
  • 9
  • 1
    `INTERVAL '1 hour' * 5` is absolutely valid in Postgres but not for JPA –  Aug 21 '18 at 06:31
  • Agreed, which is why I'd suspect the issue is with JPA's query parser; it's definitely worth trying the 1st suggestion in my answer though (building the interval as a string and casting using `::interval`) which might work around the JPA query parser limitations. I strongly suspect that the 2nd example in my answer (which just swaps the multiplication arguments around) will have the same issue as the original query. – Timshel Aug 21 '18 at 06:37
  • Thanks for your suggestions, but without nativeQuery I couldn't do that. – Warmix Aug 21 '18 at 17:42
2

Like a_horse_with_no_name said I had to use native query to do something like that so I changed my method to:

  @Query(value = "select * from activation_codes a where a.type = :type and a.id_account = :id_account and a.creation_time <= NOW() - :hoursAgo * INTERVAL '1 hour'", nativeQuery = true)
  List<ActivationCode> getAllAddedAtLeastXHoursAgo(@Param("id_account") int id_account, @Param("type") int type, @Param("hoursAgo") int hoursAgo);

And it works correct. Thanks for all help.

Warmix
  • 217
  • 8
  • 15
1

If anyone is interested after all this time, I did this in the most despicable way possible. But it worked.

The @Query Annotation is like below;

@Query("SELECT q FROM QueryStatusDao q WHERE q.jobStatus='Submit' AND q.submitTime < now() - :interval")
List<QueryStatusDao> getRunnableParkedQueries(@Param("interval")String interval);

And I call this method like this;

queryStatusRepository.getRunnableParkedQueries("INTERVAL '1 hour'");

If I find any other non-despicable method to do this, I will update.

Tharaka Devinda
  • 1,952
  • 21
  • 23