java - 在 JPQL 中查询获取过去 X 个月的数据

Question

我构建了以下 JPQL 查询，其中根据传入的时间戳发送结果。我希望修改它并传入一个数字(即月份值)，并获取过去 6 个月的所有结果。

如果相关的话，我正在使用 Oracle DB。我尝试在此查询中传递 datediff 方法，但它抛出编译错误。有没有办法做到这一点？

我当前的查询。

@Query("SELECT SUM(urlCount.referEventTotalCount) " +
            "FROM UrlCountEntity urlCount " +
            "JOIN urlCount.eventTypes et " +
            "WHERE urlCount.url.urlTx IN :urls " +
            "AND et.referEventTypeDs =:eventType " +
            "AND urlCount.updateTimeStamp <=:timeStamp") 
    Integer getTotalCount(@Param("urls") List<String> urls, @Param("eventType")String eventType, @Param("timeStamp")Timestamp timeStamp);

希望修改该行 -> "AND urlCount.updateTimeStamp <=:timeStamp")

  @Query("SELECT SUM(urlCount.referEventTotalCount) " +
            "FROM UrlCountEntity urlCount " +
            "JOIN urlCount.eventTypes et " +
            "WHERE urlCount.url.urlTx IN :urls " +
            "AND et.referEventTypeDs =:eventType ")
            // "AND urlCount.updateTimeStamp <=:timeStamp") // instead of this, pass in the numOfMths value to calculate how much data to show (example past 3 mths)
  Integer getTotalCount(@Param("urls") List<String> urls, @Param("eventType")String eventType, @Param("numOfMths")int numOfMths);

Answer 1

使用 Oracle，您可以使用带有负值的 ADD_MONTH 进行减去。

ADD_MONTHS(SYSDATE, -6)

您可以像这样调用此函数:

function('ADD_MONTH', CURRENT_DATE, -6)

因此您的查询可能如下所示:

 @Query("SELECT SUM(urlCount.referEventTotalCount) " +
        "FROM UrlCountEntity urlCount " +
        "JOIN urlCount.eventTypes et " +
        "WHERE urlCount.url.urlTx IN :urls " +
        "AND et.referEventTypeDs =:eventType " +
        "AND urlCount.updateTimeStamp >= function('ADD_MONTH', CURRENT_DATE, :month)")