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

You cannot do that. The JavaScript functions you are using evaluate "outside" and "before" the pipeline content is sent to the server. You cannot use arbitrary code on elements during pipeline execution. You will either need to work with the timestamp values to determine "dayOfYear" ( very complex and I'm "fairly" sure the aggregation framework does not have the necessary math operators to handle it ). Or otherwise convert your collection to use Date types instead. But you cannot cast this way inside pipeline execution. — Neil Lunn, Jan 27 '15 at 07:02
If my mongodb client is c++, how can I make a json value which has a time value, when the json is sent to mongodb, the date value will be automatically saved as a ISODate value ? how can I accomplish it? — user3172936, Jan 28 '15 at 06:59
The driver library will have a date object type. If your not sure how to use it then post another question. What you are trying to do here is not possible as per my explanation. — Neil Lunn, Jan 28 '15 at 07:03

score 4 · Answer 1 · answered Jul 27 '18 at 21:28

4

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

answered Jul 27 '18 at 21:28

Ashh

44,693
14
105
132

score 3 · Answer 2 · answered Jan 31 '18 at 18:12

3

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

answered Jan 31 '18 at 18:12

prog.Dusan

1,324
2
12
25

Timestamp are 64bit numbers of seconds in low & high. Until 4.0 $add doesn't allow Timestamp data. – 0zkr PM Feb 11 '19 at 17:09

score 2 · Answer 3 · answered Jan 27 '15 at 07:22

2

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.

answered Jan 27 '15 at 07:22

Larry Battle

9,008
4
41
55

score 0 · Answer 4 · answered Mar 14 '17 at 20:29

MongoDB offers no easy way to do this, unfortunatelly.

The way I solved it was using the map function to inject a date field into the result.

First, setup a date conversion function, like the one below:

function toDateStr(ts) {
    let dataF = new Date();   dataF.setTime(ts);
    let strDataF = dataF.toLocaleString();
    return strDataF;
}

Then, in your aggregate, just call this function on each document and insert a new date field. Like so:

db.Collection.aggregate([
     {
       $project: {
            timestamp: 1
          }
      },
      {
          $match: { /* something */
          }
      }
   ]
).map(function(doc) {
    doc['date'] = toDateStr(doc.timestamp);
    return doc
})

how to convert timestamp to ISO date in mongodb aggregate

4 Answers4