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

Question

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
}

Why do you store date/time values as number? Use proper `Date` objects. — Wernfried Domscheit, Jan 27 '21 at 08:11
IoT devices will give date/time data as a timestamp so data is storing as a number, I posted here a sample data object — Umapathi, Jan 27 '21 at 08:17

score 3 · Accepted Answer · answered Jan 27 '21 at 09:42

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

score -1 · Answer 2 · answered Jan 27 '21 at 02:49

-1

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/

answered Jan 27 '21 at 02:49

Kai

19
3

Did you try using $hour and $minute, the accepted answer in the other StackOverflow question, or both? It would be helpful if you included the queries you were using in your question (at least the relevant parts). – Kai Jan 27 '21 at 03:58

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

2 Answers2