Query datetime by time of day in MongoDB

Question

I have a collection of objects in my mongoDB containing datetimes among other values. How would I go about querying the objects by datetime, where the timestamp is set to be at 9 o'clock?

So if I have the following collection...

id : 1, date : ISODate("2017-07-16T09:00:00.000+0000")
id : 2, date : ISODate("2017-01-17T07:00:00.000+0000")
id : 3, date : ISODate("2017-07-27T09:00:00.000+0000")
id : 4, date : ISODate("2017-03-20T09:00:00.000+0000")
id : 5, date : ISODate("2017-03-07T10:00:00.000+0000")
id : 6, date : ISODate("2017-07-04T11:00:00.000+0000")

The return value should be...

id : 1, date : ISODate("2017-07-16T09:00:00.000+0000")
id : 3, date : ISODate("2017-07-27T09:00:00.000+0000")
id : 4, date : ISODate("2017-03-20T09:00:00.000+0000")

I'm fairly new to MongoDB and not very experienced with Js so please try and keep it as simple as you can. To that note Neil Lunn marked this question as a duplicate of This Question, which I feel is partially correct, but it's also more complex than I need.

I don't need grouping or anything of that nature, I just want a query that tells me which documents exist containing this timestamp.

glytching · Accepted Answer · 2017-07-31T14:11:36.233

You could use an aggregate pipeline to convert the date to its timepart and then match on that converted value. For example:

db.collection.aggregate([
    {
        $project: {
            timePart: {$dateToString: { format: "%H:%M:%S:%L", date: "$date"}},     
            date: 1

        }
    },
    {
        $match: {
            timePart: '09:00:00:000'
        }
    },
    {
        $project: {
            date: 1
        }
    }
])

You can think of this as a pipeline; the output from the first $project step becomes the input to the $match step. The $project step outputs - for every document in the underlying collection - a document containing the _id, the date and a new attribute named timePart which has been populated with the time part from the date attribute. The $match step then matches these documents against your filter criteria (in your example this is 09:00:00:000 i.e. 9am) and then the documents which are matched are then forwarded to the next step which uses the $project operator again to discard the timePart attribute since, I assume, that is only relevant for saerching and should not be included in the end result.

Breaking it down, the output of the first step looks like this:

{
   "_id" : 1,
   date : ISODate("2017-07-16T09:00:00.000+0000"),
   timePart: "09:00:00.000"
},
{
   "_id" : 2,
   date : ISODate("2017-01-17T07:00:00.000+0000"),
   timePart: "07:00:00.000"
},
...

The second step excludes the document with id: 2 because its timePart does not match 09:00:00.000 and then forwards the document with id: 1 to the third stage which then selects - from those documents forwarded by step 2 - the fields _id and date thereby giving you:

{
   "_id" : 1,
   date : ISODate("2017-07-16T09:00:00.000+0000")
},
{
   "_id" : 3,
   date : ISODate("2017-07-27T09:00:00.000+0000")
},
{
   "_id" : 4,
   date : ISODate("2017-03-20T09:00:00.000+0000")
}

Note: this approach must transform the date attribute of every document before applying the match stage, if that's worryingly inefficient for you then you might want to reconsider how you are persisting this data.

I'm fairly new to MongoDB, so let me just sum this up to see if I get this correctly: for each document you take the date, format it, turn it into a string, then into a timepart, and then you match it against another timepart made from a string? — SpiritBH, Jul 31 '17 at 13:57
I've updated the answer to explain the nature of an aggregation pipeline. Hope that helps. — glytching, Jul 31 '17 at 14:12
Thank you, this worked like a charm and you helped me understand this all a little better. — SpiritBH, Aug 01 '17 at 11:34

Query datetime by time of day in MongoDB

1 Answers1