Convert date from milliseconds to ISODate object

问题:

I am trying to aggregate records in a MongoDB colloection by hour and need to convert date stored as timestamp (milliseconds) to ISODate so that I can use aggregate framework's built-in date operators ($hour, $month, etc.)

Records are stored as

{ 
"data" : { "UserId" : "abc", "ProjId" : "xyz"}, 
"time" : NumberLong("1395140780706"),
"_id" : ObjectId("532828ac338ed9c33aa8eca7") 
} 

I am trying to use an aggregate query of following type:

db.events.aggregate(
    { 
       $match : { 
         "time" : { $gte : 1395186209804, $lte : 1395192902825 } 
       } 
    }, 
    { 
       $project : {
         _id : "$_id", 
         dt : {$concat : (Date("$time")).toString()} // need to project as ISODate
       } 
    },
    // process records further in $project or $group clause
)

which produces results of the form:

{
    "result" : [
        { 
            "_id" : ObjectId("5328da21fd207d9c3567d3ec"), 
            "dt" : "Fri Mar 21 2014 17:35:46 GMT-0400 (EDT)" 
        }, 
        { 
            "_id" : ObjectId("5328da21fd207d9c3567d3ed"), 
            "dt" : "Fri Mar 21 2014 17:35:46 GMT-0400 (EDT)" 
        }, 
            ... 
} 

I want to extract hour, day, month, and year from the date but since time is projected forward as string I am unable to use aggregate framework's built-in date operators ($hour, etc.).

How can I convert time from milliseconds to ISO date to do sometime like the following:

db.events.aggregate(
    {
        $match : { 
            "time" : { $gte : 1395186209804, $lte : 1395192902825 } 
        }
    },
    {
        $project : {
            _id : "$_id",
            dt : <ISO date from "$time">
        }
    },
    { 
        $project : {
            _id : "$_id",
            date : { 
                hour : {$hour : "$dt"} 
            }
        }
    }
)

回答1:

I assume there's no way to do it. Because aggregation framework is written in native code. not making use of the V8 engine. Thus everything of JavaScript is not gonna work within the framework (And that's also why aggregation framework runs much faster).
Map/Reduce is a way to work this out, but aggregation framework definitely got much better performance.

About Map/Reduce performance, read this thread.

Another way to work it out would be get a "raw" result from aggregation framework, put it into an JSON array. Then do the conversion by running JavaScript. Sort of like:

var results = db.events.aggregate(...);
reasult.forEach(function(data) {
    data.date = new Date(data.dateInMillionSeconds);
    // date is now stored in the "date" property
}

回答2:

Actually, it is possible, the trick is to add your milliseconds time to a zero-milliseconds Date() object using syntax similar to:

dt : {$add: [new Date(0), "$time"]}

I modified your aggregation from above to produce the result:

db.events.aggregate(
    {
        $project : {
            _id : "$_id",
            dt : {$add: [new Date(0), "$time"]}
        }
    },
    { 
        $project : {
            _id : "$_id",
            date : { 
                hour : {$hour : "$dt"} 
            }
        }
    }
);

The result is (with one entry of your sample data):

{
  "result": [
    {
      "_id": ObjectId("532828ac338ed9c33aa8eca7"),
      "date": {
        "hour": 11
      }
    }
  ],
  "ok": 1
}

回答3:

To return a valid BSON date all you need is a little date "maths" using the $add operator. You need to add new Date(0) to the timestamp. The new Date(0) represents the number of milliseconds since the Unix epoch (Jan 1, 1970) and is a shorthand for new Date("1970-01-01").

db.events.aggregate([
    { "$match": { "time": { "$gte" : 1395136209804, "$lte" : 1395192902825 } } },
    { "$project": { 
        "hour": { "$hour": { "$add": [ new Date(0), "$time" ] } }, 
        "day": { "$dayOfMonth":  { "$add": [ new Date(0), "$time" ] } },
        "month": { "$month": { "$add": [ new Date(0), "$time" ] } },
        "year": { "$year":  { "$add": [ new Date(0), "$time" ] } } 
    }} 
])

Which yields:

{
    "_id" : ObjectId("532828ac338ed9c33aa8eca7"),
    "hour" : 11,
    "day" : 18,
    "month" : 3,
    "year" : 2014
}

回答4:

use this if {$add: [new Date(0), "$time"]} function returning string type not an ISO date type

I use all of that option but still fail, because my new date from $project return a string type like '2000-11-2:xxxxxxx' not date type like ISO('2000-11-2:xxxxxxx') for anyone who have same problem with me use this.

db.events.aggregate(
    {
        $project : {
            _id : "$_id",
            dt : {$add: [new Date(0), "$time"]}
        }
    },
    { 
        $project : {
            _id : "$_id",
            "year": { $substr: [ "$dt", 0, 4 ] },
            "month": { $substr: [ "$dt", 5, 2] },
            "day": { $substr: [ "$dt", 8, 2 ] }
        }
    }
);

the result will be

 { _id: '59f940eaea87453b30f42cf5',
    year: '2017',
    month: '07',
    day: '04' 
},

you can get hours or minute if you want depending on which string you want to subset, then you can group that again according to same date,month or year