如何使用 LocalDate 值查询 JPA LocalDateTime 字段? [英] How to query JPA LocalDateTime field with a LocalDate value?
问题描述
我寻找一种方法来获取在 Postgresql 数据库中 TIMESTAMPTZ 类型的字段中保存的某个 LocalDateTime 日期上创建的对象列表.
为此，我尝试使用 JpaRepository:
List但我收到错误:
java.lang.IllegalArgumentException:参数值 [2020-12-12] 与预期类型 [java.time.LocalDateTime (n/a)] 不匹配我也尝试用相同的结果自己编写查询.
我目前想到的解决方案:
在 Java 中获取所有对象和过滤器(但我不想去那里，所以没有)
在 LocalDateTime 中转换 LocalDate 参数(但我假设在这种情况下我只会看到在同一时间创建的对象，所以不会.只有当我确定时间字段时，我才会考虑这个选项是午夜).
创建一个方法
findByCreationDateBetween(LocalDateTime startOfTheDay, LocalDateTime endOfTheDay)(有趣的选项，但我想在不修改 LocalDate 的情况下进行查询并将其转换为 LocalDateTime 在开始和在一天结束时.)
我还试图找到一些能够在查询中转换"LocalDate 中的 LocalDateTime 或比较 LocalDate 和 LocalDateTime 的年、月和日的函数，但没有成功.creationDate 的类型应保持 LocalDateTime 和 TIMESTAMPTZ 类型的数据库字段.
还有其他 Jpa @Query 替代方案吗?总的来说，这种情况下最好的方法是什么?
我不使用 JPA、Hibernate 或 Spring，所以我必须忽略您问题的那个方面.我使用的是直接的 JDBC.
一天的时间范围需要一个时区
您似乎忽略了时区的关键问题.对于任何给定时刻，日期在全球各地因时区而异.在某个时刻，在日本东京可能是明天"，而在美国俄亥俄州托莱多仍然是昨天".
因此，当您指定从一天开始到第二天开始的日期范围时，您必须考虑一个时区.
将分区时间转换为 UTC 搜索条件
你说:
<块引用>TIMESTAMPTZ 类型字段中的 Postgresql 数据库
类型名称 TIMESTAMPTZ
I search for a way to get a list of Objects created on a certain LocalDateTime date saved in the Postgresql database in a field of type TIMESTAMPTZ.
To do so, I tried to use JpaRepository:
List<Object> findByCreationDate(LocalDate date);
But I get the error:
java.lang.IllegalArgumentException: Parameter value [2020-12-12] did not match expected type [java.time.LocalDateTime (n/a)]
I also tried writing the query myself with the same result.
The solutions I thought about so far :
To get all the objects and filter in Java (but I wouldn't want to go there, so no)
To convert the LocalDate parameter in LocalDateTime (but I assume in this case I will see only Objects created at the same exact time, so no. I would consider this option only when I'm sure the fields of time are midnight).
To create a method
findByCreationDateBetween(LocalDateTime startOfTheDay, LocalDateTime endOfTheDay)(interesting option, but I would like to do the query without modifying the LocalDate and converting it to LocalDateTime at the start and at the end of the day.)
I also tried to find some functions that would be able to 'cast' the LocalDateTime in LocalDate in the query or compare the year, the month and the day of the LocalDate and LocalDateTime, but unsuccessfully. The type of creationDate should remain LocalDateTime and the database field of type TIMESTAMPTZ.
Are there any other Jpa @Query alternatives and overall, what would be the best approach for this case ?
I do not use JPA, Hibernate, or Spring, so I must ignore that aspect of your Question. I am using straight JDBC instead.
A day’s range of moments requires a time zone
You seem to be ignoring the crucial issue of time zone. For any given moment, the date varies around the globe by time zone. At some moment, it may be "tomorrow" in Tokyo Japan while still "yesterday" in Toledo Ohio US.
So you must have a time zone in mind when you specify the range of a date from start of day to start of the following day.
Convert from zoned time to UTC for search criteria
You said:
Postgresql database in a field of type TIMESTAMPTZ
The type name TIMESTAMPTZ in Postgres is a non-standard abbreviation for the standard type name TIMESTAMP WITH TIME ZONE.
Postgres stores all submitted data in a column of that type in UTC, with an offset of zero hours-minutes-seconds from UTC. Any offset or time zone accompanying submitted inputs is used to first adjust the value to UTC for storage. After storage, any submitted zone/offset info is discarded. Postgres stores all TIMESTAMP WITH TIME ZONE values in UTC.
So if you want to query for values that lay within the duration of a day, you must first define a day in your desired time zone, then adjust the start and end of that day into UTC values.
String input = "2020-12-12" ;
LocalDate ld = LocalDate.parse( input ) ;
Determine the first moment as seen in the time zone of your choice. Always let java.time determine first moment of the day, never assume a day starts at 00:00:00. Some days on some dates in some zones start at other times such as 01:00:00.
Usually best in date-time handling to define a span-of-time using Half-Open approach. The beginning is inclusive, while the ending is exclusive. This allows spans to neatly abut one another.
So your SQL query does not use BETWEEN. Your SQL will look something like the following. Note that a shorter way to say "is equal to or later than" is "is not before" (!< in SQL).
SELECT *
FROM event_
WHERE when_ !< ?
AND when_ < ?
;
Your Java looks like this.
ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
ZonedDateTime zdtStart = ld.atStartOfDay( z ) ;
ZonedDateTime zdtEnd = ld.plusDays( 1 ).atStartOfDay( z ) ;
2020-12-12T00:00+13:00[Pacific/Auckland]/2020-12-13T00:00+13:00[Pacific/Auckland]
Adjust to UTC by extracting Instant objects. Instant objects are always in UTC, by definition.
Instant start = zdtStart.toInstant() ;
Instant end = zdtEnd.toInstant() ;
2020-12-11T11:00:00Z/2020-12-12T11:00:00Z
See this code run live at IdeOne.com.
And your JDBC looks like this:
myPreparedStatement.setObject( 1 , start ) ;
myPreparedStatement.setObject( 2 , end ) ;
In that JDBC code, we are passing Instant objects. That may or may not work, depending on your JDBC driver. Oddly, the JDBC 4.2 spec requires support for OffsetDateTime but not the more commonly used Instant or ZonedDateTime types. No matter, we can easily convert.
myPreparedStatement.setObject( 1 , start.atOffset( ZoneOffset.UTC ) ) ;
myPreparedStatement.setObject( 2 , end.atOffset( ZoneOffset.UTC ) ) ;
And we could have gone from ZonedDateTime to OffsetDateTime by calling ZonedDateTime::toOffsetDateTime. But then the offset of those objects would not be UTC (zero). Your JDBC driver and database should be able to handle that. But out of an abundance of caution, and for the sake of debugging and logging, I would use UTC as seen above. But FYI, the code:
myPreparedStatement.setObject( 1 , zdtStart.toOffsetDateTime() ) ;
myPreparedStatement.setObject( 2 , zdtEnd.toOffsetDateTime() ) ;
Never use LocalDateTime for moments
Never use LocalDateTime when dealing with moments, with specific points on the timeline. Lacking the context of a zone/offset, a LocalDateTime cannot represent a moment.
这篇关于如何使用 LocalDate 值查询 JPA LocalDateTime 字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
