Getting unix timestamp in seconds out of MongoDB ISODate during aggregation

Question

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:

> db.data.aggregate([
    {$match: {dt:2}}, 
    {$project: {timestamp: {$concat: [{$substr: ["$md", 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
  ])

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:

{
    "_id" : ObjectId("52f8fc693890fc270d8b456b"),
    "timestamp" : "2014-02-10T16:20:56011141"
}

Then I improved the command with:

> db.data.aggregate([
    {$match: {dt:2}},
    {$project: {timestamp: {$concat: [{$substr: [{$subtract: ["$md", new Date('1970-01-01')]}, 0, -1]}, '01', {$substr: ["$id", 0, -1]}]}}}
  ])

Now I get:

{
    "_id" : ObjectId("52f8fc693890fc270d8b456b"),
    "timestamp" : "1392049256000011141"
}

What I really need is 1392049256011141 so without the 3 extra 000. I tried with $subtract:

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

What I get is:

{
    "_id" : ObjectId("52f8fc693890fc270d8b456b"),
    "timestamp" : "1.39205e+009011141"
}

Not exactly what I would expect from the command. Unfortunately the $substr operator doesn't allow negative length. Does anyone have any other solution?

Answer 1

I'm not sure why you think you need the value in seconds rather than milliseconds as generally both forms are valid and within most language implementations the milliseconds is actually preferred. But generally speaking, trying to coerce this into a string is the wrong way to go around this, and generally you just do the math:

db.data.aggregate([
  { "$project": {
    "timestamp": {
      "$subtract": [
        { "$divide": [
            { "$subtract": [ "$md", new Date("1970-01-01") ] },
            1000
        ]},
        { "$mod": [
          { "$divide": [
              { "$subtract": [ "$md", new Date("1970-01-01") ] },
              1000
          ]},
          1
        ]}
      ]
    }
  }}
])

Which returns you an epoch timestamp in seconds. Basically derived from when one BSON date object is subtracted from another one then the result is the time interval in milliseconds. Using the initial epoch date of "1970-01-01" results in essentially extracting the milliseconds value from the current date value. The $divide operator essentially takes off the milliseconds portion and the $mod does the modulo to implement rounding.

Really though you are better off doing the work in the native language for your application as all BSON dates will be returned there as a native "date/datetime" type where you can extract the timestamp value. Consider the JavaScript basics in the shell:

var date = new Date()
( date.valueOf() / 1000 ) - ( ( date.valueOf() / 1000 ) % 1 )

Typically with aggregation you want to do this sort of "math" to a timestamp value for use in something like aggregating values within a time period such as a day. There are date operators available to the aggregation framework, but you can also do it the date math way:

db.data.aggregate([
    { "$group": {
        "_id": {
          "$subtract": [
              { "$subtract": [ "$md", new Date("1970-01-01") ] },
              { "$mod": [
                  { "$subtract": [ "$md", new Date("1970-01-01") ] },
                  1000 * 60 * 60 * 24
              ]}
          ]
        },
        "count": { "$sum": 1 }
    }}
])

That form would be more typical to emit a timestamp rounded to a day, and aggregate the results within those intervals.

So your purposing of the aggregation framework just to extract a timestamp does not seem to be the best usage or indeed it should not be necessary to convert this to seconds rather than milliseconds. In your application code is where I think you should be doing that unless of course you actually want results for intervals of time where you can apply the date math as shown.

The methods are there, but unless you are actually aggregating then this would be the worst performance option for your application. Do the conversion in code instead.