Comparing java.sql.Timestamp and LocalDate using slick?

Question

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?

Nagarjuna Pamu · Accepted Answer · 2017-10-09 12:08:31Z

4

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.

edited Oct 9, 2017 at 12:08

answered Oct 9, 2017 at 11:57

Nagarjuna Pamu

14.8k3 gold badges24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rory Over a year ago

For the 'Not recommended way', I want to ignore the times, is this possible? (I only want to compare the dates)

Rory Over a year ago

Thanks, what about _.modifiedDate - do we need to change this to atStartOfDay as well (otherwise the times might be different?). Although not sure I can modify it via the filter function

Nagarjuna Pamu Over a year ago

@Rory Added more to the answer. please check

Collectives™ on Stack Overflow

Comparing java.sql.Timestamp and LocalDate using slick?

1 Answer 1

Not recommended way

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

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Not recommended way

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

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related

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