How to set datetimeoffset values in JPQL?

Question

I'm trying to extend CrudRepository to implement a soft delete feature. Referencing this Q&A, I made a repository interface like this:

@NoRepositoryBean
public interface SoftDeleteCrudRepository<T extends SoftDelete, ID extends Long> extends CrudRepository<T, ID> {
    //...

    @Override
    @Query("update #{#entityName} e set e.delDT=current_timestamp where e.delDT is null and e.id=?1")
    @Modifying
    void deleteById(Long id);

    //...
}

The method runs well, but the delDT value doesn't contain offset value(eg. 2019-04-08 17:41:20.12 +00:00).

I can change the JPQL to native query and use sysdatetimeoffset() instead of current_timestamp, but I want to keep using JPQL if I can.

How to set datetimeoffset values in JPQL?

`CURRENT_TIMESTAMP` for JPQL is of type `java.sql.Timestamp`, [check this](https://www.objectdb.com/java/jpa/query/jpql/date), which cannot does not contain any offset value. I don't think it is doable with those JPQL functions, you might try to pass a `DateTimeOffset` object together with the `id` parameter into the query? `e.delDT=$2` — buræquete, Apr 09 '19 at 08:14
@buræquete I can, but It's a bit shame that I should input the value manually. — user2652379, Apr 09 '19 at 08:21
Can you try doing that? If that works, I can put it as an answer, otherwise I don't see any way of doing it with JPQL sadly... =/ — buræquete, Apr 09 '19 at 08:22
@buræquete I tried but failed with the same result. Maybe [this issue](https://jira.spring.io/browse/DATACMNS-834?jql=text%20~%20%22ZonedDateTime%22) is the reason? — user2652379, Apr 09 '19 at 09:51
Oh, of course, the offset value will always be same in the DB. You cannot pass +05:00 or -03:00 etc. It will map all to single offset (either defined by the database, or by the JDBC connection (i think)) so the value in DB will be shifted to that chosen timezone. You should have a separate column to store timezone data, if you want to keep that information. Sorry I misunderstood your problem. — buræquete, Apr 10 '19 at 01:07

user2652379 · Answer 1 · 2019-04-10T02:59:56.990

It seems there is no way to do this in JPQL, so I had to take a workaround. Basically, I used the SQL server's implicit conversion from varchar to datetimeoffset. So this might not work in other DBMS. I changed the repository interface like this:

@NoRepositoryBean
public interface SoftDeleteCrudRepository<T extends SoftDelete, ID extends Long> extends CrudRepository<T, ID> {
    //...

    @Query("update #{#entityName} e set e.delDT=?2 where e.delDT is null and e.id=?1")
    @Modifying
    void deleteById(Long id, String datetimeStr);

    //...
}

Then use like this:

authRepository.deleteById(entity.getSeq(), OffsetDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS XXX")));

I also changed delDT field type from OffsetDatetime to String from an entity class. Otherwise, an exception like this will occur.

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [2019-04-10 11:53:13.189 +09:00] did not match expected type [java.time.OffsetDateTime (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [2019-04-10 11:53:13.189 +09:00] did not match expected type [java.time.OffsetDateTime (n/a)]

How to set datetimeoffset values in JPQL?

1 Answers1