mongo $gt and $ls with time as string

Question

I have a collection with field in following format

 "hours": {
        "Friday":{
             "close":"12:30",
             "open":"7:30"
                  }

I need to find if current time is between open and close. First of all I don't know what is good way to do this but i simply prepared a query like

query="{hours."currday()+".close:{gt:\""+currtime()+"\"}}";

where currday() and currtime() are javascript functions i wrote to give me name of day and time at the moment.

but now time is stored as a string and I cannot do gt to a string. I am not sure if my approach of writing js functions and preparing a query string is good one or not because I need to find most optimal way of running this query.

My first question is what is the most optimal way of doing this and second question is how do I perform something similar to between operator in sql. I am new to Mongo. Do you think i shall change structure of mongo collection or is there a way of writing query that will perform this operation.

I must repeat I am trying to find all documents whose day is current day and current time is between open and close time.

it is hard to compare apple with bananas - what I will suggest is to add field openTimeInMinutes (7:30=> 450), closeTimeInMinutes(12:30=>750) - then in you query just use integer comparison — profesor79, Mar 26 '16 at 00:42
`query` is a "string". MongoDB queries are objects and not strings. Also, the operator is `$gt` and not `gt`. There are many mistakes like that in here. If you want to "dynamically" contruct a query object with variable values for "keys" then seee [Using a variable for a key in a JavaScript object literal](http://stackoverflow.com/questions/2274242/using-a-variable-for-a-key-in-a-javascript-object-literal). I would suggest the "off-topic" vote was probably about the `$gt` mistake, if not also the "string" for a query. — Blakes Seven, Mar 26 '16 at 07:46

score 0 · Accepted Answer · answered Mar 29 '16 at 17:20

Do you think i shall change structure of mongo collection or is there a way of writing query that will perform this operation.

It sounds like you are willing to consider changing the structure of your documents, and I think that will be a wiser approach.

One way to organize opening and closing hours is:

{
    hours: [
        { day: 0, open: 450, close: 750 },
        { day: 1, open: 420, close: 720 },
        { day: 2, open: 480, close: 960 }
    ]

}

I used a suggestion from the comments to transform time (hours and minutes) into minutes, and the day is a number (0 = sunday, 1 - monday, ...) in accordance with getDay() method. With this structure you can write a query like:

var d = new Date();
var queryDayNumber = d.getDay();
var queryTimeInMinutes = d.getHours() * 60 + d.getMinutes();

db.myCollection.find({ hours: { 
    $elemMatch: { 
        day: queryDayNumber, 
        open: { $lte: queryTimeInMinutes },
        close: { $gt: queryTimeInMinutes }
    }
  }})

Transforming your current document structure to a different one should not be too difficult, but that's a whole other topic.

mongo $gt and $ls with time as string

1 Answers1