CODEWITHSUNDEEP
node.jsmongodbmongoose
Umapathi
Umapathi

Reputation: 568

How to find last record of each day in mongodb using given timestamps?

I'm using MongoDB to store data, every 15 mins once one new record will be inserted into DB.So I want to download the report using the given timestamps. If I select the Friday, January 1, 2021 12:00:00 AM(1609459200000) timestamp to Sunday, January 3, 2021 11:59:59 PM(1609718399000) timestamp I should get only 3 records as a response.

db.vehicle.aggregate( [ { "$match": { "time": {"$gte":1609459200000,"$lte":1609718399000 }} }, { "$sort": { "time": 1 } }, { $group: { _id: "$speed", time: { $last: "$time" } } } ] );

I tried the above query but it's giving all the records

Thank you.

Example DB Record:

// Day 1
{
   "_id":"100",
   "vehical":"train_1",
   "speed":"100kmh",
   "time":1609459200000,
   "starting_point":12
},
{
   "_id":"101",
   "vehical":"train_1",
   "speed":"120kmh",
   "time":1609460100000,
   "starting_point":13
},
{
   "_id":"102",
   "vehical":"train_1",
   "speed":"125kmh",
   "time":1609461000000,
   "starting_point":14
},
// Day 2
{
   "_id":"201",
   "vehical":"train_1",
   "speed":"100kmh",
   "time":1609545600000,
   "starting_point":15
},
{
   "_id":"202",
   "vehical":"train_1",
   "speed":"130kmh",
   "time":1609546500000,
   "starting_point":16
},
// Day 3
{
   "_id":"301",
   "vehical":"train_1",
   "speed":"100kmh",
   "time":1609632000000,
   "starting_point":20
},
{
   "_id":"302",
   "vehical":"train_1",
   "speed":"140kmh",
   "time":1609632900000,
   "starting_point":21
}

Response:

// Day 1
{
   "_id":"102",
   "vehical":"train_1",
   "speed":"125kmh",
   "time":1609461000000,
   "starting_point":14
},
// Day 2
{
   "_id":"202",
   "vehical":"train_1",
   "speed":"130kmh",
   "time":1609546500000,
   "starting_point":16
},
// Day 3
{
   "_id":"302",
   "vehical":"train_1",
   "speed":"140kmh",
   "time":1609632900000,
   "starting_point":21
}

Upvotes: 2

Views: 2582

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

Try this one:

db.vehicle.aggregate([
   { $match: { time: { $gte: 1609459200000, $lte: 1609718399000 } } },
   { $set: { timestamp: { $toDate: "$time" } } },
   { $sort: { time: 1 } },
   {
      $group: {
         _id: {
            $dateFromParts: {
               year: { $year: "$timestamp" },
               month: { $month: "$timestamp" },
               day: { $dayOfMonth: "$timestamp" }
            }
         },
         last: { $last: "$$ROOT" }
      }
   },
   { $replaceRoot: { newRoot: "$last" } }
]);

Upvotes: 3

Kai
Kai

Reputation: 19

Someone else posted a similar question 7 months ago. This might answer your question: How to get last document of each day in MongoDB collection?

If that doesn't work for you and you know that the last record of the day will always be generated at the same time, you should be able to build a query with $hour and $minute.

https://docs.mongodb.com/manual/reference/operator/aggregation/hour/
https://docs.mongodb.com/manual/reference/operator/aggregation/minute/

Upvotes: -1

Related Questions