Comparing java.sql.Timestamp and LocalDate using slick?

1
votes

I have a slick column that is a Rep[Option[Timestamp]]. I'd like to compare it to a user supplied java.util.LocalDate via the filter method. I'm only interested in comparing the date (ddMMyyyy), not the time: 

val userSuppliedLocalDate: LocalDate = ...
usersTable.filter(_.modifiedDate === userSuppliedLocalDate).result // Doesn't compile

I thought about converting the Rep[Timestamp] (_.modifiedDate) to a LocalDate, but unsure if this is possible (unwrapping a Rep type?)

Or, converting the userSuppliedLocalDate to a java.sql.Timestamp and doing a comparison ignoring the time portions - is this possible?

1
scalaslickslick-3.0

1 Answers

3
votes

Not recommended way

 Warning: Take care of time zone information

You can convert LocalDate into timestamp and compare the timestamp.

val timestamp = Timestamp.valueOf(localDate.atStartOfDay)

Your method becomes

val userSuppliedLocalDate: LocalDate = ...
val userSuppliedTimestamp = Timestamp.valueOf(localDate.atStartOfDay)
usersTable.filter(_.modifiedDate === userSuppliedTimestamp).result

But, this is not the recommended way.

If you want to compare timestamp of particular day. One Hack would be 

val start = Timestamp.valueOf(localDate.atStartOfDay())
val end = Timestamp.valueOf(start.plusDays(1))

usersTable.filter(row => row.modifiedDate >= start && row.modifiedDate < end).result

Recommended way is to use LocalDate itself as slick table column.

How is this possible? 

This possible with slick column mapper.

Have below implicit in the scope.

implicit val mapper = MappedColumnType.base[LocalDate, Timestamp](
 _.toLocalDateTime().toLocalDate(),
 Timestamp.valueOf)

Now you can have a LocalDate column directly

def birthday = column[LocalDate]("birthday")

Now you can directly compare

def birthdaysBefore(localDate: LocalDate) = 
  query.filter(_.birthday < localDate).result

You can use other comparison operators as well.