Query to get last X minutes data with Mongodb

Question

I'm trying to query my db that have this document format:

{
  "_id" : ObjectId("520b8b3f8bd94741bf006033"),
  "value" : 0.664,
  "timestamp" : ISODate("2013-08-14T13:48:35Z"),
  "cr" : ISODate("2013-08-14T13:50:55.834Z")
}

I can get the last records from a datetime with this query:

db.mycol.find({timestamp:{$gt: ISODate("2013-08-14T13:48:00Z")}}).sort({x:1});

But I'm trying to get a set with the value fields and timestamps from 18 minutes ago.

See this [question](http://stackoverflow.com/questions/1197928/how-to-add-30-minutes-to-a-javascript-date-object) for how to add minutes to a Date, you should be able to use this to take 18 minutes from your date. — Alistair Nelson, Aug 14 '13 at 14:23

score 73 · Accepted Answer · answered Aug 14 '13 at 14:46

For the 18 minutes part, that's not really about MongoDB, but about JavaScript and what's available in the mongo shell:

query = {
    timestamp: { // 18 minutes ago (from now)
        $gt: new Date(ISODate().getTime() - 1000 * 60 * 18)
    }
}

Works in the mongo shell, but using Mongo drivers for other languages would be really different.

To "project" over a smaller schema with both values and timestamps:

projection = {
    _id: 0,
    value: 1,
    timestamp: 1,
}

Applying both:

db.mycol.find(query, projection).sort({timestamp: 1});

Well, that's still not a "set" since there might be duplicates. To get rid of them you can use the $group from the aggregation framework:

db.mycol.aggregate([
    {$match: query},
    {$group: {
        _id: {
            value: "$value",
            timestamp: "$timestamp",
        }
    }},
    {$project: {
        value: "$_id.value",
        timestamp: "$_id.timestamp",
    }},
    {$sort: {timestamp: 1}},
])

if you need a query that works outside the mongo shell, you can't use the above answer. You'll need to project a time different field: `time_diff: { $subtract: [ Date() - "$time" ] }` as part of the aggregation and then match against: `time_diff: { $lte: "$time_diff" }`. — Guy, Nov 23 '17 at 05:57

score 12 · Answer 2 · answered Feb 18 '20 at 21:49

12

You could also do below

  db.getCollection('collectionName').find({timestamp : {$gte: new Date().getTime()-(60*60*1000) } } )

The above query ll give you records of timestamp b/w now and 60 mins. if you like more then 60 mins - say 2 hrs you could change expression to (2*60*60*1000) for 30 mins (30*60*1000)

answered Feb 18 '20 at 21:49

Pravin Bansal

4,315
1
28
19

Will it get records below 10 mins from current time? – Navi Aug 10 '20 at 14:57
1

below query will give records of last 10 mins. from current time. db.getCollection('collectionName').find({timestamp : {$gte: new Date().getTime()-(10*60*1000) } } ) – Pravin Bansal Aug 12 '20 at 18:13
Thank you @Pravin – Navi Aug 13 '20 at 15:29

score 4 · Answer 3 · answered Dec 07 '21 at 19:37

Starting in Mongo 5, you can use $dateSubtract:

// { date: ISODate("2021-12-05T20:32:56Z") } <= 5  minutes ago
// { date: ISODate("2021-12-05T20:07:56Z") } <= 25 minutes ago (older than 18 minutes)
db.collection.aggregate([
  { $match: {
    $expr: {
      $gt: [
        "$date",
        { $dateSubtract: { startDate: "$$NOW", unit: "minute", amount: 18 } }
      ]
    }
  }}
])
// { date: ISODate("2021-12-05T20:32:56Z") } <= 5 minutes ago

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

score 2 · Answer 4 · answered Mar 11 '19 at 10:50

you can access the data of current timestamp from mongodb using nodejs

 const collection1 = dbo.collection('customers');
    var dateq = new Date();
      collection1.find({    "Timestamp" : { $gt: new Date(dateq.getTime() - 6000)}  
    }).toArray(function(err , docs){   
    console.log(docs);
    }

code end

Goku · Answer 5 · 2013-08-14T14:47:41.783

1

Wow, thanks to @Alistair_Nelson I was able to get the data from n minutes ago, for example to get the last 18 minutes from ISODate("2013-08-14T14:00:00Z"):

db.mycol.find({timestamp:{$gt: new Date(ISODate("2013-08-14T14:00:00Z")-18*60000)}})

To get only the fields I need:

db.mycol.find({timestamp:{$gt: new Date(ISODate("2013-08-14T14:00:00Z")-18*60000)}},{value:1,timestamp:1, _id:0})

edited Aug 14 '13 at 14:47

answered Aug 14 '13 at 14:42

Goku

1,750
5
23
35

score 0 · Answer 6 · answered Feb 20 '23 at 11:37

0

const xMins = 10;

db.mycol.find({ timestamp: { $gt: new Date(Date.now() - 1000 * 60 * xMins) } }).sort({ x: 1 });

answered Feb 20 '23 at 11:37

Cherif KAOUA

834
12
21

Query to get last X minutes data with Mongodb

6 Answers6

Linked