How to query JPA LocalDateTime field with a LocalDate value?

Question

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 :

1. To get all the objects and filter in Java (but I wouldn't want to go there, so no)

2. 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).

3. 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 ?

Caprifoliaceous

LocalDate and LocalDateTime seem to be the wrong data types since they do not denote a point in time. Consider Instant instead. – Raimes

Answer 1

Using an answer that was quickly deleted after it was posted (I had to use the column and not the property name, mark the query as nativeQuery and also select with '.*' to avoid org.postgresql.util.PSQLException: The column name id was not found in this ResultSet error), I found this solution:

@Query(value = "SELECT e.* FROM Entity e WHERE DATE(creation_date) =:date", nativeQuery = true)
List<Entity> findByCreationDate(LocalDate date);

Alternatively, following this answer, I tested with success a second solution:

default List<Entity> findByCreationDate(LocalDate localDate) {
    return findByPublicationDateBetween(localDate.atStartOfDay(), localDate.plusDays(1).atStartOfDay());
}


List<Entity> findByCreationDateBetween(LocalDateTime from, LocalDateTime to);

Caprifoliaceous

I was actually testing the query before posting it again. – Lorinalorinda

Answer 2

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.

---

Kowloon

always enlightening! – Abdel

Answer 3

Your current solution is fine. But in @Query you can cast column using DATE()

@Query(value = "SELECT * FROM Entity u WHERE DATE(creation_date) = ?1", nativeQuery = true)
List<Entity> findByCreationDate(LocalDate date);

Lorinalorinda

Note that using a function on a column possibly makes it impossible for the database to use any index that might exist on the column. Therefore a between query might be more efficient. – Raimes

@JensSchauder I totally agree with you, between query actually better, I don't know is casting cost more or not. – Lorinalorinda