Gmongo date query

Question

Given a mongo collection (stripped down to only a few fields for illustrative purposes):

{
    "_id" : ObjectId("52402905e4b0cebf1e474093"),
    "impressionId" : LUUID("6a46028f-aa02-e87d-c097-01df765ec487"),
    "requestTime" : ISODate("2013-09-23T11:41:57.505Z"),
    "responseTime" : ISODate("2013-09-23T11:41:57.712Z"),
}

I want to query for the requestTime. Ideally I'd like two queries, one for the exact requestTime and one for a range of requestTimes (greater than or equal to some datetime and less than or equal to some other datetime). I have so far tried the following but none seem to work:

def fromDate = new Date(2013, 9, 22, 11, 41, 57)
def fromDateLong = fromDate.getTime()
def toDate = new Date(2013, 9, 23, 11, 41, 58);
def toDateLong = toDate.getTime()

// None of these return a record
def result = db.activityLog.find(requestTime : ['$date' : '2013-09-23T11:41:57.505Z']).count()
def result = db.activityLog.find(requestTime : ['$gte' : fromDateLong, '$lte' : toDateLong]).count()
def result = db.activityLog.find(requestTime : ["\\\$gte" : fromDateLong, "\\\$lte" : toDateLong]).count()
def result = db.activityLog.find(requestTime : ['$gte' : fromDate, '$lte' : toDate]).count()
def result = db.activityLog.find(requestTime : ["\\\$gte" : fromDate, "\\\$lte" : toDate]).count()

// I have also tried to read the first record form the mongo collection pull out the date (which works fine) and then query for this date (which doesn't work):

def result = db.activityLog.findOne()
println result.requestTime  // prints correct requestTime
def requestTimeLong = result.requestTime.getTime()
println requestTimeLong  // prints correct requestTime

def result2 = db.activityLog.find(requestTime : ["\\\$gte" : requestTimeLong]).count()
def result3 = db.activityLog.find(requestTime : ["\\\$date" : requestTimeLong]).count()
def result4 = db.activityLog.find(requestTime : ["\\\$gte" : result.requestTime]).count()
def result5 = db.activityLog.find(requestTime : ["\\\$date" : result.requestTime]).count()

println result2 // prints 0 for both above queries i.e. no records found
println result3 // prints 0 for both above queries i.e. no records found
println result4 // prints 0 for both above queries i.e. no records found
println result5 // prints 0 for both above queries i.e. no records found

Does anyone have any suggestions? Any help with this would be much appreciated.

I have made some progress but it's not working quite yet. I can retrieve a record pull out the date and use this date in a query which works. However if I try to create the date myself it does not work:

def result = db.activityLog.findOne(impressionId : ['$exists' : true])
def requestTimeResult = result.requestTime
println requestTimeResult
println requestTimeResult.class
def requestTimeLong = result.requestTime.getTime()
println requestTimeLong

def result2 = db.activityLog.find(requestTime: ['$gte': ['$date' : requestTimeLong]]).count()
def result3 = db.activityLog.find(requestTime: ['$gte': requestTimeResult]).count()

println result2
println result3

def fromDateNew = new Date(2013, 8, 23, 12, 41, 50)
println fromDateNew.class
println fromDateNew
def result4 = db.activityLog.find(requestTime: ['$gte': fromDateNew]).count()

println result4

outputs:

Mon Sep 23 12:41:50 BST 2013
class java.util.Date
1379936510544
0
196869
class java.util.Date
Sat Aug 23 12:41:50 BST 3913
0

It seems to be a type mismatch. Have you tried to just pass fromDate and toDate (instead of fromDateLong and toDateLong)? — Lukasz Wiktor, Mar 28 '14 at 11:30
@Lucasz Wiktor - Hi, thanks for your suggestion. Yes, I just tried fromDate and toDate and I get the same result - count is 0. I'll update the question to include this example. — Stuart, Mar 28 '14 at 11:40

score 2 · Answer 1 · answered Mar 28 '14 at 11:36

You pretty much "always" want to do a "range query" when using dates. Unless you absolutely know the actual date value, which kind of defeats the point. So even for a single value like this:

"requestTime" : ISODate("2013-09-23T11:41:57.505Z"),

You really want to define a "small" range, possibly like:

db.activityLog.find({
    "requestTime": { 
        "$gte": new Date("2013-09-23 11:41:57"), 
        "$lt": new Date("2013-09-23 11:41:58")
    }
})

But in actual content of what you seem to be trying to do (and sort of illustrated above) what you need to do is use you "native" language platform implementation of a "date" type when passing into a query.

Dates in MongoDB are stored in a BSON representation which is actually an "epoch" timestamp internally. So your "native" dates are converted by the "driver" into this representation. The same is true when "reading" results from the collection, so the dates that MongoDB stores are converted to your "native" language date format.

So use the "native" way rather than converting to strings. The ISODate object type is just a mongo "shell" way of representing these "objects".

@Neill_Lunn, Hi, thanks for your response. I am trying to do this now - see the addition to my question. I think I'm nearly there but it's confusing that the datetime I obtain from mongo will work but the one I create manually does not, even though they are of the same class. I wouldn't mind using the time since epoch and querying on this but this doesn't seem to work either. Note - I can do this query in mongo shell but I would like to also be able to do it programmatically in groovy using the gmongo driver. — Stuart, Mar 28 '14 at 12:19
@Stuart I did presume this was the class as mentioned in your edit. Considering it is "Groovy" it should be the basic java Date class ( scala people prefer Joda time ) That translation should be handled by the drver, see [here](http://docs.mongodb.org/ecosystem/drivers/java-types/#dates-times). So maybe include some of the code you are using to actually try with this type. — Neil Lunn, Mar 28 '14 at 12:27

Lukasz Wiktor · Accepted Answer · 2014-03-28T12:44:22.243

1

The problem is with the Date constructor. The first parameter is not the year itself, it's 1900 + year. That's why you get 3913 when you pass 2013. What's more month starts from 0 not 1 - that explains why you get Aug instead of Sep.

edited Mar 28 '14 at 12:44

answered Mar 28 '14 at 12:27

Lukasz Wiktor

19,644
5
69
82

For future reference this is the syntax for datetime queries of mongo with groovy using gmongo driver: `def fromDate = Date.parse("yyyy-MM-dd'T'HH:mm:ss", "2013-09-23T11:41:50") println fromDate def result = db.activityLog.find(requestTime: ['$gte' : fromDate]).count() println result` – Stuart Mar 28 '14 at 14:21

Gmongo date query

2 Answers2