Can we compare only time in mongodb?
To start with, the current schema is not conducive for querying; the arrays are really not necessary in this case, if you were to keep the weekdays as keys then the better approach would be to lose the arrays and just reference the element:
"happyHours": { "mon": { "startTime" : ISODate("2016-04-11T06:30:59.000Z"), "endTime" : ISODate("2016-04-11T14:30:59.000Z") }, ... "sun": { "startTime" : ISODate("2016-04-11T06:30:59.000Z"), "endTime" : ISODate("2016-04-11T14:30:59.000Z") } }
However, a much better design which makes querying much easier beckons. You could convert the happyHours
field into an array that holds a document which describes the weekday, start hour, start minutes, end hour and minutes respectively as follows:
"happyHours": [ { "weekDay": "mon", "startHour": 6, "startMinutes": 30, "endHour": 14 "endMinutes": 30 } ...]
then querying would be as follows:
var now = new Date(), days = ['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sun'], weekDay = days[now.getDay()], hour = now.getHours(), minutes = now.getMinutes();db.hotels.find({ "happyHours.weekDay": weekDay, "happyHours.startHour": { "$lte": hour }, "happyHours.startMinutes": { "$lte": minutes }, "happyHours.endHour": { "$gte": hour }, "happyHours.endMinutes": { "$gte": minutes }})
Now, if you don't have the capacity to modify your schema to conform to the above recommendations, then the aggregation framework offers a workaround. Consider the following aggregation pipeline which makes use of the date aggregation operators in the $project
step and query in the subsequent $match
pipeline:
var now = new Date(), days = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sun'], weekDay = days[now.getDay()], hour = now.getHours(), minutes = now.getMinutes(); project = { "$project": { "otherfields": 1, "happyHours": 1, "happyHoursMonStartHour": { "$hour": "$happyHours.mon.startTime" }, "happyHoursMonStartMinutes": { "$minute": "$happyHours.mon.startTime" }, "happyHoursMonEndHour": { "$hour": "$happyHours.mon.endTime" }, "happyHoursMonEndMinutes": { "$minute": "$happyHours.mon.endTime" }, "happyHoursTueStartHour": { "$hour": "$happyHours.tue.startTime" }, "happyHoursTueStartMinutes": { "$minute": "$happyHours.tue.startTime" }, "happyHoursTueEndHour": { "$hour": "$happyHours.tue.endTime" }, "happyHoursTueEndMinutes": { "$minute": "$happyHours.tue.endTime" }, "happyHoursWedStartHour": { "$hour": "$happyHours.wed.startTime" }, "happyHoursWedStartMinutes": { "$minute": "$happyHours.wed.startTime" }, "happyHoursWedEndHour": { "$hour": "$happyHours.wed.endTime" }, "happyHoursWedEndMinutes": { "$minute": "$happyHours.wed.endTime" }, "happyHoursThuStartHour": { "$hour": "$happyHours.thu.startTime" }, "happyHoursThuStartMinutes": { "$minute": "$happyHours.thur.startTime" }, "happyHoursThuEndHour": { "$hour": "$happyHours.thu.endTime" }, "happyHoursThuEndMinutes": { "$minute": "$happyHours.thu.endTime" }, "happyHoursFriStartHour": { "$hour": "$happyHours.fri.startTime" }, "happyHoursFriStartMinutes": { "$minute": "$happyHours.fri.startTime" }, "happyHoursFriEndHour": { "$hour": "$happyHours.fri.endTime" }, "happyHoursFriEndMinutes": { "$minute": "$happyHours.fri.endTime" }, "happyHoursSatStartHour": { "$hour": "$happyHours.sat.startTime" }, "happyHoursSatStartMinutes": { "$minute": "$happyHours.sat.startTime" }, "happyHoursSatEndHour": { "$hour": "$happyHours.sat.endTime" }, "happyHoursSatEndMinutes": { "$minute": "$happyHours.sat.endTime" }, "happyHoursSunStartHour": { "$hour": "$happyHours.sun.startTime" }, "happyHoursSunStartMinutes": { "$minute": "$happyHours.sun.startTime" }, "happyHoursSunEndHour": { "$hour": "$happyHours.sun.endTime" }, "happyHoursSunEndMinutes": { "$minute": "$happyHours.sun.endTime" }, } }, match = { "$match": {} }, pipeline = [ { "$unwind": "$happyHours.mon" }, { "$unwind": "$happyHours.tue" }, { "$unwind": "$happyHours.wed" }, { "$unwind": "$happyHours.thur" }, { "$unwind": "$happyHours.fri" }, { "$unwind": "$happyHours.sat" }, { "$unwind": "$happyHours.sun" } ];match["$match"]["happyHours"+ weekDay +"StartHour"] = { "$lte": hour };match["$match"]["happyHours"+ weekDay +"StartMinutes"] = { "$lte": minutes };match["$match"]["happyHours"+ weekDay +"EndHour"] = { "$gte": minutes };match["$match"]["happyHours"+ weekDay +"EndMinutes"] = { "$gte": minutes };pipeline.push(project);pipeline.push(match);db.hotels.aggregate(pipeline);
Printing the pipeline before running it with printjson(pipeline)
would show you this:
[ { "$unwind" : "$happyHours.mon" }, { "$unwind" : "$happyHours.tue" }, { "$unwind" : "$happyHours.wed" }, { "$unwind" : "$happyHours.thur" }, { "$unwind" : "$happyHours.fri" }, { "$unwind" : "$happyHours.sat" }, { "$unwind" : "$happyHours.sun" }, { "$project" : { "otherfields" : 1, "happyHours" : 1, "happyHoursMonStartHour" : { "$hour" : "$happyHours.mon.startTime" }, "happyHoursMonStartMinutes" : { "$minute" : "$happyHours.mon.startTime" }, "happyHoursMonEndHour" : { "$hour" : "$happyHours.mon.endTime" }, "happyHoursMonEndMinutes" : { "$minute" : "$happyHours.mon.endTime" }, "happyHoursTueStartHour" : { "$hour" : "$happyHours.tue.startTime" }, "happyHoursTueStartMinutes" : { "$minute" : "$happyHours.tue.startTime" }, "happyHoursTueEndHour" : { "$hour" : "$happyHours.tue.endTime" }, "happyHoursTueEndMinutes" : { "$minute" : "$happyHours.tue.endTime" }, "happyHoursWedStartHour" : { "$hour" : "$happyHours.wed.startTime" }, "happyHoursWedStartMinutes" : { "$minute" : "$happyHours.wed.startTime" }, "happyHoursWedEndHour" : { "$hour" : "$happyHours.wed.endTime" }, "happyHoursWedEndMinutes" : { "$minute" : "$happyHours.wed.endTime" }, "happyHoursThuStartHour" : { "$hour" : "$happyHours.thu.startTime" }, "happyHoursThuStartMinutes" : { "$minute" : "$happyHours.thur.startTime" }, "happyHoursThuEndHour" : { "$hour" : "$happyHours.thu.endTime" }, "happyHoursThuEndMinutes" : { "$minute" : "$happyHours.thu.endTime" }, "happyHoursFriStartHour" : { "$hour" : "$happyHours.fri.startTime" }, "happyHoursFriStartMinutes" : { "$minute" : "$happyHours.fri.startTime" }, "happyHoursFriEndHour" : { "$hour" : "$happyHours.fri.endTime" }, "happyHoursFriEndMinutes" : { "$minute" : "$happyHours.fri.endTime" }, "happyHoursSatStartHour" : { "$hour" : "$happyHours.sat.startTime" }, "happyHoursSatStartMinutes" : { "$minute" : "$happyHours.sat.startTime" }, "happyHoursSatEndHour" : { "$hour" : "$happyHours.sat.endTime" }, "happyHoursSatEndMinutes" : { "$minute" : "$happyHours.sat.endTime" }, "happyHoursSunStartHour" : { "$hour" : "$happyHours.sun.startTime" }, "happyHoursSunStartMinutes" : { "$minute" : "$happyHours.sun.startTime" }, "happyHoursSunEndHour" : { "$hour" : "$happyHours.sun.endTime" }, "happyHoursSunEndMinutes" : { "$minute" : "$happyHours.sun.endTime" } } }, { "$match" : { "happyHoursThuStartHour" : { "$lte" : 9 }, "happyHoursThuStartMinutes" : { "$lte" : 34 }, "happyHoursThuEndHour" : { "$gte" : 34 }, "happyHoursThuEndMinutes" : { "$gte" : 34 } } }]