title: Getting unix timestamp in seconds out of MongoDB ISODate during aggregation
body: <p>I was searching for this one but I couldn't find anything useful to solve my case. What I want is to get the unix timestamp in seconds out of MongoDB ISODate during aggregation. The problem is that I can get the timestamp out of ISODate but it's in milliseconds. So I would need to cut out those milliseconds. What I've tried is:</p>
  
  <pre><code>&gt; db.data.aggregate([
      {$match: {dt:2}}, 
      {$project: {timestamp: {$concat: [{$substr: ["$md", 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
    ])
  </code></pre>
  
  <p>As you can see I'm trying to get the timestamp out of 'md' var and also concatenate this timestamp with '01' and the 'id' number. The above code gives:</p>
  
  <pre><code>{
      "_id" : ObjectId("52f8fc693890fc270d8b456b"),
      "timestamp" : "2014-02-10T16:20:56011141"
  }
  </code></pre>
  
  <p>Then I improved the command with:</p>
  
  <pre><code>&gt; db.data.aggregate([
      {$match: {dt:2}},
      {$project: {timestamp: {$concat: [{$substr: [{$subtract: ["$md", new Date('1970-01-01')]}, 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
    ])
  </code></pre>
  
  <p>Now I get:</p>
  
  <pre><code>{
      "_id" : ObjectId("52f8fc693890fc270d8b456b"),
      "timestamp" : "1392049256000011141"
  }
  </code></pre>
  
  <p>What I really need is 1392049256011141 so without the 3 extra 000. I tried with $subtract:</p>
  
  <pre><code>&gt; db.data.aggregate([
      {$match: {dt:2}}, 
      {$project: {timestamp: {$concat: [{$substr: [{$divide: [{$subtract: ["$md", new Date('1970-01-01')]}, 1000]}, 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
    ])
  </code></pre>
  
  <p>What I get is:</p>
  
  <pre><code>{
      "_id" : ObjectId("52f8fc693890fc270d8b456b"),
      "timestamp" : "1.39205e+009011141"
  }
  </code></pre>
  
  <p>Not exactly what I would expect from the command. Unfortunately the $substr operator doesn't allow negative length. Does anyone have any other solution?</p>