4

I tried to write a query method in my repository similar to this

@Modifying
@Query("UPDATE foo SET some_timestamp = :someTimestamp WHERE id = :id")
void updateSomeTimestamp(@Param("id") long id, @Param("someTimestamp") Instant someTimestamp)

When executing the code I got the following error:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.time.Instant. Use setObject() with an explicit Types value to specify the type to use.

Did I forget something or is usage of Instant as parameter simply not supported by Spring Data Jdbc? If so, is such a support planned in the future?

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
stoetti
  • 93
  • 1
  • 5

2 Answers2

2

For those who came here looking for a solution to generate java.sql.Timestamp instances with better than millisecond precision (the Timestamp constructor that accepts nanos is deprecated):

Timestamp timestamp = Timestamp.from(Instant.now());

This assumes your operating system supports better than millisecond precision for the system clock. It's useful for databases that support better-than-millisecond TIMESTAMP precision, such as with PostgreSQL, which supports microsecond precision.

You can convert it back to an Instant or LocalDateTime:

System.out.println(timestamp.toInstant()); // UTC

or

System.out.println(timestamp.toLocalDateTime()); // local time

I will also reiterate information stoetti provided in a comment, that according to the documentation link that stoetti provided, https://jdbc.postgresql.org/documentation/head/8-date-time.html, the PostgreSQL JDBC driver does recognize the following java.time classes as timestamps:

java.time.LocalTime as TIME [ WITHOUT TIMEZONE ]

java.time.LocalDateTime as TIMESTAMP [ WITHOUT TIMEZONE ]

java.time.OffsetDateTime as TIMESTAMP WITH TIMEZONE

But not java.time.Instant, as stoetti pointed out.

acker9
  • 363
  • 2
  • 11
1

Spring Data JDBC has no knowledge of the type expected by the SQL-statement. Therefore it passes parameters a long without any conversion and relies on the database to properly handle the parameter.

One might argue that it should convert parameters if a matching converter is registered, but this is currently not the case.

Therefore currently the solution is to convert the argument yourself and changing the argument type to java.util.Date or whatever your JDBC driver is accepting.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • I understand your point but from my opinion java.time.* is a set of classes that should be supported out of the box. – stoetti Dec 19 '19 at 10:13
  • I kind of agree and would make that case against the JDBC driver. The question is: Should Spring Data JDBC try to convert parameters? – Jens Schauder Dec 19 '19 at 12:01
  • According to the documentation (at least for postgresql-drive which we use) some classes from java.time are supported, Instant being not included. => https://jdbc.postgresql.org/documentation/head/8-date-time.html In my opinion converting parameters from java.time.* to some jdbc-compatible type would be a nice feature I guess more users of Spring Data JDBC would benefit from. – stoetti Dec 19 '19 at 16:06
  • 1
    I created https://jira.spring.io/browse/DATAJDBC-464 for this. – Jens Schauder Dec 20 '19 at 05:58