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

user_6787574 0
Viewed 1709

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
}
mongoose node.js mongodb
2 Answers

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" } }
]);
user_30272660
user_139234410
Related