Spring JPA Hibernate giving error updating timestamp wo timezone in postgres

Question

I have been trying to update timestamp field using an update stmt. I tried java.sql.timestamp, java.util.date, Calendar, LocalDateTime, ZonedDateTime and bunch of other java date util packages. None of them seem to be working.

Column: commit_ts (TimeStamp without Timezone in Postgresql) is defined in our JPA/Hibernate as

@Column(name = "COMMIT_TS")
@Temporal(TemporalType.TIMESTAMP)
private Timestamp commitTs;

here is the Query

@Timed(name = "updateWorkAllocationStatus")
@Transactional
@Modifying(clearAutomatically = true)
@Query(
      nativeQuery = true,
      value = "UPDATE wlm_work_allocation SET commit_ts=:ts " + 
              "WHERE allocation_id = :allocationId " + 
              "and status = :status " + 
              "and commit_ts == null"
)
int updateWorkAllocationStatus(
    @Param("timestamp") Timestamp ts,
    @Param("allocationId")Long allocationId,
    @Param("status")String status
);

I also tried NativeQueries

@NamedNativeQuery(name = "WorkAllocationEntity.updateCommitTs",
        query="UPDATE wlm_work_allocation SET commit_ts= TIMESTAMP WHERE allocation_id=:allocationId and status=:status and commit_ts==null")

Note: According to this link,The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. https://www.postgresql.org/docs/9.1/static/datatype-datetime.html

Springboot version :1.5.7.RELEASE

Postgres JDBC Driver : 9.0-801.jdbc4

PostgresSQL DB: 9.6.5

Error: 
org.postgresql.util.PSQLException: ERROR: operator does not 
exist: timestamp without time zone == unknown
Hint: No operator matches the given name and argument type(s). 
You might need to add explicit type casts.

Can anyone help?

Simon Martinelli · Accepted Answer · 2018-06-09 13:34:36Z

2

The comparison operator in JPQL and SQL is = not ==

commit_ts==null

should be

commit_ts is null

edited Jun 9, 2018 at 13:34

answered Jun 8, 2018 at 14:30

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

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

3 Comments

Vijay Ram Over a year ago

ahh. I have not looked at that angel. The error was misleading. Thank you for the input. let me try and update you. Thank you once again

Vijay Ram Over a year ago

your input was valuable. Can i request you to edit the answer to commit_ts IS NULL.(the SQL way of null check) I should have been bit careful but silly me. This answer will help others

Simon Martinelli Over a year ago

ok. i've edited the answer. Can you mark the answer as correct, please?

Collectives™ on Stack Overflow

Spring JPA Hibernate giving error updating timestamp wo timezone in postgres

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