CODEWITHSUNDEEP
mongodbmongoose
Abdul Qayyum
Abdul Qayyum

Reputation: 1052

convert between clause of sql to mongoose

I have a table with name OgShift and I want to fetch all the records if current DateTime lies between ShiftStartTime and ShiftEndTime. It works perfectly fine in SQL but I need to implement it for MongoDB using node and mongoose.

here is my query.

SELECT * FROM dbo.OgShift 
WHERE '2018-12-18 04:43:59.810' BETWEEN ShiftStartTime AND ShiftEndTime
AND isActive=1

Database collection enter image description here

Schema

let OgShift=new mongoose.Schema({
    ShiftName:String,
    ShiftStartTime:Date,
    ShiftEndTime:Date,
    isActive:Boolean    
})

Route code

app.get('/shift',async (req,res)=>{
    var ShiftStartTime = new Date(req.query.ShiftStartTime).toISOString();
    var ShiftEndTime = new Date(req.query.ShiftEndTime).toISOString();

    let result = await OgShift.OgShift.find({
        ShiftStartTime: {
            $gt: ShiftStartTime
        },
        ShiftEndTime: {
            $lt: ShiftEndTime
        }
    });
    res.send('ShiftStartTime is' + ShiftStartTime + " end date is " + ShiftEndTime + result);

})

Result on Browser enter image description here Code after commenting the date filters

app.get('/shift',async (req,res)=>{
    var ShiftStartTime = new Date(req.query.ShiftStartTime).toISOString();
    var ShiftEndTime = new Date(req.query.ShiftEndTime).toISOString();

    let result = await OgShift.OgShift.find({});
    res.send('ShiftStartTime is' + ShiftStartTime + " end date is " + ShiftEndTime + result);

})

Result after commenting the filters <code>enter image description here</code>

Upvotes: 1

Views: 506

Answers (3)

Abdul Qayyum
Abdul Qayyum

Reputation: 1052

Well, you can not write the query in SQL way but have to do it in mongo way. After a lot of tries, I finally succeed with below logic.

let result = await OgShift.OgShift.find({
        $and: [{
            isActive: true
            }
        }],
        $or: [{
                "ShiftStartTime": {
                    "$gte": start,
                    "$lte": end
                }
            },
            {
                "ShiftEndTime": {
                    "$gte": start,
                    "$lte": end
                }
            }
        ]
    })

Upvotes: 0

Saurabh Mistry
Saurabh Mistry

Reputation: 13679

convert your input date to ISO format and then apply find query

make get request : /find?ShiftStartTime='start_date'&ShiftEndTime='end_date'

app.get('/find',function(req,res){

    var ShiftStartTime=new Date(req.query.ShiftStartTime).toISOString();
    var ShiftEndTime=new Date(req.query.ShiftEndTime).toISOString();

    OgShift.find({
       isActive:true,
       $and:[{
               ShiftStartTime:{$gte:ShiftStartTime}
             },
             {
              ShiftEndTime:{$lte:ShiftEndTime}
             }],       
    },function(err,result){
      console.log(err);
      console.log(result);
      if(!err && result){
        return res.stauts(200).json({result:result});
      }
    });

});

Upvotes: 1

Faraz Babakhel
Faraz Babakhel

Reputation: 664

Try this i hope this will work for you

let result = await OgShift.OgShift.find({
        ShiftStartTime: {
             $gte:new Date("2018-01-12"),
        },
        ShiftEndTime: {
            $lt: new Date("2019-12-31")
        }
    })

Upvotes: 1

Related Questions