Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find all documents within last n days

Tags:

mongodb

My daily collection has documents like:

.. { "date" : ISODate("2013-01-03T00:00:00Z"), "vid" : "ED", "san" : 7046.25, "izm" : 1243.96 } { "date" : ISODate("2013-01-03T00:00:00Z"), "vid" : "UA", "san" : 0, "izm" : 0 } { "date" : ISODate("2013-01-03T00:00:00Z"), "vid" : "PAL", "san" : 0, "izm" : 169.9 } { "date" : ISODate("2013-01-03T00:00:00Z"), "vid" : "PAL", "san" : 0, "izm" : 0 } { "date" : ISODate("2013-01-03T00:00:00Z"), "vid" : "CTA_TR", "san" : 0, "izm" : 0 } { "date" : ISODate("2013-01-04T00:00:00Z"), "vid" : "CAD", "san" : 0, "izm" : 169.9 } { "date" : ISODate("2013-01-04T00:00:00Z"), "vid" : "INT", "san" : 0, "izm" : 169.9 } ... 

I left off _id field to spare the space here. My task is to "fetch all documents within last 15 days". As you can see I need somehow to:

  1. Get 15 unique dates. The newest one should be taken as the newest document in collection (what I mean that it isn't necessary the today's date, it's just the latest one in collection based on date field), and the oldest.. well, maybe it's not necessary to strictly define the oldest day in query, what I need is some kind of top15 starting from the newest day, if you know what I mean. Like 15 unique days.
  2. db.daily.find() all documents, that have date field in that range of 15 days.

In the result, I should see all documents within 15 days starting from the newest in collection.

like image 721
kaytrance Avatar asked Mar 22 '13 14:03

kaytrance


People also ask

What is $GTE in MongoDB?

$gte selects the documents where the value of the field is greater than or equal to (i.e. >= ) a specified value (e.g. value .) For most data types, comparison operators only perform comparisons on fields where the BSON type matches the query value's type.

How do I find most recent files in MongoDB?

To get last inserted document, use sort() along with limit(1).

What is $EXPR in MongoDB?

$expr can build query expressions that compare fields from the same document in a $match stage. If the $match stage is part of a $lookup stage, $expr can compare fields using let variables. See Perform Multiple Joins and a Correlated Subquery with $lookup for an example.

What is $natural in MongoDB?

The ({$natural − 1}) works like LIFO(LAST IN FIRST OUT), that means last inserted document will be shown first. Let us create a collection with documents − > db.


2 Answers

I just tested the following query against your data sample and it worked perfectly:

db.datecol.find( {     "date":      {         $gte: new Date((new Date().getTime() - (15 * 24 * 60 * 60 * 1000)))     } } ).sort({ "date": -1 }) 
like image 175
Moacy Barros Avatar answered Sep 18 '22 20:09

Moacy Barros


Starting in Mongo 5, it's a nice use case for the $dateSubtract operator:

// { date: ISODate("2021-12-05") } // { date: ISODate("2021-12-02") } // { date: ISODate("2021-12-02") } // { date: ISODate("2021-11-28") } <= older than 5 days db.collection.aggregate([   { $match: {     $expr: {       $gt: [         "$date",         { $dateSubtract: { startDate: "$$NOW", unit: "day", amount: 5 } }       ]     }   }} ]) // { date: ISODate("2021-12-05") } // { date: ISODate("2021-12-02") } // { date: ISODate("2021-12-02") } 

With $dateSubtract, we create the oldest date after which we keep documents, by subtracting 5 (amount) "days" (unit) out of the current date $$NOW (startDate).

And you can obviously add a $sort stage to sort documents by date.

like image 45
Xavier Guihot Avatar answered Sep 19 '22 20:09

Xavier Guihot