datetime - MongoDB - Querying between a time range of hours

Question

I have a MongoDB datastore set up with location data stored like this:

{
"_id" : ObjectId("51d3e161ce87bb000792dc8d"),
"datetime_recorded" : ISODate("2013-07-03T05:35:13Z"),
"loc" : {
    "coordinates" : [
        0.297716,
        18.050614
    ],
    "type" : "Point"
},
"vid" : "11111-22222-33333-44444"
}

I'd like to be able to perform a query similar to the date range example but instead on a time range. i.e. Retrieve all points recorded between 12AM and 4PM (can be done with 1200 and 1600 24 hour time as well).

e.g.

With points:

• "datetime_recorded" : ISODate("2013-05-01T12:35:13Z"),
• "datetime_recorded" : ISODate("2013-06-20T05:35:13Z"),
• "datetime_recorded" : ISODate("2013-01-17T07:35:13Z"),
• "datetime_recorded" : ISODate("2013-04-03T15:35:13Z"),

a query

db.points.find({'datetime_recorded': {
    $gte: Date(1200 hours),
    $lt: Date(1600 hours)}
});

would yield only the first and last point.

Is this possible? Or would I have to do it for every day?

Answer 1

Well, the best way to solve this is to store the minutes separately as well. But you can get around this with the aggregation framework, although that is not going to be very fast:

db.so.aggregate( [ 
    { $project: {
        loc: 1,
        vid: 1,
        datetime_recorded: 1, 
        minutes: { $add: [
            { $multiply: [ { $hour: '$datetime_recorded' }, 60 ] }, 
            { $minute: '$datetime_recorded' } 
        ] } 
    } },
    { $match: { 'minutes' : { $gte : 12 * 60, $lt : 16 * 60 } } }
] );

In the first step $project, we calculate the minutes from hour * 60 + min which we then match against in the second step: $match.