CODEWITHSUNDEEP
javascriptmongodbmongoose
user3334406
user3334406

Reputation: 337

Mongoose select query between two time range

Ok this is getting interesting, im working on a school project schedule, and use mongoDB, which has no time attribute.

I want to query between two time ranges in a class.

This SQL Query works but im trying this in mongoose:

SELECT * From Schedule WHERE room_name = ?  AND ((start_time) BETWEEN @KP and @KD OR (end_time) BETWEEN @KP and @KD)  AND day=?

For example:

First lecture starts from 08:00 till 09:00 in room number 8 on Monday.

If i try to add another lecture with start time: 08:30 end time: 09:30 in room number 8 on Monday , it should tell me that i can not because the class is busy at the moment.

This is the Schema im using:

    day: Number,
    start_time: Date,
    end_time: Date,
    room: [{ type: Schema.Types.ObjectId, ref: 'Room' }]

I need ideas how can i solve this :) because im a begginer in mongo.

Upvotes: 0

Views: 3515

Answers (2)

ZeMoon
ZeMoon

Reputation: 20284

I am assuming the model name is Lecture.

var newStartTime; //Start time for class to be created.
var newEndTime;   //End time for class to be created.

Lecture.find({$and: [
{$or: [{$and: [{start_time: {$lte: newStartTime}}, {end_time: {$gte: newStartTime}}]}, {$and: [{start_time: {$lte: newEndTime}}, {end_time: {$gte: newEndTime}}]}]},
{$not: {$or: [{start_time: {$eq: newEndTime}}, {end_time: {$eq: newStartTime}}]}}
]}, function (err, results) {

    if (err) {
        //handle error
        return;
    }

    if (results.length > 0)
    {
        //Clashing class, handle
    }
});

The above query checks for lectures that:

Upvotes: 3

user3334406
user3334406

Reputation: 337

Thanks to @ZeMoon i solved my problem, here is the full query if someone needs it:

$or: [{$and: [
                {$or: [
                        {$and: [{start_time: {$lt: newStartTime}}, {end_time: {$gt: newStartTime}}]}, 
                        {$and: [{start_time: {$lt: newEndTime}}, {end_time: {$gt: newEndTime}}]},
                        {$and: [{start_time: {$gte: newStartTime}}, {end_time: {$lte: newEndTime}}]},

            ]},
                        {$or: [{start_time: {$ne: newStartTime}}, {end_time: {$ne: newEndTime}}]},

                 ]
                    },
                        {$and: [{start_time: {$eq: newStartTime}}, {end_time: {$eq: newEndTime}}]}
            ]

Upvotes: 2

Related Questions