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

Question

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?

you can use native query @Query("select * from workers where update_date < to_timestamp('2018-08-11', 'YYYY-MM-DD')::timestamp without time zone", nativeQuery="true") — TheSprinter
– TheSprinter, Commented Jan 11, 2019 at 10:50
@Query(value = "select * from workers where update_date < to_timestamp('2018-08-11', 'YYYY-MM-DD')::timestamp without time zone", nativeQuery=true) I have to add value and remove quotation marks for true. Anyway stil have problems because I pass parameters there (date) and it doesn't work — Bob
– Bob, Commented Jan 11, 2019 at 11:04

Aditya Narayan Dixit · Accepted Answer · 2019-01-11 12:03:35Z

4

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

edited Jan 11, 2019 at 12:03

answered Jan 11, 2019 at 11:59

Aditya Narayan Dixit

2,12915 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bob Over a year ago

It works. At the same time I create another solution:

@Query(value="select w from Worker w where w.update_date < CAST (:date as timestamp without time zone) order by update_date",  			nativeQuery = true) 	List<Worker> findBeforeDate(@Param("date") String date);

I use CAST instead of to_timestamp

Bob Over a year ago

By the way you have to use value before select

Aditya Narayan Dixit Over a year ago

Yes, my bad. Updated the answer.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related