7

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")
        }
    ]
}
kadamb
  • 1,532
  • 3
  • 29
  • 55
zulekha
  • 313
  • 9
  • 17

1 Answers1

5

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
            }
        }
    }
]
chridam
  • 100,957
  • 23
  • 236
  • 235
  • the reason of adding array is here that hotel can have happyhours in multiple slots for same day. For eg 10 to 11 then same day 20 to 23 like that. – zulekha May 02 '16 at 10:27
  • Hi @zulekha Is there anything in the answer that wasn't clear? – chridam May 05 '16 at 07:53
  • Thank you for your time. But this will not work for me as I explained earlier that I may have many happyHour slots in each day. – zulekha May 05 '16 at 07:59
  • No worries :) I think you must have missed the part where I recommended a workaround using the aggregation framework if you cannot modify the schema, the last part of my answer offers the solution. – chridam May 05 '16 at 08:02
  • I looked at it. As per answer I can not get "happyHours.mon.startTime" as happyHours.mon is array of object not single object in my design. – zulekha May 05 '16 at 08:20
  • The `$unwind` operator takes care of that as it flattens the array. If you were to run the `printjson(pipeline)` in mongo shell you would get the above pipeline structure which you can then use to run your aggregation operation. – chridam May 05 '16 at 08:43
  • Oh I get it. I'll try this. Thanks :) – zulekha May 05 '16 at 09:04
  • 1
    @chridam I am using your suggested schema for storing shifts but got stuck in a case say that we have a shift stars 08:50 and ends 20:30 and suppose that time now 18:40 based on your code: ` 1- "happyHours.startHour": { "$lte": hour }, 2- "happyHours.startMinutes": { "$lte": minutes }, 3- "happyHours.endHour": { "$gte": hour }, 4- "happyHours.endMinutes": { "$gte": minutes } ` it will pass the first condition as 8 is smaller than 18 but it will fail in the second as 50 is bigger than 40 although logically in real-life 08:50 comes before 18:40 – tarek noaman Aug 17 '20 at 13:46