6

I have this query that updates some prices higher that 2 days ago, but does not work

  @Transactional
    @Modifying
    @Query("update HotelDailyPrice hdp  set hdp.price = (select avg (hp.price) "
            + "from HotelPrice hp where hp.id = ?1 and hp.updateDate > CURRENT_DATE - 2), hdp.day = ?2 ")
    void updateDailyAveragePrice (Hotel hotel, String dayDate);
biniam
  • 8,099
  • 9
  • 49
  • 58
Ramon De Les Olives
  • 657
  • 2
  • 6
  • 8
  • "does not work" is not a problem description. Why doesn't it work? Do you get an error? If so, what's the error? Do you get unexpected results? If so, what results do you get, and what did you expect (based on what data)? – HoneyBadger Jan 26 '18 at 14:25
  • and what is the actual SQL that was invoked? –  Jan 26 '18 at 14:36

1 Answers1

9

Actually, JPA doesn't support time periods operations because not all databases support it. So you have following options:

  1. Calculate date programmatically (i.e. using calendar API or Java 8 Date Time API)
  2. Use native query
  3. Use concrete implementation if your JPA provider allows that. But AFAIK popular JPA providers don't give you such an option.
asm0dey
  • 2,841
  • 2
  • 20
  • 33