Query help: Clock range search - reverse e.g 9PM - 5AM

[Sarath Ms]

Hello All,

First of all, I thank all those who are making this wonderful module.

Here's my problem. The business case: I don't want to retrieve users who don't want to be retrieved during a range of hours in a day e.g., off-work hours.

On a schema close to this

{
  'name': String,
  'from': Number, // 800 (8:00 AM)
  'to': Number, // 1700 (5:00 PM)
  ...
}

I'm using an aggregation to fetch users while also unwinding subdocument arrays among other operations. The basic search criteria is in the $match operator which is like this.

User.aggregate([
  {
    // some aggregation stuff
  },
  {
    $match: {
      $and: [
        { 'from': { $lte: currentTime },
        { 'to': { $gte: currentTime }
      ]
    }
  }
]);

Question: This works fine for the regular 9AM to 5PM notation. How can I allow users to setup reverse ranges also? Like 8PM to 8AM. How can I write a query that will can select and search one of these ranges? Don't want to put this into the app logic. No hair left to pull on my head anymore.

Thanks,
Sarath

[Jason Crawford]

A few ideas off the top of my head:

1. Store two types of ranges, regular and reversed. Your query has two parts: (type = regular AND from <= current AND to >= current) OR (type = reversed AND (from >= current OR to <= current)).

2. If the end time is before the start time, add 24 hours to it. So 8pm to 8am becomes 8:00 to 32:00. Then query: (from <= current AND to >= current) OR (from <= current + 2400 AND to >= current + 2400).

3. Break the range up into two ranges, at the midnight boundary. So 8pm–8am becomes 8pm–12m, and 12m–8am. You'd have to coaslesce them when the user wanted to view or edit their ranges, though.

4. Equivalent to #2, but maybe easier to understand: Instead of start and end, have a start and duration. So instead of {from: 9:00, to: 17:00}, do: {from: 9:00, duration: 8}. The query is similar.

Each of those ways either has two parts to the query, or two parts to the data… I can't come up with a way to make it simpler than that. This is a cool problem, though, if anyone has a better answer I'd love to hear it!

-Jason

--
Documentation - http://mongoosejs.com/
Plugins - http://plugins.mongoosejs.com/
Bug Reports - http://github.com/learnboost/mongoose
Production Examples - http://mongoosejs.tumblr.com/
StackOverflow - http://stackoverflow.com/questions/tagged/mongoose
Google Groups - https://groups.google.com/forum/?fromgroups#!forum/mongoose-orm
Twitter - https://twitter.com/mongoosejs
IRC - #mongoosejs
---
You received this message because you are subscribed to the Google Groups "Mongoose Node.JS ODM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongoose-orm...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Joe Wagner]

Hi everybody,

This is a fun problem.  I have a novel solution that Jason didn't mention, and I thought I would throw it into the discussion even though its probably not the route you want to take.

Your schema would be something like:

{

    name: String,

    range: Array, 

    ....

}

The idea being that you would give each record an array that represented bins of the times in a day.  As an example, you could choose to divide each day into hours and have Arrays with length 24.  Then a user who was available from 8am to 8 pm would look like

[ 0, // not available between 12am and 1am

  0, // not available between 1am and 2am

  0, // not available between 2am and 3am

  0, // not available between 3am and 4am

  0, // etc...

  0,

  0,

  0,

  1, // AVAILABLE between 8am and 9am

  1, // etc...

  1,

  1,

  1,

  1,

  1,

  1,

  1,

  1,

  1,

  1,

  0,

  0,

  0,

  0 ]

With this solution your query is simplified to just checking if the current hour is a 1.  This also makes it easy to have more complex ranges of time, e.g. if you don't want to be contacted during lunch or you are available after dinner each night etc...

If you want to decrease the size of the bins the Arrays will quickly become much larger, and potentially make this a poor choice. 

There is an over two year old mongodb ticket that would add better support for bitwise operators https://jira.mongodb.org/browse/SERVER-3518.  If this landed you could reduce the size requirements for implementing something like this significantly and potentially use a strategy similar to above without the overhead of huge Arrays of numbers.

Anyway, like I said above, this may not be a useful solution for you, but a fun one to think about :)

-Joe

[William Riley-Land]

What if you stored an array of the hours (or other interval) to reduce space somewhat?

range: [ 1,2,3,4,5,6,7,8,20,21,22,23,24 ]

You could then query it like this: http://stackoverflow.com/questions/5366687/how-to-check-if-an-array-field-contains-a-unique-value-or-another-array-in-mongo

William Riley-Land
Principal & Open Source Consultant
Kun.io App Studio LLC
http://kun.io/wprl

[Ryan Wheale]

In your aggregation pipeline, you should project another field onto your data to specify the logic to be used in the match operation.  Lets call this new fields "between" and set it equal to "from < to".  Then your match logic would look something like this:

 $match: {
      $or: [

        { 'between': true, 'from': { $lte: currentTime }, 'to': { $gte: currentTime } },

        { 'between': false, 'from': { $gte: currentTime }, 'to': { $lte: currentTime } }
      ]

}

I can't test this right now, and my suggestion is merely conceptual.  Just trying to give you ideas.