Question 116802 in Mongodb Question posted in
The official documentation can be found here.

Mongodb aggregate query to compare time in String format

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!!!

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search