Postgresql- Hibernate Query Syntax exception : org.hibernate.hql.ast.QuerySyntaxException: unexpected token at near

Question

I am using Postgresql with hibernate. now() is working fine but now() at time zone UTC is throwing an error.

Version: Postgresql9.5. The same sql query is working fine in pgAdmin.

String sql = "SELECT a FROM XspJobRequest a " +
            "WHERE a.xspJobTypeId = :xspJobTypeId " +
            "AND a.xspJobStatusId = :xspJobStatusId " +
            "AND a.nextAttemptTs < (now() at time zone 'utc') " +
            " ORDER BY a.insertTs";
    try
    {
        Query query = entityManager.createQuery(sql);
        query.setMaxResults(limitReturnCount);
        query.setParameter("xspJobTypeId", jobType.getId());
        query.setParameter("xspJobStatusId", XspJobStatusEnum.eNew.getId());
        List<XspJobRequest> retval = query.getResultList();

        if (!alreadyActive)
        {
            et.commit();
        }
        return retval;
    }

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: at near line 1, column 167 [SELECT a FROM com.hp.jampub.db.entity.XspJobRequest a WHERE a.xspJobTypeId = :xspJobTypeId AND a.xspJobStatusId = :xspJobStatusId AND a.nextAttemptTs < (select now() at time zone 'utc') ORDER BY a.insertTs]

createQuery takes in JPQL, not SQL. So labelling something as "sql" is clearly your problem, since you seem to expect to be able to dump in any old random SQL in there (and you cannot). JPQL is defined by a spec. "NOW()" is not part of it (though CURRENT_TIMESTAMP is), and neither is "TIME ZONE 'UTC'". There are ample docs of JPQL on the internet — user3973283

Twister Twister · Accepted Answer · 2018-10-09T10:13:15

The Billy Frost's comment is correct. You can create new Date parameter instead of using now() at time zone 'utc'

Postgresql- Hibernate Query Syntax exception : org.hibernate.hql.ast.QuerySyntaxException: unexpected token at near

1 Answers