How to delete rows older than 7 days(1 week) using JPQL or NativeQuery?-CodePudding

I am building a Spring Boot application. Postgres being used as RDBMS. I have a table called activity I intend to delete all rows older than 7 days by including a WHERE clause to delete rows where created_at column is less than 7 days

So I tried this native query below since there is no ready-made JPA Query method to achieve this...

@Query(nativeQuery = true, value = "DELETE FROM activity WHERE created_date < (NOW() - INTERVAL 7 DAY)")
void deleteActivitiesOlderThanOneWeek();

So I got the error below

org.postgresql.util.PSQLException: ERROR: syntax error at or near "7"
  Position: 61
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-42.3.3.jar:42.3.3]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-42.3.3.jar:42.3.3]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-42.3.3.jar:42.3.3]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-42.3.3.jar:42.3.3]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-42.3.3.jar:42.3.3]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166) ~[postgresql-42.3.3.jar:42.3.3]
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118) ~[postgresql-42.3.3.jar:42.3.3]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2322) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:956) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2868) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2850) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2677) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2181) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1204) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1665) ~[hibernate-core-5.6.5.Final.jar:5.6.5.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:198) ~[spring-data-jpa-2.6.2.jar:2.6.2]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90) ~[spring-data-jpa-2.6.2.jar:2.6.2]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155) ~[spring-data-jpa-2.6.2.jar:2.6.2]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143) ~[spring-data-jpa-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138) ~[spring-data-commons-2.6.2.jar:2.6.2]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.16.jar:5.3.16]
    at org.springf

Plese what am I doing wrong or any suggestion of what I can do. Thanks.

CodePudding user response：

Change the condition to:

 (NOW() - INTERVAL '7 DAYS')

See: https://www.postgresql.org/docs/current/functions-datetime.html

CodePudding user response：

Updated: It worked after I changed my approach

 @Modifying
    @Query(value = "DELETE  FROM Activity act WHERE act.createdDate <= :p")
    void deleteActivitiesOlderThanOneWeek(@Param("p") Instant date);`

Then during usage, I passed an argument of type Instant, subtracting a period of 7 days. Like below:

  var xDays = Instant.now().minus(Period.ofDays(7));
      activityRepository.deleteActivitiesOlderThanOneWeek(xDays);

This did the magic for me