Pulling yesterdays documents based on date column

[Vidya_Swar]

Hi,
Am newbie to MongoDB. Can you help me pull all the documents that were recorded in a collection yesterday dynamically?

Am trying this in MongoDB Compass, aggregation pipeline where am trying to retrieve all the documents that have date_added > yesterday and date_added < today (after truncating time).

$and :
[ {“date_added”: {“$gte”: [new Date((new Date().getTime() - (24 * 60 * 60 *1000)))]}},
{“date_added”: {“$lt”: [{$dateToString: {format: “%d.%m.%Y”, date: (new Date()) } }]}}
]

Is this the right way to pull yesterdays data? or is there a better way to handle this?
Thanks in Advance.

[R_V]

Hi @Vidya_Swar , welcome to the community.

Have you tried this way, getting yesterday’s date and setting start time from midnight till 23:59 hrs.

const startTime = new Date(new Date(new Date().setDate(new Date().getDate()-1)).setHours(00,00,00,00)).toISOString();

const endTime = new Date(new Date(new Date().setDate(new Date().getDate()-1)).setHours(23,59,59,999)).toISOString();

db.collection.aggregate([
 { 
  $match: 
   {
    "date_added": 
         {
          $gte: startTime,
          $lte: endTime 
          }
    }
  }
])

Hoping it’s useful.
Regards

[Vidya_Swar]

Thanks so much for your time @R_V

I did a minor change to your code , but it neither returns any documents nor shows any errors.

const startTime = new Date(new Date(new Date().setDate(new Date().getDate()-1)).setHours(0,0,0,0)).toISOString();
const endTime = new Date(new Date(new Date().setDate(new Date().getDate()-1)).setHours(23,59,59,999)).toISOString();
db.mycollection2.aggregate([
 { 
  $match: 
   {
    "date_added": 
         {
          $gte: startTime,
          $lte: endTime 
          }
    }
  }
]);

I am a SQL developer and was looking for a equivalent query to pull yesterdays records:
SELECT * FROM t1
WHERE date_added > CONVERT(DATE, GetDate()-1)
AND date_added < CONVERT(DATE, GetDate())

Appreciate any help or advice!

[Tarun_Gaur]

Hello @Vidya_Swar ,

Welcome to The MongoDB Community Forums!

Try using below code and update it as per your requirements.

db.mycollection2.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          { $gte: [ "$date_added", { $toDate: { $subtract: [ ISODate(), 86400000 ] } } ] },
          { $lt: [ "$date_added", { $toDate: { $subtract: [ ISODate(), 0 ] } } ] }
        ]
      }
    }
  }
])

Note: Please test your aggregation pipeline as per your requirements and collection documents. Also, try testing the edge cases of your use-cases to make sure you don’t miss anything.

Regards,
Tarun