spring-data-jdbc and postgres: Trailing junk on timestamp

[ghost]

Original question here: https://stackoverflow.com/q/66484578

Small gradle project with a junit test to reproduce the error: https://github.com/schachtelhalm/minimal-demo

With spring boot 2.3.8 this junit test doesn't fail, with 2.3.9 it fails with
java.lang.NumberFormatException: Trailing junk on timestamp: 'T21:26:13.318881+01:00'
Tested with openjdk 11.0.10.
Complete stacktrace on minimal-demo page.

The query method is in a Repository that extends CrudRepository:

    @Query("SELECT` * FROM animal WHERE updated_at > :lastUpdate")
    List<Animal> findByUpdatedAtGreaterThan(@Param("lastUpdate") OffsetDateTime lastUpdate);

The entity has a field private Timestamp updatedAt;

When I tried to debug the original application I set a breakpoint in org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(). With spring boot 2.3.8 I think I only hit the method for timestamps from the database rows, but not for the parameter of the query. With 2.3.9 the method parseBackendTimestamp() was also called for the parameter I'd used with curl requests for the RestController.

[ghost]

i have no idea what the problem is, if it is postgresql or spring-data related, or if there is something wrong in our application. And if so, why did it work until I upgraded spring boot? Any hints would be much appreciated.

[schauder]

I haven't found the exact reason yet of what is going wrong.
But everything does work if you use Instant instead of OffsetDateTime.

I'm always very sceptical when I see time and date like datatypes with timezone information used in persistent storage, since it seems to mingle two very different concepts: a point in time and a rough approximation of a location/cultural context.

Most of the time Instant is the correct type to use and timezone information if needed at all should come from a different source: The user observing the Instant, or the entity to which the Instant relates.

[jerrychen-afterpay]

Also ran into this issue today, but with SpringBoot 2.4.3. We also are running postgresql. The issue occurs when we run updates on a table of the form (other fields omitted):

@Table("table_name")
data class MyTable(
    @Id @Column("id") val id: UUID,
    @Column("created_at") val createdAt: OffsetDateTime = OffsetDateTime.now(),
    @Column("updated_at") val updatedAt: OffsetDateTime = OffsetDateTime.now()
)

The code works for a different table that doesn't have an updated_at field, i.e. it looks like the following (other fields omitted):

@Table("table_2_name")
data class MyOtherTable(
    @Id @Column("id") val id: UUID,
    @Column("updated_at") val createdAt: OffsetDateTime = OffsetDateTime.now()
)

[ghost]

@schauder The application (that I kind of inherited) uses UTC for all dates (and for timestamp with time zone, the internally stored value in Postgres is always in UTC). So we don't really store a time zone.
According to the Postgresql Driver docs, the Java type for TIMESTAMP WITH TIMEZONE is OffsetDateTime and they also mention: "Note that ZonedDateTime, Instant and [...] are not supported".
Is this relevant at all when using spring-data-jdbc?

[rattermeyer]

Also ran into the problem today. But I would have blamed the PostgreSQL driver for not corectly parsing the OffeseDateTime string representation. It does not expect the seperating 'T'. However PostgreSQL documentation suggests, it should work.
Haven't tried without spring-data-jdbc.

[schauder]

The problem seems to be that we are now setting the sqlType in the SqlParameterSource and use the wrong java.sql.Types value: 93 (TIMESTAMP) instead of 2014 (TIMESTAMP_WITH_TIMEZONE).

[schauder]

I just created a PR that hopefully fixes the issue. If anybody can give that a try, that would be helpful.

Note, that in it's current form it breaks code that inherits from AbstractJdbcConfiguration and overwrites customConversions(). That method got replaced by one with a Dialect argument.

[Kshitij09]

I'm still getting "Trailing Junk on timestamp: 'T07:02:27.894774Z'". I've even aligned OffsetDateTime to UTC using following snippet

var odtNow = OffsetDateTime.now().withOffsetSameInstant(ZoneOffset.UTC);

Are we supposed to use Instant? Postgres Documentation says OffsetDateTime is supported in JDBC 4.2

[schauder]

The problem is only in so far the JDBC driver that the JDBC driver of most databases just drops the timezone.

Do you use one of the 2.3 milestone releases of Spring Data JDBC?
If so an you still see this problem we'd need a new issue and a reproducer.