CODEWITHSUNDEEP
javapostgresqlspring-data-jpa
Bob
Bob

Reputation: 339

String convert to timestamp in @Query using Spring Data JPA and postgresql

I would like to get from Postgresql all records before chosen date. Date is String. In my opinion the easiest way is to convert string into timestamp using to_timestamp. 

select * from workers where update_date < to_timestamp('2018-08-11', 'YYYY-MM-DD')::timestamp without time zone;

I created method in my repository like bellow but it doesn't work. 

@Query("select w from Worker w where w.update_date < to_timestamp(:date, 'YYYY-MM-DD')::timestamp without time zone") 
List<Worker> findBeforeDate(@Param("date") String date);

It throws

org.hibernate.hql.internal.ast.QuerySyntaxException:
unexpected token: : near line 1, column 104 [select w from com.oksa.workreport.doma.Worker w where w.update_date < to_timestamp(:date, 'YYYY-MM-DD')::timestamp without time zone]

Could anyone help me how to do this?

Upvotes: 2

Views: 17235

Answers (1)

Aditya Narayan Dixit
Aditya Narayan Dixit

Reputation: 2119

You'll have to escape the colons in your query. Try below:

@Query(value = "select w from Worker w where w.update_date < to_timestamp(:date, 'YYYY-MM-DD')\\:\\:timestamp without time zone", nativeQuery=true) 
List<Worker> findBeforeDate(@Param("date") String date);

Upvotes: 4

Related Questions