finding mongodb entries by joda DateTime range scala/casbah

Question

I'm stumped on casbah find. I'm trying to pull back all documents from a MongoDB between date1 and date2. Here's an example set of mongo docs:

{ "_id" : NumberLong("1285248838000"), "openTime" : "Thu Sep 23 2010 06:33:58 GMT-0700 (PDT)", "closeTime" : "Thu Sep 23 2010 06:36:15 GMT-0700 (PDT)", "timeInTrade" : "00:02:17", "direction" : "Long", "size" : 1, "outcome" : "Loss" }
{ "_id" : NumberLong("1285595711000"), "openTime" : "Mon Sep 27 2010 06:55:11 GMT-0700 (PDT)", "closeTime" : "Mon Sep 27 2010 06:57:37 GMT-0700 (PDT)", "timeInTrade" : "00:02:26", "direction" : "Short", "size" : 1, "outcome" : "Win"}
{ "_id" : NumberLong("1285594773000"), "openTime" : "Mon Sep 27 2010 06:39:33 GMT-0700 (PDT)", "closeTime" : "Mon Sep 27 2010 06:41:47 GMT-0700 (PDT)", "timeInTrade" : "00:02:14", "direction" : "Short", "size" : 1, "outcome" : "Win" }
{ "_id" : NumberLong("1286289026000"), "openTime" : "Tue Oct 05 2010 07:30:26 GMT-0700 (PDT)", "closeTime" : "Tue Oct 05 2010 07:36:23 GMT-0700 (PDT)", "timeInTrade" : "00:05:57", "direction" : "Short", "size" : 2, "outcome" : "Loss"}

So, let's say I want to pull back the documents from Sep 27. How would I go about doing that?

In the casbah documentation, it looks like I could construct a builder like this:

val dt = new DateTime("2010-09-27T00:00:00.000-08:00")
val bldr = MongoDBObject.newBuilder
bldr += "openTime" $gte dt $lt dt.plusDays(1)
val result = coll.find(bldr.result)

In my IDE (Netbeans), this will not compile because "$gte is not a member of java.lang.String". I had similar results with the other documented ways to construct my filter.

I suspect that the next problem I would have is that it doesn't know how to compare the dates because they are stored as joda DateTimes, so if anyone has experience with these problems, I would greatly appreciate some guidance.

Thanks, John

FOLLOW-UP:

I've got a partial solution, but only because I was using the milliseconds as the _id. Here is some code that works for that case:

val begin = dt.getMillis
val end = dt.plusDays(1).getMillis
val json = "{ '_id' : { '$gte' : " + begin + " , '$lt' : " + end + "}}"
val dbObject = JSON.parse(json).asInstanceOf[DBObject];
for (x <- coll.find(dbObject)) println(x)

I'm still interested in learning about a solution that works on DateTime instead of the Long millis...

Answer 1

opentime is stored as a string on the Mongo side. Your $gte function won't work b/c string comparison won't work.

To make this work, you'll have to use a $where clause and a function that performs the comparison correctly. So you'll basically have to write a javascript function that correctly interprets the JODA time. You'll then have to include that function with your DB call or you'll have to store it server-side and proceed from there.

Here are some details on the where clause. Here are some details on server-side code execution.