title: Is it possible to retrieve a 'time span' from a MongoDB query, using the timestamp within an ObjectId?
body: <p>We have a basic enquiry management tool that we're using to track some website enquiries in our administration suite, and we're using the ObjectId of each document in our enquiries collection to sort the enquiries by the date they were added.</p>
  
  <pre class="lang-js prettyprint-override"><code>{
      "_id" :      ObjectId("53a007db144ff47be1000003"),
      "comments" : "This is a test enquiry. Please ignore. We'll delete it shortly.",
      "customer" : {
          "name" :      "Test Enquiry",
          "email" :     "test@test.com",
          "telephone" : "07890123456",
          "mobile" :    "07890123456",
          "quote" :     false,
          "valuation" : false
      },
      "site" : [],
      "test" : true,
      "updates" : [ 
          {
              "_id" :          ObjectId("53a007db144ff47be1000001"),
              "status" :       "New",
              "status_id" :    ObjectId("537de7c3a5e6e668ffc2335c"),
              "status_index" : 100,
              "substatus" :    "New Web Enquiry",
              "substatus_id" : ObjectId("5396bb9fa5e6e668ffc23388"),
              "notes" :        "New enquiry received from website.",
          }, 
          {
              "_id" :          ObjectId("53a80c977d299cfe91bacf81"),
              "status" :       "New",
              "status_id" :    ObjectId("537de7c3a5e6e668ffc2335c"),
              "status_index" : 100,
              "substatus" :    "Attempted Contact",
              "substatus_id" : ObjectId("53a80e06a5e6e668ffc2339e"),
              "notes" :        "In this test, we pretend that we've not managed to get hold of the customer on the first attempt.",
          }, 
          {
              "_id" :          ObjectId("53a80e539b966b8da5c40c36"),
              "status" :       "Approved",
              "status_id" :    ObjectId("52e77a49d85e95f00ebf6c72"),
              "status_index" : 200,
              "substatus" :    "Enquiry Confirmed",
              "substatus_id" : ObjectId("53901f1ba5e6e668ffc23372"),
              "notes" :        "In this test, we pretend that we've got hold of the customer after failing to contact them on the first attempt.",
          }
      ]
  }
  </code></pre>
  
  <p>Within each enquiry is an updates array of objects which also have an ObjectId as their main identity field. We're using an <code>$unwind</code> and <code>$group</code> aggregation to pull the first and latest updates, as well as the count of updates, making sure we only take enquiries where there have been more than one update (as one is automatically inserted when the enquiry is made):</p>
  
  <pre class="lang-js prettyprint-override"><code>db.enquiries.aggregate([
      {
          $match: {
              "test": true
          }
      },
      {
          $unwind: "$updates"
      },
      {
          $group: {
              "_id": "$_id",
              "latest_update_id": {
                  $last: "$updates._id"
              },
              "first_update_id": {
                  $first: "$updates._id"
              },
              "update_count": {
                  $sum: 1
              }
          }
      },
      {
          $match: {
              "update_count": {
                  $gt: 1
              }
          }
      }
  ])
  </code></pre>
  
  <p>This results in the following output:</p>
  
  <pre class="lang-js prettyprint-override"><code>{
      "result" : [ 
          {
              "_id" : ObjectId("53a295ad122ea80200000005"),
              "latest_update_id" : ObjectId("53a80bdc7d299cfe91bacf7e"),
              "first_update_id" : ObjectId("53a295ad122ea80200000003"),
              "update_count" : 2
          }, 
          {
              "_id" : ObjectId("53a007db144ff47be1000003"),
              "latest_update_id" : ObjectId("53a80e539b966b8da5c40c36"),
              "first_update_id" : ObjectId("53a007db144ff47be1000001"),
              "update_count" : 3
          }
      ],
      "ok" : 1
  }
  </code></pre>
  
  <p>This is then passed through to our code (node.js, in this case) where we perform a few operations on it and then present some information on our dashboard.</p>
  
  <p>Ideally, I'd like to add another <code>$group</code> pipeline aggregation to the query which would subtract the timestamp of <code>first_update_id</code> from the timestamp of <code>latest_update_id</code> to give us a timespan, which we could then use <code>$avg</code> on.</p>
  
  <p>Can anyone tell me if this is possible? (Thank you!)</p>