CODEWITHSUNDEEP
mongodbdate
noob
noob

Reputation: 6864

MongoDB String attribute to Date

I have a collection with around 500,0000 entries in MongoDB.

Each entry has an attribute Date in the following format: "Date" : "21/01/2005"

I'd like to know how I can convert it in such a way to Date format, so I can then index it (old-new) and query for entries by year.

I have tried:

db.collection.find().forEach(function(element){
  element.OrderDate = ISODate(element.OrderDate);
  db.collection.save(element);
})

But this just seems to change the Date attribute to today's date, along with time in the following format: "Date" : ISODate("2016-02-11T11:41:45.680Z")

Thank you in advance.

Upvotes: 1

Views: 375

Answers (1)

chridam
chridam

Reputation: 103455

Convert the field to the correct date object by spliting the string on the given delimiter. Use parseInt() to convert the strings into numbers, and the new Date() constructor builds a Date from those parts: the third part will be the year, the second part the month, and the first part the day. Since Date uses zero-based month numbers you have to subtract one from the month number.

The following demonstrates this approach:

var cursor = db.collection.find({"OrderDate": {"$exists": true, "$type": 2 }}); 
while (cursor.hasNext()) { 
    var doc = cursor.next(); 
    var parts = doc.OrderDate.split("/");
    var dt = new Date(
                parseInt(parts[2], 10), // year
                parseInt(parts[1], 10) - 1, // month
                parseInt(parts[0], 10) // day
            );
    db.collection.update(
        {"_id": doc._id}, 
        {"$set": {"OrderDate": dt}}
    ) 
};

For improved performance especially when dealing with large collections, take advantage of using the Bulk API for bulk updates as you will be sending the operations to the server in batches of say 500 which gives you a better performance as you are not sending every request to the server, just once in every 500 requests.

The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions >= 2.6 and < 3.2. It updates all the documents in the collection by changing the OrderDate fields to date fields:

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

db.collection.find({"OrderDate": {"$exists": true, "$type": 2 }}).forEach(function (doc) {
    var parts = doc.OrderDate.split("/");
    var dt = new Date(
                parseInt(parts[2], 10), // year
                parseInt(parts[1], 10) - 1, // month
                parseInt(parts[0], 10) // day
            );
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "OrderDate": dt}
    });

    counter++;
    if (counter % 500 == 0) {
        bulk.execute(); // Execute per 500 operations and re-initialize every 500 update statements
        bulk = db.collection.initializeUnorderedBulkOp();
    }
})
// Clean up remaining operations in queue
if (counter % 500 != 0) { bulk.execute(); }

The next example applies to the new MongoDB version 3.2 which has since deprecated the Bulk API and provided a newer set of apis using bulkWrite():

var bulkOps = db.collection.find({"OrderDate": {"$exists": true, "$type": 2 }}).map(function (doc) { 
    var parts = doc.OrderDate.split("/");
    var dt = new Date(
                parseInt(parts[2], 10), // year
                parseInt(parts[1], 10) - 1, // month
                parseInt(parts[0], 10) // day
            );
    return { 
        "updateOne": { 
            "filter": { "_id": doc._id } ,              
            "update": { "$set": { "OrderDate": dt } } 
        }         
    };          
})

db.collection.bulkWrite(bulkOps);

Upvotes: 2

Related Questions