Search code examples
mongodbgmongo

Gmongo date query

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
Solution

  • 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.