JPA projection cannot project OffsetDateTime from timestampz

[cjacky475]

JPA projection cannot project OffsetDateTime from timestampz

[mp911de]

If you would like us to spend some time helping you to diagnose the problem, please spend some time describing it and, ideally, providing a minimal yet complete sample that reproduces the problem.
You can share it with us by pushing it to a separate repository on GitHub or by zipping it up and attaching it to this issue.

[cjacky475]

Sure, I will provide more details:

in my PostgreSQL database I store posts. One of the columns is created_at which is of type timestamptz. For this in Spring Boot I have entity class with projection class:

interface PostProjection {
    // Other functions

    @JsonProperty("createdAt")
    @JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSSSSX")
    fun getCreated_at(): OffsetDateTime
}

@Entity
@Table(name = "posts")
data class Post(
    // Other variables

    @NotBlank
    @JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSSSSX")
    val createdAt: OffsetDateTime = OffsetDateTime.now()
)

To get the data from the database I use repository interface:

@Repository
interface PostRepository : JpaRepository<Post, Long> {

   @Query(
        value = "SELECT p.* " +
                "FROM posts AS p " +
                "LEFT JOIN users AS u ON p.user_id = u.id " +
                "GROUP BY p.id ",
        countQuery = "SELECT COUNT(*) " +
                "FROM posts AS p " +
                "LEFT JOIN users AS u ON p.user_id = u.id " +
                "GROUP BY p.id ",
        nativeQuery = true
    )
    fun <T> findPosts(
        cursorBasedPageable: CursorBasedPageable<T>,
        pageable: Pageable
    ): Page<Post>

    @Query(
        value = "SELECT p.*, SUM(u.reporting_score) " +
                "FROM posts AS p " +
                "LEFT JOIN users AS u ON p.user_id = u.id " +
                "GROUP BY p.id ",
        countQuery = "SELECT COUNT(*) " +
                "FROM posts AS p " +
                "LEFT JOIN users AS u ON p.user_id = u.id " +
                "GROUP BY p.id ",
        nativeQuery = true
    )
    fun <T> findPostsWithReportingScore(
        cursorBasedPageable: CursorBasedPageable<T>,
        pageable: Pageable
    ): Page<PostProjection>

}

When I call findPosts() function I do receive all information, including created_at field which in database is stored as shown below (one of the examples):

The Spring JPA generates the following JSON response (one of the examples):
"createdAt": "2024-03-26T16:10:07.688571Z",, which is correct.

Now when I call findPostsWithReportingScore() function I receive a warning: Resolved [org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: Cannot project java.time.Instant to java.time.OffsetDateTime; Target type is not an interface and no matching Converter found].

[mp911de]

Returning Instant is a consequence of Hibernate's TimestampUtcAsJdbcTimestampJdbcType class that converts Timestamp values into Instant.
Spring Data uses tuple-queries to provide data for interface projections hence we have to rely on the types that Hibernate provides to us.

With the source value being Instant we do not have sufficient information to convert the value into OffsetDateTime.