Custom LocalDateTime parser in Slick 3

Custom LocalDateTime parser in Slick 3

Problem Description:

I’m converting a bunch of java.sql.Timestamp columns from my Slick 3 models into LocalDateTime. My database backend is MySQL 8 and the columns I’m converting are either TIMESTAMP or DATETIME.

I ran into issues with MySQL returning dates in format yyyy-MM-dd HH:mm:ss, while LocalDateTime.parse expects yyyy-MM-dd'T'HH:mm:ss. This results in runtime errors such as java.time.format.DateTimeParseException: Text '2022-12-05 08:01:08' could not be parsed at index 10.

It found that it could be solved by using a custom formatter, like this:

private val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
val localDateTimeMapper: BaseColumnType[LocalDateTime] = MappedJdbcType.base[LocalDateTime, String](
    ldt => ldt.format(formatter),
    s => LocalDateTime.parse(s, formatter)

Normally I would define the formatter as implicit, but it creates a compile error in the model: No implicits found for parameter tt: TypedType[LocalDateTime]. Applying the formatter explicitly works wonderful for column[LocalDateTime], but does not work for column[Option[LocalDateTime]] (causes Type mismatch, required TypedType[Option[LocalDateTime]]).

class Users(tag: Tag) extends Table[User](tag, "users") {
  def uuid           = column[UUID]("uuid", O.PrimaryKey)
  def name           = column[String]("name")
  def email          = column[String]("email")
  def lastSignedInAt = column[Option[LocalDateTime]]("last_signed_in_at")(localDateTimeMapper)
  def createdAt      = column[LocalDateTime]("created_at")(localDateTimeMapper)

  override def * = (uuid, name, email, lastSignedInAt, createdAt) <> (User.tupled, User.unapply)

Other custom types (such as enums) works without issues using the implicit formatter approach, but I suspect the issue here is that Slick has a LocalDateTime-mapper that I’m trying to override. From what I can tell Slick wants LocalDateTime objects to be stored as VARCHAR rather than date types, but I don’t want to convert the database columns.

Any advise on how I can make my custom formatter work (or use built in functionality in Slick) to allow LocalDateTime to work with MySQL’s date types?

Solution – 1

I eventually found a way that works by extending Slick’s MySQLProfile:

package lib

import slick.jdbc.JdbcProfile

import java.sql.PreparedStatement
import java.sql.ResultSet
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter

trait ExMySQLProfile extends JdbcProfile with slick.jdbc.MySQLProfile { driver =>
  private val localDateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")

  override val columnTypes = new ExJdbcTypes

  class ExJdbcTypes extends super.JdbcTypes {

    private[this] def stringToMySqlString(value: String): String = {
      value match {
        case null => "NULL"
        case _ =>
          val sb = new StringBuilder
          for (c <- value) c match {
            case ''' => sb.append("\'")
            case '"'  => sb.append("\"")
            case 0    => sb.append("\0")
            case 26   => sb.append("\Z")
            case 'b' => sb.append("\b")
            case 'n' => sb.append("\n")
            case 'r' => sb.append("\r")
            case 't' => sb.append("\t")
            case '\' => sb.append("\\")
            case _    => sb.append(c)

      * Override LocalDateTime handler, to parse values as we expect them.
      * The default implementation in Slick does not support TIMESTAMP or DATETIME
      * columns, but expects timestamps to be stored as VARCHAR
    override val localDateTimeType: LocalDateTimeJdbcType = new LocalDateTimeJdbcType {
      override def sqlType: Int = java.sql.Types.TIMESTAMP
      override def setValue(v: LocalDateTime, p: PreparedStatement, idx: Int): Unit = {
        p.setString(idx, if (v == null) null else v.toString)
      override def getValue(r: ResultSet, idx: Int): LocalDateTime = {
        r.getString(idx) match {
          case null          => null
          case iso8601String => LocalDateTime.parse(iso8601String, localDateTimeFormatter)
      override def updateValue(v: LocalDateTime, r: ResultSet, idx: Int) = {
        r.updateString(idx, if (v == null) null else v.format(localDateTimeFormatter))
      override def valueToSQLLiteral(value: LocalDateTime): String = {


trait MySQLProfile extends ExMySQLProfile {}

object MySQLProfile extends MySQLProfile

In my application.conf I’ve configured the profile with:

slick.dbs.default {
  profile = "lib.MySQLProfile$"
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.