CODEWITHSUNDEEP
mongodb
tarmes
tarmes

Reputation: 15442

MongoDB time range search

Given a set of documents that contains an array of time start and end times:

{
   times: [
      { startAt: ... endAt: ...},
      { startAt: ... endAt: ...},
      { startAt: ... endAt: ...}
      ...
   ]
}

Is there a way to find all the documents which not only fall within a date range, but also a time of day.

For example, it is possible to query for all documents where at least one of the ranges ends on any day before the 1/1/16 where the end time for that day is >= 17:00?

Upvotes: 0

Views: 673

Answers (1)

somallg
somallg

Reputation: 2043

Best i can do is get the id of all documents where at least one of the ranges ends on any day before the 1/1/16 where the end time for that day is >= 17:00. You will have to query for the actual document again using the id to get full data

Sample data

db.times.insert({ time: [
    { startAt: new Date("2015-01-01 10:00"), endAt: new Date("2015-01-01 18:00") },
    { startAt: new Date("2015-01-02 10:00"), endAt: new Date("2015-01-02 18:00") } ]
});

db.times.insert({ time: [
    { startAt: new Date("2015-01-01 10:00"), endAt: new Date("2016-01-01 16:00") },
    { startAt: new Date("2015-01-02 10:00"), endAt: new Date("2015-01-02 16:00") } ]
});

db.times.insert({ time: [
    { startAt: new Date("2015-01-01 10:00"), endAt: new Date("2016-01-01 16:00") },
    { startAt: new Date("2015-01-02 10:00"), endAt: new Date("2016-01-02 16:00") } ]
});

To query

db.times.aggregate([
    { $unwind: "$time" },
    { $project: { endHour: { $hour: "$time.endAt" }, time: { startAt: "$time.startAt", endAt: "$time.endAt" } } },
    { $match: { $and: [ { "time.endAt": { $lt: new Date("2016-01-01") } }, { endHour: { $gte: 9 }} ] } },
    { $group: { _id: "$_id", time: { $push: { startAt: "$time.startAt", endAt: "$time.endAt" } } } }
]);

Output

{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d2") }

Because the data we are looking for is in the array times, for project to work we will need to unwind it to be able to examine the endAt. After first unwind stage the output is (also input for next stage)

{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d2"), "time" : { "startAt" : ISODate("2015-01-01T03:00:00Z"), "endAt" : ISODate("2015-01-01T11:00:00Z") } }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d2"), "time" : { "startAt" : ISODate("2015-01-02T03:00:00Z"), "endAt" : ISODate("2015-01-02T09:00:00Z") } }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d3"), "time" : { "startAt" : ISODate("2015-01-01T03:00:00Z"), "endAt" : ISODate("2016-01-01T09:00:00Z") } }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d3"), "time" : { "startAt" : ISODate("2015-01-02T03:00:00Z"), "endAt" : ISODate("2015-01-02T09:00:00Z") } }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d4"), "time" : { "startAt" : ISODate("2015-01-01T03:00:00Z"), "endAt" : ISODate("2016-01-01T09:00:00Z") } }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d4"), "time" : { "startAt" : ISODate("2015-01-02T03:00:00Z"), "endAt" : ISODate("2016-01-02T09:00:00Z") } }

Then go to next stage the $project, we extract the hours from endAt and keep time object otherwise we will lost list

{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d2"), "time" : { "startAt" : ISODate("2015-01-01T03:00:00Z"), "endAt" : ISODate("2015-01-01T11:00:00Z") }, "endHour" : 11 }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d2"), "time" : { "startAt" : ISODate("2015-01-02T03:00:00Z"), "endAt" : ISODate("2015-01-02T09:00:00Z") }, "endHour" : 9 }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d3"), "time" : { "startAt" : ISODate("2015-01-01T03:00:00Z"), "endAt" : ISODate("2016-01-01T09:00:00Z") }, "endHour" : 9 }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d3"), "time" : { "startAt" : ISODate("2015-01-02T03:00:00Z"), "endAt" : ISODate("2015-01-02T09:00:00Z") }, "endHour" : 9 }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d4"), "time" : { "startAt" : ISODate("2015-01-01T03:00:00Z"), "endAt" : ISODate("2016-01-01T09:00:00Z") }, "endHour" : 9 }
{ "_id" : ObjectId("56659a492b9eaad2d9e6d4d4"), "time" : { "startAt" : ISODate("2015-01-02T03:00:00Z"), "endAt" : ISODate("2016-01-02T09:00:00Z") }, "endHour" : 9 }

Note that mongodb use ISODate, at my local time is GMT+7 so, the hourEnd is not 18 but 18-7=11 After this stage we already have all information we need, we can now filter based on endAt and endHour

{ $match: { $and: [ { "time.endAt": { $lt: new Date("2016-01-01") } }, { endHour: { $gte: 10 }} ] } }

In this $match, we want only document that endAt before 2016-01-01 and endHour greater than 10 (aka 17:00)

The last stage is only to group by id to remove redundant document

Hope that helps

Upvotes: 2

Related Questions