Can we compare only time in mongodb?

Question

I have stored happyHours of hotels in date time format. Now I want to fetch all hotels whose happyHours are greater than current time.

As per my knowledge I know there is date comparison to fetch result but this will compare whole date time object.

Is there any way to compare time only?

Happyhours date object sample in db:

"happyHours" : {
    "mon" : [ 
        {
            "startTime" : ISODate("2016-04-11T06:30:59.000Z"),
            "endTime" : ISODate("2016-04-11T14:30:59.000Z")
        }
    ],
    "tue" : [ 
        {
            "startTime" : ISODate("2016-04-11T06:30:59.000Z"),
            "endTime" : ISODate("2016-04-11T14:30:59.000Z")
        }
    ],
    "wed" : [ 
        {
            "startTime" : ISODate("2016-04-11T06:30:59.000Z"),
            "endTime" : ISODate("2016-04-11T14:30:59.000Z")
        }
    ],
    "thu" : [ 
        {
            "startTime" : ISODate("2016-04-11T06:30:59.000Z"),
            "endTime" : ISODate("2016-04-11T14:30:59.000Z")
        }
    ],
    "fri" : [ 
        {
            "startTime" : ISODate("2016-04-11T06:30:59.000Z"),
            "endTime" : ISODate("2016-04-11T14:30:59.000Z")
        }
    ],
    "sat" : [ 
        {
            "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")
        }
    ]
}

Answer 1

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
            }
        }
    }
]