Mongo : How to convert all entries using a long timeStamp to an ISODate?

Question

I have a current Mongo database with the accumulated entries/fields

{
 name: "Fred Flintstone",
 age : 34,
 timeStamp : NumberLong(14283454353543)
}

{
 name: "Wilma Flintstone",
 age : 33,
 timeStamp : NumberLong(14283454359453)
}

And so on...

Question : I want to convert all entries in the database to their corresponding ISODate instead - How does one do this?

Desired Result :

{
 name: "Fred Flintstone",
 age : 34,
 timeStamp : ISODate("2015-07-20T14:50:32.389Z")
}

{
 name: "Wilma Flintstone",
 age : 33,
 timeStamp : ISODate("2015-07-20T14:50:32.389Z")
}

Things I've tried

 >db.myCollection.find().forEach(function (document) {
    document["timestamp"] = new Date(document["timestamp"])

    //Not sure how to update this document from here
    db.myCollection.update(document) //?
})

Miserable

Answer 1

Using the aggregation pipeline for update operations, simply run the following update operation:

db.myCollection.updateMany(
   { },
   [
      {  $set: {
         timeStamp: { 
            $toDate: '$timeStamp'
         }
      } },
   ]
])

---

With you initial attempt, you were almost there, you just need to call the save() method on the modified document to update it since the method uses either the insert or the update command. In the above instance, the document contains an _id fieldand thus the save() method is equivalent to an update() operation with the upsert option set to true and the query predicate on the _id field:

db.myCollection.find().snapshot().forEach(function (document) {
    document["timestamp"] = new Date(document["timestamp"]);
    db.myCollection.save(document)
})

The above is similar to explicitly calling the update() method as you had previously attempted:

db.myCollection.find().snapshot().forEach(function (document) {
    var date = new Date(document["timestamp"]);
    var query = { "_id": document["_id"] }, /* query predicate */
        update = { /* update document */
           "$set": { "timestamp": date }
        },
        options = { "upsert": true };         
  
    db.myCollection.update(query, update, options);
})

---

For relatively large collection sizes, your db performance will be slow and it's recommended to use mongo bulk updates for this:

MongoDB versions >= 2.6 and < 3.2:

var bulk = db.myCollection.initializeUnorderedBulkOp(),
    counter = 0;

db.myCollection.find({"timestamp": {"$not": {"$type": 9 }}}).forEach(function (doc) {    
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "timestamp": new Date(doc.timestamp") } 
    });

    counter++;
    if (counter % 1000 === 0) {
        // Execute per 1000 operations 
        bulk.execute(); 
        
        // re-initialize every 1000 update statements
        bulk = db.myCollection.initializeUnorderedBulkOp();
    }
})

// Clean up remaining operations in queue
if (counter % 1000 !== 0) bulk.execute(); 

MongoDB version 3.2 and newer:

var ops = [],
    cursor = db.myCollection.find({"timestamp": {"$not": {"$type": 9 }}});

cursor.forEach(function (doc) {     
    ops.push({ 
        "updateOne": { 
            "filter": { "_id": doc._id } ,              
            "update": { "$set": { "timestamp": new Date(doc.timestamp") } } 
        }         
    });

    if (ops.length === 1000) {
        db.myCollection.bulkWrite(ops);
        ops = [];
    }     
});

if (ops.length > 0) db.myCollection.bulkWrite(ops);

Lovesome

Answer 2

It seems that there are some cumbersome things happening in mongo when trying to instantiate Date objects from NumberLong values. Mainly becasue the NumberLong values are converted to wrong representations and the fallback to current date is used.

I was fighting 2 days with mongo and finally I found the solution. The key is to convert NumberLong to Double ... and pass double values to Date constructor.

Here is the solution that uses bulb operations and work for me ...

(lastIndexedTimestamp is the collection field that is migrated to ISODate and stored in lastIndexed field. A temporary collection is created, and it is renamed to the original value in the end.)

db.annotation.aggregate(    [
     { $project: { 
        _id: 1,
        lastIndexedTimestamp: 1,
        lastIndexed: { $add: [new Date(0), {$add: ["$lastIndexedTimestamp", 0]}]}
        }
    },
    { $out : "annotation_new" }
])

//drop annotation collection
db.annotation.drop();

//rename annotation_new to annotation
db.annotation_new.renameCollection("annotation");

Enchantment