how to convert timestamp to ISO date in mongodb aggregate

Question

I am using node.js mongodb client. I have a timestamp value in a collection, now I want to use aggregate to convert timestamp to IOSDate, so I can compare it easily. But I do not know how to do it.

   var db = game.getDB();
    var coll = db.collection("LoginRecord");
    coll.aggregate([
        {
            $project: {
                "PT" : 1,
                "PID" : 1,
                "regDate" : new Date("$createTime"), //#####createTime is a timestamp, I want to convert it to IOSDate
                "loginDay" : { $dayOfYear : "$_serverDate"}
            }
        },
        {
            $group : {
                "_id" : "$loginDay",
                "logUsers" : { $addToSet: "$PID"}
            }
        },
        {
            $unwind : "$logUsers"
        },
        {
            "$group" : {
                "_id" : "$_id",
                "logCount" : { $sum: 1}
            }
        }
    ], function(err, res) {
        logger.info("aggregate res " + JSON.stringify(res));
    });

Solution 1

Mongodb 4.0 has introduced $toDate aggregation which simply convert timestamp to ISO date

db.collection.aggregate([
  { "$project": {
    "regDate": { "$toDate": "$createTime" }
  }}
])

You can try it here

Solution 2

As referred convert milliseconds to date in mongodb aggregation pipeline for group by?

You can do it as follows to convert milliseconds (timestamp) to date object:

"regDate": {
   $add: [ new Date(0), "$createTime" ]
}

Solution 3

Before using the aggregation framework, just query the collection and update the documents to ISODate date format. Mongodb timestamp objects for internal use only. So you should make this a permanently switch the type to ISODate. Link to warning.

Comments

[0zkr PM]

Timestamp are 64bit numbers of seconds in low & high. Until 4.0 $add doesn't allow Timestamp data.