Same type (java.sql.Timestamp) turns into different SQL-types

[olereidar]

Hello, I am not sure where this happens, but I am having some trouble with ZonedDateTime.

I have read in the documentation that ZonedDateTime is not supported (https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.entity-persistence.types), therefore I wrote a converter (ZonedDateTime -> java.sql.Timestamp).

I have the following data class:

data class OrderLineEvent(
  (...) other fields 
  val orderLineEventType: String? = null,
  val travelDate: ZonedDateTime? = null, 
  val timestamp:  LocalDateTime = LocalDateTime.now() // Actually a ZonedDateTime, but using LocalDateTime for illustration purposes 
) {
}

Converter:

import java.sql.Timestamp
import java.time.ZoneOffset
import java.time.ZonedDateTime

@Configuration
@EnableJdbcRepositories
class DataJdbcConfiguration : AbstractJdbcConfiguration() {

  @WritingConverter
  class ZonedDateTimeWriterConverter : Converter<ZonedDateTime, Timestamp> {

    override fun convert(date: ZonedDateTime): Timestamp {
      return Timestamp.valueOf(date.withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime())
    }

  }

  @Bean
  override fun jdbcCustomConversions(): JdbcCustomConversions {
    return JdbcCustomConversions(listOf(ZonedDateTimeWriterConverter()))
  }
}

Repository:

@Repository
interface OrderLineEventRepository : CrudRepository<OrderLineEvent, Long>

---

Ok, here is the issue: I am unable to save this as a timestamp. The error is Translating SQLException with SQL state '42804', error code '0', message [ERROR: column "travel_date" is of type timestamp without time zone but expression is of type character varying which is quite odd.

After investigating this further (and enabling TRACE-logging), it seems like the parameter gets converted into a VARCHAR/string type(?) AND a different type than the `LocalDateTime

Look at logs below:

o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 11, parameter value [123.42], value class [java.lang.String], SQL type 12  
o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 12, parameter value [2021-11-17 08:31:28.731541], value class [java.sql.Timestamp], SQL type 93  
o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 13, parameter value [2021-11-17 08:31:28.73009], value class [java.sql.Timestamp], SQL type 12  

It seems like String and ZonedDateTime turns into the same SQL type.

String -> java.lang.String, SQL type 12
LocalDateTime -> java.sql.Timestamp, SQL type 93
ZonedDateTime -> java.sql.Timestamp, SQL type 12

[olereidar]

Same thing happens with a LocalDateTime converter. It gets the SQL type 12 (same as java.lang.String)

    override fun convert(date: ZonedDateTime): LocalDateTime {
      return date.withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime()
    }

Logs:

o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 11, parameter value [123.42], value class [java.lang.String], SQL type 12  
o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 12, parameter value [2021-11-17 10:51:11.019172], value class [java.sql.Timestamp], SQL type 93  
o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 13, parameter value [2021-11-17T09:51:11.018157], value class [java.time.LocalDateTime], SQL type 12 

[schauder]

Please provide a Minimimal Reproducable Example, preferable as a Github repository. Make sure to include the database, either as an in memory database or if that is not possible using Testcontainers.

[olereidar]

Here you go: https://github.com/olereidar/spring-jdbc-help

I used Testcontainers. Run the test via gradle

[schauder]

We do make a mistake when determining the JdbcType to pass with the Timestamp.

You can work around that by returning a JdbcValue which already contains the JdbcType:

  @WritingConverter
  class ZonedDateTimeWriterConverter : Converter<ZonedDateTime, JdbcValue> {

    override fun convert(date: ZonedDateTime): JdbcValue {
      val valueOf = Timestamp.valueOf(date.withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime())
      return JdbcValue.of(valueOf, JDBCType.TIMESTAMP)

    }
  }

We should try to determine the JdbcType only after the conversion.

[olereidar]

Thank you, this works. Do you have any plans on fixing this, or do you consider this workaround a permanent solution?

EDIT: It only works partly. If the value is nullable (which it is in this case) it crashes when inserting null. Same error: is of type timestamp without time zone but expression is of type character varying

o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [null], value class [null], SQL type 12 

Any ideas on workaround here?

[schauder]

I don't consider this workaround a permanent solution. And I understand where the problem with null comes from, basically null gets a shortcut and doesn't use conversions, but apart from avoiding nulls I don't have a workaround at hand.

[olereidar]

Ok, thank you anyways! Let me know if you find a more permanent solution. Have a nice day.

[schauder]

Somewhat related SO question https://stackoverflow.com/questions/62004522/convert-null-to-some-value-in-spring-converter

[huima]

Just stumbled upon the same problem as described by @olereidar .

Did you eventually change the design and use a different datatype in the entity?

[huima]

We are trying to now use OffsetDateTime in the entity, as the jdbc drivers seem to work properly with them and Spring conversion code is not needed then.

[olereidar]

@huima unfortunately no. We ended up using another library. Awesome if its fixed :D