Querying mongo with rmongodb by timestamp

Question

I'm trying to query a mongodb based on timestamp, but I always seem to get an empty list back. I must be formatting something wrong. For the mongo document below:

{
"_id" : ObjectId("21def16043fe370208f857c2"),
"username" : "user",
"domain" : "domain.com",
"hash" : "65fdacac5ff9e",
"created" : ISODate("2012-11-20T16:08:28Z"),
"ip" : "160.85.11.222",
"actions" :
...
}

I want to filter documents by the created field. However, when I try use :

mongo <- mongo.create()
buf <- mongo.bson.buffer.create()
mongo.bson.buffer.start.object(buf, "created")
mongo.bson.buffer.append(buf, "$gt", "2013-01-01")
mongo.bson.buffer.finish.object(buf)
mongo.bson.buffer.append(buf, "$lt", "2013-08-01")
mongo.bson.buffer.finish.object(buf)
query <- mongo.bson.from.buffer(buf)
cursor <- mongo.find(mongo, "domain.actions", query)  

I get a empty list. When I just use $gt or $lt it doesn't work either. Is the date formatted wrong? Or is the query wrong?

Answer 1

You'll need to convert your date strings to proper dates so that MongoDB can do the comparison.

Try

mongo <- mongo.create()
buf <- mongo.bson.buffer.create()
startDate <- as.Date("2013-01-01")
endDate <- as.Date("2013-08-01")
mongo.bson.buffer.start.object(buf, "created")
mongo.bson.buffer.append(buf, "$gt", startDate)
mongo.bson.buffer.append(buf, "$lt", endDate)
mongo.bson.buffer.finish.object(buf)
query <- mongo.bson.from.buffer(buf)
cursor <- mongo.find(mongo, "domain.actions", query)  

Note that the date strings are in R's default format. If you change them you'll have to provide a format string to as.Date.

I'm not super familiar with the R MongoDB driver but you might also need to remove the first finish.object. Since you want the gt & lt to both apply to "created" then I think you want to have it as above.

Answer 2

You may use mongo.bson.buffer.append.time with strptime("2013-01-01","%Y-%m-%d") instead of "mongo.bson.buffer.append(buf, "$gt", startDate)" or "mongo.bson.buffer.append(buf, "$gt", as.Date("2013-01-01"))"

mongo <- mongo.create()
buf <- mongo.bson.buffer.create()
mongo.bson.buffer.start.object(buf, "created")
mongo.bson.buffer.append.time(buf, "$gt", strptime("2013-01-01","%Y-%m-%d"))
mongo.bson.buffer.finish.object(buf)
mongo.bson.buffer.append.time(buf, "$lt", strptime("2013-08-01","%Y-%m-%d"))
mongo.bson.buffer.finish.object(buf)
query <- mongo.bson.from.buffer(buf)
cursor <- mongo.find(mongo, "domain.actions", query)  

Answer 3

Looks like rmongodb has had an update on this issue. From the rmongodb website:

To build bson with ISODate data you should pass it as POSIXct object:

date_string <- "2014-10-11 12:01:06"
# Pay attention to timezone argument
query <- mongo.bson.from.list(list(date = as.POSIXct(date_string, tz='GMT')))
# Note, that internally MongoDB strores dates in unixtime format:
query

I changed the originally quoted MSK timezone to GMT to make it work on my data.