Spring错误地更新postgres中的时间戳wo时区

Question

我一直试图使用更新stmt更新时间戳字段。我尝试了java.sql.timestamp、java.util.date、Calendar、LocalDateTime、ZonedDateTime和其他一些java date util包。他们似乎都不起作用。

列: commit_ts (在Postgresql中没有Timezone的TimeStamp)在JPA/Hibernate中定义为

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

以下是查询

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

我也试过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")

注意:根据这个链接，SQL标准要求只写时间戳就等于没有时区的时间戳，PostgreSQL尊重这种行为。https://www.postgresql.org/docs/9.1/static/datatype-datetime.html

Springboot版本:1.5.7 version

Postgres JDBC驱动程序: 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.

有人能帮忙吗？

Answer 1

JPQL和SQL中的比较运算符是=非==

commit_ts==null 

应该是

commit_ts is null