Comparing time in string format using a MongoDB aggregate query

My database has the following data stored:

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 applied a filter based on the movie's start time and end time as follows -- starting:"12:00 pm" ending:"00:00 am"

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

I am looking to retrieve the list of movies where the start and end timing is between 

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

The desired 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 used was:

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

However, this approach did not yield the expected results. I'm uncertain about my approach and seeking guidance.

I prefer not to use $dateFromSting, new Date(), or ISODate()

I aim to achieve the desired output using only mongodb queries. Any assistance would be greatly appreciated!

javascriptarraysmongodbmongodb-queryaggregation-framework

Answer №1

It wouldn't be my top choice, but one potential data structure could look like this:

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"]}
])

To see this in action, check out the example on the playground

Answer №2

Your situation is not entirely clear. Following my input and the recommendation from @nimrod_serok, a potential solution could be as follows:

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 } } }
            ]
         }
      }
   }
])

This solution is more concise compared to the one proposed by @nimrod_serok. It's possible to simplify some of these conditions further.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Tips for extracting values from a Multiple Search Selection dropdown box using react-semantic-ui

I am having trouble retrieving the selected values from a Multiple Search Selection dropdown using the semantic-ui-React component. Although I have successfully rendered the items and enabled searching and selecting, I am struggling to extract the chosen v ...

javascriptreactjssemantic-uisemantic-ui-react

Tips for optimizing the performance of nested for loops

I wrote a for loop that iterates over 2 enums, sending them both to the server, receiving a value in return, and then calculating another value using a nested for loop. I believe there is room for improvement in this code snippet: const paths = []; for awa ...

javascripttypescriptperformancenext.js

Java for Phone Number Validation

Currently, I am in the process of creating a Java program to validate whether a user has entered a valid phone number. In this program, I am using a character array to store the phone number information. However, it seems that there might be a logical erro ...

javaarraysswitch-statement

Checking for changes in Angular text area using pipes is a common task. To do this,

I am encountering an issue with a text area containing formatted json. Users are allowed to make changes in the text area, but due to the json pipe, I cannot use [(ngmodel)}. In addition, (change) and (ngModelChange) do not appear to trigger anything. How ...

javascripthtmljsonangular

Trail of crumbs leading to pages displayed in a div container

My website is designed with only one page where all other pages are loaded within a div. I am looking to implement breadcrumbs to keep track of the pages loaded inside the div. However, the solutions I have found online only work for pages loaded in the en ...

phpjavascriptjqueryajaxbreadcrumbs

Looking for a way to add a permanent footer to the bottom of your webpage without affecting the tab order for accessibility?

I have a paginated form with a fixed navigation footer at the bottom that needs to stay in place even when scrolling. This footer should also be at the bottom of the page for mobile devices and tablets. To achieve this, I used position: fixed on the foote ...

javascriptcssreactjs

Guide on combining and organizing arrays from Collection.find({}) in Meteor

I am currently working with a Collection structure that looks like this: { "_id" : "12ds5dsfSSFS2sfds", "name": "Peter Jackson", "films" : [ { "name" : "King Kong", "date" : "2005" }, { ...

mongodbmeteoraggregation-framework

Sending a message through Discord.JS to a designated channel

Recently diving into Discord.JS, I am struggling to understand how to make my bot send a message to the General Chat when a new user joins. Many examples I've come across suggest using the following code: const channel = client.channels.cache.find(ch ...

javascripttypescriptdiscord.js

React Change Detection: Uncovering Updates

Whenever there is a change in some data, I want to trigger a side-effect. const useSearchResults = () => { const location = useLocation(); const [data, setData] = useState(); useEffect(() => { Api.search(location.query.q).then(data => se ...

javascriptreactjsreact-hooks

Unable to redirect to the initial page successfully

My React application is using Redux-Toolkit (RTK) Query for user login functionality. When the user clicks the "logout" button, it should redirect to the home page ("/"). However, I'm facing an issue where clicking the "logout" button does not trigger ...

javascriptreactjsreduxredux-toolkitrtk-query

Implementing a controller's method within a jQuery/ajax URL in Rails: A step-by-step guide

My goal is to use jQuery/Ajax to create and save data in the cart model by calling the controller's method in the Ajax URL. Here's the jQuery/Ajax script: $(document).ready(function() { $('.add-cart-product').click(function() { va ...

javascriptjqueryruby-on-railsrubyajax

Explore the versatility of MongoEngine's DynamicDocuments functionality for effective

My dilemma involves saving dictionary objects using MongoEngine: class DataRecord(DynamicDocument): record_id = SequenceField(primary_key = True) class SimpleMongoDB(object): def __init__(self, *args, **kwargs): """ Basic MongoDB ...

pythonmongodbmongoengine

How to iterate over an array and assign values to distinct labels using AngularJS

Challenge My goal is to present the user with information about their upcoming 4 events. I have used splice on an array to extract the first 4 objects. Now, I need to iterate through these objects and display the relevant data. Each label is unique and w ...

javascriptasp.netarraysangularjs

Problem with npm during Nativescript command line installation

I'm having trouble setting up Nativescript for developing iOS apps in Javascript. When I tried to install it using the command below: npm i -g nativescript I encountered the following error message: module.js:327 throw err; ^ Error: Cannot ...

javascriptnpmcommandlinenativescript

Executing the Correct Command to Import a CSV File into MongoDB using OSX Terminal

I am attempting to upload a TSV file into Mongodb, but my lack of familiarity with Terminal is causing issues. I keep receiving an error when trying to execute the following command. Can anyone provide guidance? /mongoimport --db webapp-dev --collection ...

javascriptmongodbcsvterminal

Encountering difficulties reading data from a database in a Next.js/Firebase application

I am encountering a problem within a nextJS app that I developed on Firebase. In my realtime DB, I have some stored data that I want to read using a component. Below is my firebase/config.js file: import {initializeApp} from "firebase/app"; imp ...

javascriptfirebasenext.jsfirebase-realtime-database

"Exploring the possibilities of Ajax in conjunction with Sol

I recently completed a tutorial on Ajax Solr and followed the instructions in step one. Below is the code I wrote: header.php: <script type="text/javascript" src="static/js/ajax-solr/core/Core.js"></script> <script type="text/javascript" s ...

javascriptjqueryajaxsolr

Contrast in the way JavaScript input is handled between computers and smartphones

Recently, I developed a sudoku game using JavaScript. The program is designed to only accept numbers from 1-9. However, when I tested it on my Android phone, the program was accepting both letters and digits. Unfortunately, the code seems to not be workin ...

javascript

Is there a way to transfer a value from one JS function to another in Node.js?

I am struggling to retrieve the return value from a JavaScript function in Node.js and pass it back in a POST method within my server file. Despite my efforts, I keep receiving an undefined result. What could be causing this issue? My objective is to retur ...

javascriptnode.js

Simultaneous findAndModify queries are able to successfully update a shared document

Currently, I have a Java task worker operating with a MongoDB 3.4 replica set, where numerous threads are executing similar tasks simultaneously. Execute task Indicate task completion by updating a corresponding document in MongoDB Check if all tasks in ...

javamongodb