Find all documents within last n days

Question

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:

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.
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.

score 88 · Accepted Answer · edited Sep 08 '17 at 08:49

88

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 })

edited Sep 08 '17 at 08:49

AshHimself

4,024
1
21
26

answered Apr 22 '13 at 01:33

Moacy Barros

1,869
13
9

Excellent, didn't know you can create vars inside mongoDB sentences – Ignacio Ara Nov 07 '18 at 08:32
3

If you choose to use the moment framework, then you can create the Date instance using: `moment().subtract(15, 'days').toDate()` – Edward Corrigall Jun 01 '20 at 21:00
@EdwardCorrigall Thank you so much for showing this with moment. – Abhijit Aug 28 '21 at 11:03
1

Using a hard-coded date (i.e. `$gte: new Date(2021,7,7)`) is _significantly_ more performant compared to the solution proposed in the answer. – Bataleon Oct 12 '21 at 14:36

score 8 · Answer 2 · answered Dec 05 '21 at 11:12

8

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.

answered Dec 05 '21 at 11:12

Xavier Guihot

54,987
21
291
190

2

Perfect answer. – arbob Mar 19 '22 at 12:28
Please note that using $expr with $dateSubstract will lead to table scan. $expr has limitation regarding indexes uses. Documentation states: "Indexes are not used for comparisons with more than one field path operand". But the query suggested by @Xavier will transform into a filter with an $and operator. Which I suspect prevent $expr from using any index. Tested this on our own database and I can confirm Moacy Barros answer is still the best choice, especially if you need indexes for query optimisation. – BenBenMushi Jul 26 '23 at 08:17
`the best choice` depends on the context in which a query is used. Not all contexts depend on a big collection or even require an index. But indeed, good warning for those specific contexts. – Xavier Guihot Jul 26 '23 at 08:39

score -1 · Answer 3 · edited Jul 02 '17 at 18:16

You need to run the distinct command to get all the unique dates. Below is the example. The "values" array has all the unique dates of the collection from which you need to retrieve the most recent 15 days on the client side

db.runCommand ( { distinct: 'datecol', key: 'date' } )
{
    "values" : [
       ISODate("2013-01-03T00:00:00Z"),
       ISODate("2013-01-04T00:00:00Z")
    ],
    "stats" : {
       "n" : 2,
       "nscanned" : 2,
       "nscannedObjects" : 2,
       "timems" : 0,
       "cursor" : "BasicCursor"
    },
    "ok" : 1
}

You then use the $in operator with the most recent 15 dates from step 1. Below is an example that finds all documents that belong to one of the mentioned two dates.

db.datecol.find({
  "date":{
     "$in":[
        new ISODate("2013-01-03T00:00:00Z"), 
        new ISODate("2013-01-04T00:00:00Z")
      ]
  }
})

Find all documents within last n days

3 Answers3

Linked

Related