CODEWITHSUNDEEP
mongodbmongodb-query
Bonzo
Bonzo

Reputation: 443

Query to find data in particular time and date range

I need to find data inserted from 7AM yesterday to 7AM present day. How can I build a query to achieve this. I am able to fetch yesterdays data but not able to add time filter. Can someone help me with it?

db.company.find({ 
  "createdAt" : { 
    $lt: new Date(), 
    $gte: new Date(new Date().setDate(new Date().getDate()-1))
  }   
})

Upvotes: 2

Views: 1127

Answers (3)

prasad_
prasad_

Reputation: 14287

I need to find data inserted from 7AM yesterday to 7AM present day. How can I build a query to achieve this.

I have some documents like this, and two of them are within 7AM yesterday and 7AM today (19th Dec). 

{ dt: ISODate("2019-12-18T12:00:00.000Z") },
{ dt: ISODate("2019-12-17T05:00:00.000Z") },
{ dt: ISODate("2019-12-19T02:00:00.000Z") },
{ dt: ISODate("2019-12-19T14:00:00.000Z") }

The query returns two matching documents:

var yesterday_7am = ISODate("2019-12-18T07:00:00.000Z")
var today_7am = ISODate("2019-12-19T07:00:00.000Z")

db.test.find( { dt: { $gte: yesterday_7am, $lte: today_7am } } )


A generic query:

var this_7am =  { $dateFromParts: {
                       year: { $year: new Date() }, 
                       month: { $month: new Date() }, 
                       day: { $dayOfMonth: new Date() },
                       hour: 7
                 }
}

var last_7am = { $subtract: [ this_7am, 24*60*60*1000 ] }

db.test.find( { $expr: { $and: [ { $gte: [ "$dt", last_7am ] }, { $lte: [ "$dt", this_7am ] } ] } } )

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

I also would recommend the Moment.js library, my style would be this:

db.company.find({ 
  "createdAt" : { 
    $lt: moment().startOf('day').add(7, "hours").toDate(),
    $gte: moment().startOf('day').add(7, "hours").subtract(1, "day").toDate()
  }   
})

You can do it also natively, however you have to type a bit more:

db.company.find({
   "createdAt": {
      $lt:
         {
            $dateFromParts: {
               'year': { year: new Date() },
               'month': { month: new Date() },
               'day': { $dayOfMonth: new Date() },
               'hour': "7"
            }
         },
      $gte:
         {
            $add: [
               $dateFromParts: {
                  'year': { year: new Date() },
                  'month': { month: new Date() },
                  'day': { $dayOfMonth: new Date() },
                  'hour': "7"
               },
               -1000 * 60 * 60 * 24
            ]
         }
   }
})

Upvotes: 1

Ashh
Ashh

Reputation: 46441

You can use moment library

db.company.find({ 
  "createdAt" : { 
    $lt: moment("07:00:00", "hh:mm:ss").toDate(),
    $gte: moment("07:00:00", "hh:mm:ss").subtract(1, "days").toDate()
  }   
})

Upvotes: 2

Related Questions