如何使用PostgreSQL在弹簧数据存储库中通过当前时间戳过滤查询？

发布于 2025-01-24 07:07:55 字数 1258 浏览 3 评论 0原文

我的实体具有有效till类型即时的字段，即UTC中的DateTime值，以指示该实体是否已软删除。我想查询所有实体，但从结果中排除了软删除的实体。我尝试了这样的事情：

@Query(
    "SELECT e " +
    "FROM MyEntity e " +
    "WHERE e.validTill IS NULL OR e.validTill > CURRENT_TIMESTAMP"
)
Set<MyEntity> findValidMyEntities();

但是，如果我的当地时间是2022-04-26 10：00：00.000000+03（含义UTC时间为2022-04-26 07：00.00.00000000 +00）和e.validtill值是2022-04-26 08：00：00.000000+00，即使在结果中都不包含该实体，即使尽管e.validtill值是在UTC时间之后。在结果中包括有效till的实体。因此，我很确定问题是当前_timestamp正在转换。我该如何解决此问题，以使e.validtill＆gt; current_timestamp仅使用UTC格式值？
我也知道我可以将instant.now（）值作为要使用的参数而不是Current_timestamp，但我真的很想避免使用。

一天后编辑
我还没有找到解决方案，但是我觉得我已经接近了，并认为我学到的东西可能会帮助某人。
问题绝对是使用Current_Timestamp。我确保在我的Spring Boot Project Application.yml文件中包含spring.jpa.hibernate.jdbc.time_zone：utc。此外，我在我的版本控件中添加了脚本，因此当创建数据库时，DB服务器使用UTC TimeZone：

ALTER DATABASE myDatabase SET TIME ZONE TO 'UTC'

我确定这些更改会解决我的问题，但我错了。如果我使用show timezone对数据库的时区进行查询，则获得响应utc。但是，如果我通过我的API进行相同的查询，我将获得本地时区作为回应。因此，我怀疑JDBC是这里的罪魁祸首，而且我无法找到解决方案。

原文

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.

分享到QQ

分享到微博