CODEWITHSUNDEEP
javaspringpostgresqljpql
Kacper
Kacper

Reputation: 3

Adding interval to timestamp. Producing @Query in Repository interface

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 ();

Upvotes: 0

Views: 902

Answers (2)

Kacper
Kacper

Reputation: 3

The answer for my problem is following query:

@Query(value = "select job.* " +
       "from jobs job INNER JOIN projects proj ON job.project_id = proj.id INNER JOIN users u ON proj.user_id = u.id " +
       "INNER JOIN user_properties props ON props.user_id = u.id " +
       "where job.status = 'CANCELLED' " +
       "AND NOW() < job.modified_at + props.remove_cancelled_jobs_days * Interval '1days';", nativeQuery = true)

Upvotes: 0

Belayer
Belayer

Reputation: 14861

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'")

Upvotes: 1

Related Questions