Scala Slick 3.0 implicit mapping between java8 OffsetDateTime and Timestamp

Question

First is if I want to map datetime with timezone to Slick, which class should I use OffsetDateTime or ZonedDateTime? As for Joda, we can only use DateTime.

How I can write some implicit to convert between java8 ZonedDateTime and Sql Timestamp for Slick table mapping?

It seems quite straightforward to use joda DateTime to include timezone information. However once switch to Java8, not quite sure whether I should use ZonedDateTime or OffsetDateTime, as http://www.oracle.com/technetwork/articles/java/jf14-date-time-2125367.html suggests to use OffsetDateTime.

For my current code, I just use Java8 LocalDateTime and I write following implicit to map between slick.

implicit val JavaLocalDateTimeMapper = MappedColumnType.base[LocalDateTime, Timestamp](
    l => Timestamp.valueOf(l),
    t => t.toLocalDateTime
  )

Not quite sure I can write similar using either ZonedDateTime or OffsetDateTime?

Answer 1

Short Answer

As of Slick 3.1, the short answer is to use OffsetDateTime, but you'll need to map it to a String column, not a Timestamp for it to work with any database.

That is, you'll need a MappedColumnType.base[OffsetDateTime, String]. You can use toString and OffsetDateTime.parse for the string conversion:

scala> import java.time._
import java.time._

scala> val paris = ZoneId.of("Europe/Paris")
paris: java.time.ZoneId = Europe/Paris

scala> OffsetDateTime.now(paris)
res0: java.time.OffsetDateTime = 2016-01-05T20:38:46.473+01:00

scala> OffsetDateTime.parse(res0.toString)
res2: java.time.OffsetDateTime = 2016-01-05T20:38:46.473+01:00

Longer Answer

The difference between OffsetDateTime and ZonedDateTime is covered by the answer to What's the difference between java 8 ZonedDateTime and OffsetDateTime?, so I won't repeat it here.

However, you'll want to read that to decide if the short answer matches up to your situation.

If you use Postgres, there's support for java.time via the slick-pg project. I've not had the chance to use it myself, but clearly worth investigating if that's the database you're using. See e.g., the test suite for the "date2" add-on.

The Better Answer (or, Future Answer!)

The wonderful news is that there's an active pull request to add support for the java.time data types in Slick. You can monitor the progress on the ticket, which is currently scheduled for Slick 3.2.