Mongodb : Get Documents between specified time irrespective of dates

Question

Suppose the sample documents in Mongodb database are as follows:

 { "date" : ISODate("2015-11-09T05:58:19.474Z") }
 { "date" : ISODate("2014-10-25T07:30:00.241Z") }
 { "date" : ISODate("2015-11-30T15:24:00.251Z") }
 { "date" : ISODate("2012-01-10T18:36:00.101Z") }

Expected:

 { "date" : ISODate("2015-11-09T05:58:19.474Z") }
 { "date" : ISODate("2014-10-25T07:30:00.241Z") }

Iam interested in finding the documents whose time in "date" field is between 04:15 and 07:40 irrespective of day,month and year. Indirectly query has to match any "YYYY-MM-DDT" in date field.

My approach would be, query all the documents within presumed duration of dates from node and then for each document that matched the query, compare the "date" field of document with "yyyy-MM-DDT"+"required_time" ("YYYY-MM-DD is copied from each document's "date field" to compare by converting into moment() and get month,date and year") using moment.js module.

Is there any way to query to directly get the same results?

Note: I am using nodejs to connect to mongodb

This question is based on Mongodb : Query based on time in ISODate format.

Blakes Seven Blakes Seven · Accepted Answer · 2015-11-09T10:09:57

The basic case here is to use math to work out the minutes. Noting you already have an answer on the basic principles of matching, but just seem to have trouble working out the "minutes" part:

db.collection.aggregate([
    { "$redact": {
        "$cond": {
            "if": {
                "$and": [
                    { "$gte": [
                        { "$add": [
                            { "$hour": "$date" },
                            { "$divide": [{ "$minute": "$date" }, 60] }
                        ]},
                        4 + (15/60)
                    ]},
                    { "$lte": [
                        { "$add": [
                            { "$hour": "$date" },
                            { "$divide": [{ "$minute": "$date" }, 60] }
                        ]},
                        7 + (40/60)
                    ]}
                ]
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])

So when you $add the divisor of $minute to the $hour then you get a representation that is stil between 0-24 but with a fractional component.

Or just use date math directly, and clean up duplication with $let:

db.collection.aggregate([
    { "$redact": {
        "$cond": {
            "if": {
                "$let": {
                    "vars": {
                        "hours": {
                            "$divide": [
                                { "$mod": [
                                    { "$subtract": [ "$date", new Date(0) ] },
                                    1000 * 60 * 60 * 24
                                ]},
                                1000 * 60 * 60
                            ]
                        }
                    },
                    "in": {
                        "$and": [
                            { "$gte": [ "$$hours", 4 + (15/60) ] },
                            { "$lte": [ "$$hours", 7 + (40/60) ] }
                        ]
                    }
                }
            },
            "then": "$$KEEP",
            "else": "$$PRUNE"
        }
    }}
])

Either way, it's just about working out the partial component and adding that into the value for selection.

If you really want to query this on a regular basis, I would strongly suggest you set a separate document property to just contain the "time" value and query directly on that instead of calculating on each pass.

Mongodb : Get Documents between specified time irrespective of dates

1 Answers