Scala Slick: Cusom mapper type DateTime to datetime

0
votes

I'm trying to store my DateTime objects like DATETIME types into database.So far I couldn't see any example like this and instead I saw a few doing from DateTime to Timestamp or DATE in database.

Someone out there facing the same problem?

My code (not working right now) looks like this:

import slick.jdbc.MySQLProfile.api._
import com.github.nscala_time.time.Imports.DateTime

object CustomMappers {
   implicit def dateTimeMapper = MappedColumnType.base[DateTime, DateTime](
        tmap = { dateTimeObject => new DateTime(dateTimeObject.getMillis)},
        tcomap = { dateTimeDb => new DateTime(dateTimeDb) }
  )
}
2
scalaslick
I can't really find a special mapping in Slick for DATETIME. Did you try with Timestamp class, i.e. converting joda.DateTime to Timestamp? What is in db table then? – Alexey Novakov
Yeah, I couldn't see a DateTime type as you said, which is a bit surprising..... – Francisco Albert

2 Answers

1
votes

To have full control over how this data is stored you can:

  1. Specify the database column type when you define the column. Slick makes sensible choices here automatically, but you can use a column option of O.SqlType to override it with a (possibly vendor-specific) type.
  2. Then provide a customing mapping from your type into a value that makes sense for the database column type you want to use.

The first part is in your table definition:

class MyTable(tag: Tag) extends Table[MyClass](tag, "table_name") {
   def when = column[MyDateTime]("my_date", O.SqlType("DATETIME")
   // another example:
   def avatar = column[Option[Array[Byte]]]("avatar", O.SqlType("BINARY(2048)"))
}

If you are using Slick to create the schema, the my_date column will be given the type DATETIME.

For the second part, you provide a mappling like the one in your question. It should be to/from the data type you want to use (DateTime) to a type that makes sense for the DATETIME column. For example, the MySQL documentation indicates the format is: YYYY-MM-DD HH:MM:SS. So what your mapping function should be able to accept and produce String types of that format.

As an alternative...

Since Slick 3.3.0, there's built-in support for the java.time data types. There's not a DateTime in Java, but possibly the Java LocalDateTime or ZonedDateTime might suit your needs. In this case, there's no need for a mapping or an O.SqlType.

1
votes

With slick 3.3.0, I found another workaround. (IT should work for other versions also)

def cusDateTimeCol = column[LocalDateTime]("cusDateTimeCol", O.SqlType("datetime"))(localDateTimeMapper)

with the following in scope. (use trait and import it in table definition)

  val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
  implicit def LocalDateTimeToStr(d: LocalDateTime): String = {
    d.format(formatter)
  }

  implicit def strToLocalDateTime(s: String): LocalDateTime = {
    LocalDateTime.parse(s, formatter)
  }

  implicit val localDateTimeMapper = MappedColumnType.base[LocalDateTime, String](
    LocalDateTimeToStr(_),
    strToLocalDateTime(_)
  )

With this setup, code will read/write datetimes with the format YYYY-mm-dd HH:mm:ss instead of usual ISO format.