Database: Postgres, Programming Technology: SpringBoot (Java 11)
I am trying to create @Query but the way I implemented it Hibernate won't produce it and I get the exception that the problem is the interval function. I want to add some days to a modifiedAt column depending on UserProps and then decide whether Db should select it or not. I call the Postgres INTERVAL internal function but it doesn't seem to work
@Query ("select a job from Job job, Project proj, User u, Userprops props, where job.status LIKE 'CANCELED' AND job.project = proj.id AND \ n" +
"proj.user = u.id AND props.user = u.id AND NOW () <job.modifiedAt + props.removeThingsAfterDays * FUNCTION ('INTERVAL' '1 day')")
List <XTMJobEntity> getAllJobsThatShouldBeDeletedFromDbBasedOnUserProps ();
In Postgres interval is not a function, but a data type. Even if it were a though you still would not use FUNCTION as a key word. In Postgres the query would be (leaving the obsolete join syntax in place):
select a job
from job job
, project proj
, user u
, userprops props
where job.status = 'CANCELED'
and job.project = proj.id
and proj.user = u.id
and props.user = u.id
and now () <job.modifiedat + props.removethingsafterdays * '1 day'::interval;
---- OR
select a job
from job
, project proj
, user u
, userprops props
where job.status = 'CANCELED'
and job.project = proj.id
and proj.user = u.id
and props.user = u.id
and now () <job.modifiedat + props.removethingsafterdays * interval '1 day';
Note: LIKE in a SQL statement without wild cards (% or _) is the equivalent of equal operator (=).
So perhaps your query string becomes:
@Query ("select a job from Job job, Project proj, User u, Userprops props, where job.status LIKE 'CANCELED' AND job.project = proj.id AND \ n" +
"proj.user = u.id AND props.user = u.id AND NOW () <job.modifiedAt + props.removeThingsAfterDays * INTERVAL '1 day'")