One way to achieve it would be like this:
val now = DateTime.now
val today = now.toLocalDate
val tomorrow = today.plusDays(1)
val startOfToday = today.toDateTimeAtStartOfDay(now.getZone)
val startOfTomorrow = tomorrow.toDateTimeAtStartOfDay(now.getZone)
val todayLogItems = logItems.filter(logItem =>
logItem.MyDateTime >= startOfToday && logItem.MyDateTime < startOfTomorrow
).list
Is there any way to write the query in a more concise way? Something on the lines of:
logItems.filter(_.MyDateTime.toDate == DateTime.now.toDate).list
I'm asking this because in LINQ to NHibernate that is achievable (Fetching records by date with only day part comparison using nhibernate).
Unless the Slick joda mapper adds support for comparisons you are out of luck unless you add it yourself. For giving it a shot these may be helpful pointers:
* http://slick.typesafe.com/doc/2.0.0/userdefined.html
* http://slick.typesafe.com/doc/2.0.0/api/#scala.slick.lifted.ExtensionMethods
* https://github.com/slick/slick/blob/2.0.0/src/main/scala/scala/slick/lifted/ExtensionMethods.scala
I create a ticket to look into it in Slick at some point: https://github.com/slick/slick/issues/627
You're confusing matters by working with LocalDateTimes instead of using LocalDates directly:
val today = LocalDate.now
val todayLogItems = logItems.filter(_.MyDateTime.toLocalDate isEqual today)
UPDATE
A Major clarification is needed on the question here, Slick was only mentioned in passing, by way of a tag.
However... Slick is central to this question, which hinges on the fact that filter operation is actually into an SQL query by way of PlainColumnExtensionMethods
I'm not overly familiar with the library, but this must surely mean that you're restricted to just operations which can be executed in SQL. As this is a Column[DateTime] you must therefore compare it to another DateTime.
As for the LINQ example, it seems to recommend first fetching everything and then proceeding as per my example above (performing the comparison in Scala and not in SQL). This is an option, but I suspect you won't want the performance cost that it entails.
UPDATE 2 (just to clarify)
There is no answer.
There's no guarantee that your underlying database has the ability to do an equality check between dates and timestamps, slick therefore can't rely on such an ability existing.
You're stuck between a rock and a hard place. Either do the range check between timestamps as you already are, or pull everything from the query and filter it in Scala - with the heavy performance cost that this would likely involve.
FINAL UPDATE
To refer to the Linq/NHibernate question you referenced, here are a few quotes:
- You can also use the date function from Criteria, via SqlFunction
- It depends on the LINQ provider
- I'm not sure if NHibernate LINQ provider supports...
So the answers there seem to be either:
- Relying on NHibernate to push the date coercion logic into the DB, perhaps silently crippling performance (by fetching all records and filtering locally) if this is not possible
- Relying on you to write custom SQL logic
The best-case scenario is that NHibernate could translate date/timestamp comparisons into timestamp range checks. Doing something like that is quite a deep question about how Slick (and slick-joda-mapper) can handle comparisons, the fact that you'd use it in a filter is incidental.
You'd need an extremely compelling use-case to write a feature like this yourself, given the risk for creating complicated bugs. You'd be better off:
- splitting the column into separate date/time columns
- adding the date as a calculated column (maybe in a view)
- using custom SQL (or a stored proc) for the query
- sticking with the range check
- using a helper function
In the case of a helper:
def equalsDate(dt: LocalDate) = {
val start = dt.toDateTimeAtStartOfDay()
val end = dt.plusDays(1).toDateTimeAtStartOfDay()
(col: Column[DateTime]) => {
col >= start && col < end
}
}
val isToday = equalsDate(LocalDate.now)
val todayLogItems = logItems.filter(x => isToday(x.MyDateTime))
