CODEWITHSUNDEEP
sqlmongodbmongodb-querynosqlaggregation-framework
Somto
Somto

Reputation: 75

Filter time range in Mongodb

I want to get all the documents that fall within a particular TIME in MongoDB. I have searched for this, but I can only find solutions for documents that fall within a particular DATE.

For example, the SQL expression of what I am looking for would be: WHERE YEAR(date_time) = 2019 AND TIME(date_time) BETWEEN '07:30' AND '08:30'

Upvotes: 0

Views: 169

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

You can use this one:

db.collection.aggregate([
   {
      $addFields: {
         parts: {
            $dateToParts: {
               date: "$date_time" // , timezone : <timezone> if needed
            }
         }
      }
   },
   { $set: { "parts.time": { $sum: [{ $multiply: ["$parts.hour", 24] }, "$parts.minute"] } } },
   {
      $match: {
         "parts.year": 2019,
         "parts.time": {
            $gte: { $sum: [{ $multiply: [7, 24] }, 30] },
            $lte: { $sum: [{ $multiply: [8, 24] }, 30] }
         }
      }
   }
])

Another solution could be this one:

db.collection.aggregate([
   {
      $addFields: {
         time: {
            $dateFromParts: {
               year: { $year: "$date_time" }, month: 1, day: 1,
               hour: { $hour: "$date_time" }, minute: { $minute: "$date_time" }
               // , timezone : <timezone> if needed
            }
         }
      }
   },
   {
      $match: {
         time: {
            $gte: ISODate("2019-01-01T07:30:00Z"),
            $lte: ISODate("2019-01-01T08:30:00Z")
         }
      }
   }
])

Upvotes: 1

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57105

Demo - https://mongoplayground.net/p/Ny5FCEiQkE7

Use $expr

db.collection.aggregate([{
  $match: {
    $expr: { $eq: [ { $year: "$dt" }, 2021 ] }, // match year
    $or: [ // or query
      { $and: [ // match hour 7  and minutes 30+
          { $expr: { $eq: [ { "$hour": "$dt" }, 7 ] } },
          { $expr: { $gte: [ { "$minute": "$dt" }, 30 ] } }
        ]
      }, 
      { $and: [ // match hour 8  and minutes less than 30
          { $expr: { $eq: [ { "$hour": "$dt" }, 8 ] } },
          { $expr: { $lt: [ { "$minute": "$dt" }, 30 ] } } 
        ]
      }, 
    ]
  }
}])

Upvotes: 2

Related Questions