CODEWITHSUNDEEP
mongodbaggregation-framework
Bazinga777
Bazinga777

Reputation: 5281

Get last inserted item per day

Is there a feature in mongodb that I can use to get the last inserted item per day ? I have a collection where I need to get the last inserted item per day, the data is grouped on an hourly basis like in the structure below.

{
   timestamp: 2017-05-04T09:00:00.000+0000,
   data: {}
},
{
   timestamp: 2017-05-04T10:00:00.000+0000,
   data: {}
}

I thought about using a projection but I am not quite sure how I could do this.

Edit: Also, since mongodb stores data in UTC, I would like to account for the offset as well.

Upvotes: 0

Views: 59

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

You can $sort and use $last for the item, with rounding out the grouping key to each day:

db.collection.aggregate([
  { "$sort": { "timestamp": 1 } },
  { "$group": {
     "_id": {
       "$add": [
         { "$subtract": [
           { "$subtract": [ "$timestamp", new Date(0) ] },
           { "$mod": [
             { "$subtract": [ "$timestamp", new Date(0) ] },
             1000 * 60 * 60 * 24
           ]}
         ]},
         new Date(0)
       ]
     },
     "lastDoc": { "$last": "$$ROOT" }
  }}
])

So the sort makes things appear in order, and then the grouping _id is rounded for each day by some date math. You subtract the epoch date from the current date to make it a number. Use the modulus to round to a day, then add the epoch date to the number to return a Date.

So stepping through the math we have getting the timestamp value from the date with the $subract line. We do this a couple of times:

{ "$subtract": [ "$timestamp", new Date(0) ] }

// Is roughly internally like
ISODate("2017-06-06T10:44:37.627Z") - ISODate("1970-01-01T00:00:00Z")
1496745877627

Then there is the modulo with $mod which when applied to the numeric value returns the difference. The 1000 milliseconds * 60 seconds * 60 * minutes * 24 hours gives the other argument:

 { "$mod": [
   { "$subtract": [ "$timestamp", new Date(0) ] },
   1000 * 60 * 60 * 24
 ]}

 // Equivalent to 
 1496745877627 % (1000 * 60 * 60 * 24)
 38677627

Then there is the wrapping $subtract of the two numbers:

 { "$subtract": [
   { "$subtract": [ "$timestamp", new Date(0) ] },
   { "$mod": [
     { "$subtract": [ "$timestamp", new Date(0) ] },
     1000 * 60 * 60 * 24
   ]}
 ]}

 // Subtract "difference" of the modulo to a day
 // from the milliseconds value of the current date
 1496745877627 - 38677627

 1496707200000

Then add back to the epoch date value to create a date rounded to the current day, which to the aggregation pipeline basically looks like providing the millisecond value to the constructor:

new Date(1496707200000)
ISODate("2017-06-06T00:00:00Z")

Which takes the timestamp value and subrtacts out the difference of the divisor from "one day" and ends up at the time at the "start of day".

Just using $$ROOT here to represent the whole document. But any document path provided to $last here provides the result.

Upvotes: 1

Related Questions