Mongodb aggregate query to compare time in String format

itiDi 0
Viewed 58

I have data saved in db as follow:

collection name: movies:

[
  {id:1, name:"abch", start:"12:00 pm", end:"03:00 pm"},
  {id:2, name:"Annabelle", start:"08:30 am", end:"10:00 am"},
  {id:3, name:"Spider Man homecoming", start:"11:30 am", end:"03:00 pm"},
  {id:4, name:"Grudge", start:"10:00 pm", end"00:00 am"}
]

I use filter on the movie's start time and end time, like this -- starting:"12:00 pm" ending:"00:00 am"

req.query = { starting:"12:00 pm", ending:"00:00 am" }

I want all the list of movies where the start and end timing is between req.query = { starting:"12:00 pm", ending:"00:00 am" }

Output data should be:

[
  {id:1, name:"abch", start:"12:00 pm", end:"03:00 pm"},
  {id:3, name:"Spider Man homecoming", start:"11:30 am", end:"03:00 pm"},
  {id:4, name:"Grudge", start:"10:00 pm", end"00:00 am"}
]

The mongodb query I used is:

movies.aggregate([
    $or: [
           {"start":req.query.starting},{"end":req.query.ending}
         ]
])

But this does not work. I dont know how to do this and dont know where I am going wrong.

I dont want to use $dateFromSting or new Date() or ISODate()

Want to get output with the above and want it to get from mongodb queries. Need Help Please!!!

aggregation-framework mongodb-query arrays mongodb javascript
2 Answers

I would not recommand such a data structure, but one option is:

db.collection.aggregate([
  {$set: {
      startHour: {$add: [
          {$mod: [{$toInt: {$substr: ["$start", 0, 2]}}, 12]},
          {$divide: [{$toInt: {$substr: ["$start", 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: ["$start", 6, 2]}, "am"]}, 0, 12]}
      ]},
      startingHour: {$add: [
          {$mod: [{$toInt: {$substr: [req.query.starting, 0, 2]}}, 12]},
          {$divide: [{$toInt: {$substr: [req.query.starting, 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: [req.query.starting, 6, 2]}, "am"]}, 0, 12]}
      ]},
      endingHour: {$add: [
          {$toInt: {$substr: [req.query.ending, 0, 2]}},
          {$divide: [{$toInt: {$substr: [req.query.ending, 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: [req.query.ending, 6, 2]}, "am"]}, 0, 12]}
      ]},
      endHour: {$add: [
          {$toInt: {$substr: ["$end", 0, 2]}},
          {$divide: [{$toInt: {$substr: ["$end", 3, 2]}}, 60]},
          {$cond: [{$eq: [{$substr: ["$end", 6, 2]}, "am"]}, 0, 12]}
    ]}
  },
  {$set: {
      endHour: {$cond: [{$eq: ["$endHour", 0]}, 24, "$endHour"]},
      endingHour: {$cond: [{$eq: ["$endingHour", 0]}, 24, "$endingHour"]}
    }
  },
  {$match: {
      $expr: {
        $and: [{$gte: ["$startHour", "$startingHour"]},
          {$lte: ["$endHour", "$endingHour"]}]
      }
    }
  },
  {$unset: ["stasrtHour", "endHour", "startingHour", "endingHour"]}
])

See how it works on the playground example

nimrod serok0

Your condition is not 100% clear. Based on my comment and also according to @nimrod_serok recommendation a solution would be this one:

db.collection.insertMany([
   { id: 1, name: "abch", start: { h: 12, m: 0 }, end: { h: 15, m: 0 } },
   { id: 2, name: "Annabelle", start: { h: 8, m: 30 }, end: { h: 10, m: 0 } },
   { id: 3, name: "Spider Man homecoming", start: { h: 11, m: 30 }, end: { h: 15, m: 00 } },
   { id: 4, name: "Grudge", start: { h: 22, m: 0 }, end: { h: 24, m: 00 } }
])


starting = { h: 12, m: 0 }
ending = { h: 24, m: 0 }

db.collection.aggregate([
   {
      $set: {
         startTime: { $dateFromParts: { year: 1900, hour: "$start.h", minute: "$start.m" } },
         endTime: { $dateFromParts: { year: 1900, hour: "$end.h", minute: "$end.m" } }
      }
   },
   {
      $match: {
         $expr: {
            $and: [
               { $gte: ["$startTime", { $dateFromParts: { year: 1900, hour: starting.h, minute: starting.m } }] },
               { $lte: ["$startTime", { $dateFromParts: { year: 1900, hour: ending.h, minute: ending.m } }] },
               { $gte: ["$endTime", { $dateFromParts: { year: 1900, hour: starting.h, minute: starting.m } }] },
               { $lte: ["$endTime", { $dateFromParts: { year: 1900, hour: ending.h, minute: ending.m } }] }
            ]
         }
      }
   }
])

Which is much smaller compared to @nimrod_serok proposal. Some of these conditions are redundant, i.e. you can even reduce them.

Wernfried Domscheit0
Related