Converting string to date in mongodb

Question

Is there a way to convert string to date using custom format using mongodb shell

I am trying to convert "21/May/2012:16:35:33 -0400" to date,

Is there a way to pass DateFormatter or something to Date.parse(...) or ISODate(....) method?

Answer 1

Using MongoDB 4.0 and newer

The $toDate operator will convert the value to a date. If the value cannot be converted to a date, $toDate errors. If the value is null or missing, $toDate returns null:

You can use it within an aggregate pipeline as follows:

db.collection.aggregate([
    { "$addFields": {
        "created_at": {
            "$toDate": "$created_at"
        }
    } }
])

The above is equivalent to using the $convert operator as follows:

db.collection.aggregate([
    { "$addFields": {
        "created_at": { 
            "$convert": { 
                "input": "$created_at", 
                "to": "date" 
            } 
        }
    } }
])

---

Using MongoDB 3.6 and newer

You cab also use the $dateFromString operator which converts the date/time string to a date object and has options for specifying the date format as well as the timezone:

db.collection.aggregate([
    { "$addFields": {
        "created_at": { 
            "$dateFromString": { 
                "dateString": "$created_at",
                "format": "%m-%d-%Y" /* <-- option available only in version 4.0. and newer */
            } 
        }
    } }
])

---

Using MongoDB versions >= 2.6 and < 3.2

If MongoDB version does not have the native operators that do the conversion, you would need to manually iterate the cursor returned by the find() method by either using the forEach() method or the cursor method next() to access the documents. Withing the loop, convert the field to an ISODate object and then update the field using the $set operator, as in the following example where the field is called created_at and currently holds the date in string format:

var cursor = db.collection.find({"created_at": {"$exists": true, "$type": 2 }}); 
while (cursor.hasNext()) { 
    var doc = cursor.next(); 
    db.collection.update(
        {"_id" : doc._id}, 
        {"$set" : {"created_at" : new ISODate(doc.created_at)}}
    ) 
};

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 1000 which gives you a better performance as you are not sending every request to the server, just once in every 1000 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 created_at fields to date fields:

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

db.collection.find({"created_at": {"$exists": true, "$type": 2 }}).forEach(function (doc) {
    var newDate = new ISODate(doc.created_at);
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "created_at": newDate}
    });

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

---

Using MongoDB 3.2

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 = [],
    cursor = db.collection.find({"created_at": {"$exists": true, "$type": 2 }});

cursor.forEach(function (doc) { 
    var newDate = new ISODate(doc.created_at);
    bulkOps.push(         
        { 
            "updateOne": { 
                "filter": { "_id": doc._id } ,              
                "update": { "$set": { "created_at": newDate } } 
            }         
        }           
    );

    if (bulkOps.length === 500) {
        db.collection.bulkWrite(bulkOps);
        bulkOps = [];
    }     
});

if (bulkOps.length > 0) db.collection.bulkWrite(bulkOps);

Answer 2

In my case I have succeed with the following solution for converting field ClockInTime from ClockTime collection from string to Date type:

db.ClockTime.find().forEach(function(doc) { 
    doc.ClockInTime=new Date(doc.ClockInTime);
    db.ClockTime.save(doc); 
    })

Answer 3

You can use the javascript in the second link provided by Ravi Khakhkhar or you are going to have to perform some string manipulation to convert your orginal string (as some of the special characters in your original format aren't being recognised as valid delimeters) but once you do that, you can use "new"

training:PRIMARY> Date()
Fri Jun 08 2012 13:53:03 GMT+0100 (IST)
training:PRIMARY> new Date()
ISODate("2012-06-08T12:53:06.831Z")

training:PRIMARY> var start = new Date("21/May/2012:16:35:33 -0400")        => doesn't work
training:PRIMARY> start
ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")

training:PRIMARY> var start = new Date("21 May 2012:16:35:33 -0400")        => doesn't work    
training:PRIMARY> start
ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")

training:PRIMARY> var start = new Date("21 May 2012 16:35:33 -0400")        => works
training:PRIMARY> start
ISODate("2012-05-21T20:35:33Z")

Here's some links that you may find useful (regarding modification of the data within the mongo shell) -

http://cookbook.mongodb.org/patterns/date_range/

http://www.mongodb.org/display/DOCS/Dates

http://www.mongodb.org/display/DOCS/Overview+-+The+MongoDB+Interactive+Shell

Answer 4

I had some strings in the MongoDB Stored wich had to be reformated to a proper and valid dateTime field in the mongodb.

here is my code for the special date format: "2014-03-12T09:14:19.5303017+01:00"

but you can easyly take this idea and write your own regex to parse the date formats:

// format: "2014-03-12T09:14:19.5303017+01:00"
var myregexp = /(....)-(..)-(..)T(..):(..):(..)\.(.+)([\+-])(..)/;

db.Product.find().forEach(function(doc) { 
   var matches = myregexp.exec(doc.metadata.insertTime);

   if myregexp.test(doc.metadata.insertTime)) {
       var offset = matches[9] * (matches[8] == "+" ? 1 : -1);
       var hours = matches[4]-(-offset)+1
       var date = new Date(matches[1], matches[2]-1, matches[3],hours, matches[5], matches[6], matches[7] / 10000.0)
       db.Product.update({_id : doc._id}, {$set : {"metadata.insertTime" : date}})
       print("succsessfully updated");
    } else {
        print("not updated");
    }
})

Answer 5

I find it easiest to use the $toDate operator in combination with updateMany.

db.collection.updateMany({}, [{$set: {createdAt: {$toDate: "$createdAt"}}}]);

Answer 6

How about using a library like momentjs by writing a script like this:

[install_moment.js]
function get_moment(){
    // shim to get UMD module to load as CommonJS
    var module = {exports:{}};

    /* 
    copy your favorite UMD module (i.e. moment.js) here
    */

    return module.exports
}
//load the module generator into the stored procedures: 
db.system.js.save( {
        _id:"get_moment",
        value: get_moment,
    });

Then load the script at the command line like so:

> mongo install_moment.js

Finally, in your next mongo session, use it like so:

// LOAD STORED PROCEDURES
db.loadServerScripts();

// GET THE MOMENT MODULE
var moment = get_moment();

// parse a date-time string
var a = moment("23 Feb 1997 at 3:23 pm","DD MMM YYYY [at] hh:mm a");

// reformat the string as you wish:
a.format("[The] DDD['th day of] YYYY"): //"The 54'th day of 1997"