org.postgresql.util.PSQLException: conversion to class java.time.LocalDateTime from 93 not supported

[jurgispods]

Hi. First of all thank you for providing this great library which helped me a lot so far, especially when dealing with Postgres JSONB columns.

I recently added a LocalDateTime column to my table and I was able to write to the table without any problems. However, when reading back the table from another application - using the same profile and table definition - I get the following weird error:

org.postgresql.util.PSQLException: conversion to class java.time.LocalDateTime from 93 not supported

This is my table definition:

class ManualRecoTable(tag: Tag) extends Table[RecoKeyValue](tag, config.sourceTable) {
    def id = column[String]("id", O.PrimaryKey)
    def reco_values = column[Recommendation]("reco_values")
    def version = column[String]("version")
    def created = column[LocalDateTime]("created")

    def recoCreatedIndex: Index = index("reco_created", created)

    def * = (id, reco_values, version, created) <> ((RecoKeyValue.apply _).tupled, RecoKeyValue.unapply)
  }
  val manualRecoTable = lifted.TableQuery[ManualRecoTable]

These are my mapped case classes:

case class RecommendationEntry(recommendation: String, score: Float)

object RecommendationEntry {
  implicit val jsonFormat = Json.format[RecommendationEntry]
  implicit val jsonWrites = Json.writes[RecommendationEntry]
}

case class Recommendation(`type`: String, model: String, company: String, country: String,
                          sourceVariant: String, recommendations: List[RecommendationEntry]) {
  def id: String = {
    s"${company}-${country}-${sourceVariant}"
  }
}

object Recommendation {
  implicit val jsonFormat = Json.format[Recommendation]
  implicit val jsonWrites = Json.writes[Recommendation]
}

case class RecoKeyValue(id: String, reco_values: Recommendation, version: String, created: LocalDateTime)

And this is my Postgres profile:

trait PostgresProfile extends ExPostgresProfile
  with PgPlayJsonSupport
  with PgDateSupport
  with PgDate2Support {

  override protected def computeCapabilities: Set[Capability] =
    super.computeCapabilities + JdbcCapabilities.insertOrUpdate

  override val pgjson = "jsonb"

  override val api = new MyAPI{}

  trait MyAPI extends super.API
    with JsonImplicits
    //with SimpleDateTimeImplicits
    with DateTimeImplicits
  {
    implicit val beanJsonTypeMapper = MappedJdbcType.base[Recommendation, JsValue](Json.toJson(_), _.as[Recommendation])
  }

  val plainAPI = new API
    with Date2DateTimePlainImplicits {}
}

object PostgresProfile extends PostgresProfile

I am using slick-pg version 0.16.3 together with PostgreSQL version 10.4.

Any idea what is going wrong here?

[jurgispods]

The issue seems to be here. There is no case for LocalDateTime, only for OffsetDateTime, Instant and Duration.

If I modify my table definition to use a columns type of Instant instead of LocalDateTime, it works as expected. Is this a bug or am I missing some implicit conversions that need to be included?

[tminglei]

Your profile merged both PgDateSupport and PgDate2Support, so you need use specific *Implicit names. DateTimeImplicits is an alias for both Date2DateTimeImplicitsDuration and SimpleDateTimeImplicits.

[jurgispods]

I'm sorry, but I don't get what I have to change in my code. Can you tell me the specific traits I need to extend

• in PostgresProfile
• in MyAPI

in order to be able to read values as instances of LocalDateTime?

[tminglei]

...
override val api = new MyAPI{}

  trait MyAPI extends super.API
    with JsonImplicits
    //with SimpleDateTimeImplicits
    with DateTimeImplicits
  {
...

Here, you should use SimpleDateTimeImplicits and/or Date2DateTimeImplicitsDuration instead of DateTimeImplicits.