nosql - Querying between a time range of hours in MongoDB

Question

I want to extract a document between a specific range of hours(between 19:01 - 06:59) of daily activity. My dateTime document is stored in ISODate.

{
    "_id" : ObjectId("5ff8f5a56c3aacbab9ee0f41"),
    "dateTime" : ISODate("2020-06-11T20:45:00.000Z"),
    "value" : "37"
}

/* 29 */
{
    "_id" : ObjectId("5ff8f5a56c3aacbab9ee0f42"),
    "dateTime" : ISODate("2020-06-11T20:49:00.000Z"),
    "value" : "6"
}

I tried the following query, but I am not getting any result.

db.Steps.aggregate([
{$project:{
    byDay:{$dayOfMonth:{date:"$dateTime"}},
    byMonth:{$month:{date:"$dateTime"}},
    _id:0,
    val:{$toInt:"$value"},
    minutes: { $add: [
            { $multiply: [ { $hour: '$dateTime' }, 60 ] }, 
            { $minute: '$dateTime' }
        ] }}} ,
{ $match: { 'minutes' : { $gt : 19 * 60, $lt : 7 * 60 } } },
{$group:{
    _id:{month:"$byMonth",day:"$byDay" },
    StepsDone:{$avg:"$val"}}}  ,
{$sort:{_id:1}}   ]);

question from:https://stackoverflow.com/questions/65644348/querying-between-a-time-range-of-hours-in-mongodb

Answer 1

This need to do the job:

mongos> db.Steps.find()
{ "_id" : ObjectId("5ff9d1f9da146da4e1359de8"), "value" : 10, "dateTime" : ISODate("2013-10-01T20:19:00Z") }
{ "_id" : ObjectId("5ff9d203da146da4e1359de9"), "value" : 20, "dateTime" : ISODate("2013-10-01T20:20:00Z") }
{ "_id" : ObjectId("5ff9d20ada146da4e1359dea"), "value" : 30, "dateTime" : ISODate("2013-10-01T20:21:00Z") }
{ "_id" : ObjectId("5ff9d22dda146da4e1359deb"), "value" : 30, "dateTime" : ISODate("2013-12-01T20:21:00Z") }
{ "_id" : ObjectId("5ff9d236da146da4e1359dec"), "value" : 30, "dateTime" : ISODate("2013-12-01T20:15:00Z") }
{ "_id" : ObjectId("5ff9d255da146da4e1359ded"), "value" : 30, "dateTime" : ISODate("2013-06-01T20:05:00Z") }
mongos> 
mongos> db.Steps.aggregate([ {$project:{     byDay:{$dayOfMonth:{date:"$dateTime"}},     byMonth:{$month:{date:"$dateTime"}},     _id:0,     val:{$toInt:"$value"},     minutes: { $add: [             { $multiply: [ { $hour: '$dateTime' }, 60 ] },              { $minute: '$dateTime' }         ] }}} , { $match: { $or:[  { minutes:{$gt : 19 * 60}}, {minutes:{$lt : 7 * 60 }} ] } }, {$group:{     _id:{month:"$byMonth",day:"$byDay" },     StepsDone:{$avg:"$val"}}}  , {$sort:{_id:1}}   ]);
{ "_id" : { "month" : 6, "day" : 1 }, "StepsDone" : 30 }
{ "_id" : { "month" : 10, "day" : 1 }, "StepsDone" : 20 }
{ "_id" : { "month" : 12, "day" : 1 }, "StepsDone" : 30 }
mongos> 

Modified to display the final date in "DD/MM/YYYY" format:

mongos>db.Steps.aggregate([ {$project:{     byDay:{$dayOfMonth:{date:"$dateTime"}},     byMonth:{$month:{date:"$dateTime"}},  byYear:{$year:{date:"$dateTime"}} ,  _id:0,     val:{$toInt:"$value"},     minutes: { $add: [             { $multiply: [ { $hour: '$dateTime' }, 60 ] },              { $minute: '$dateTime' }         ] }}} , { $match: { $or:[  { minutes:{$gt : 19 * 60}}, {minutes:{$lt : 7 * 60 }} ] } }, {$group:{     _id:{ year:{$toString:"$byYear"} ,   month:{$toString:"$byMonth"} ,day:{$toString:"$byDay"} },StepsDone:{$avg:"$val"}}} , {$sort:{_id:-1}} ,{$project:{dateTime:{$concat:["$_id.day","/","$_id.month","/","$_id.year" ]  },_id:0,StepsDone:1  }}    ]); 
{ "StepsDone" : 20, "dateTime" : "1/6/2013" }
{ "StepsDone" : 30, "dateTime" : "1/10/2013" }
{ "StepsDone" : 30, "dateTime" : "1/12/2013" }