I have entities with a validTill field of type Instant, that is a datetime value in utc, to indicate whether the entity has been soft deleted. I would like to query for all entities, but exclude the soft deleted ones from the result. I tried something like this:
@Query(
"SELECT e " +
"FROM MyEntity e " +
"WHERE e.validTill IS NULL OR e.validTill > CURRENT_TIMESTAMP"
)
Set<MyEntity> findValidMyEntities();
However, if my local time is 2022-04-26 10:00:00.000000+03 (meaning utc time is 2022-04-26 07:00:00.000000+00) and e.validTill value is 2022-04-26 08:00:00.000000+00, the entity is not included in the result, even though e.validTill value is after the utc time. Entities with validTill after my local time are included in the result. So, I'm quite sure the issue is with the CURRENT_TIMESTAMP being transformed. How can I fix this so that the e.validTill > CURRENT_TIMESTAMP only uses the utc format values?
I also know I could give the Instant.now() value as a parameter to be used instead of current_timestamp, but I would really like to avoid that.
EDIT a day later
I have not figured out the solution, but I feel I have come closer, and thought the things I have learned might help somebody.
The problem definetly is with using CURRENT_TIMESTAMP. I made sure that in my Spring Boot project application.yml file contains spring.jpa.hibernate.jdbc.time_zone: UTC. Additionally, I added a script to my version control, so that when the database is created, the db server uses UTC timezone:
ALTER DATABASE myDatabase SET TIME ZONE TO 'UTC'
I was sure that these changes would solve my problem, but I was wrong. If I make a query for the timezone of my database using SHOW timezone, I get the response UTC. However, if I make the same query through my API, I get my local timezone as a response. So, I suspect JDBC is the culprit here, and I have not been able to find the solution.