Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting string to date in mongodb

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?

like image 679
user883257 Avatar asked Jun 08 '12 04:06

user883257


People also ask

How do I convert a string to a date?

Using strptime() , date and time in string format can be converted to datetime type. The first parameter is the string and the second is the date time format specifier. One advantage of converting to date format is one can select the month or date or time individually.

How do I create a date in MongoDB?

You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats: new Date("<YYYY-mm-dd>") returns the ISODate with the specified date.

Does MongoDB convert date to UTC?

MongoDB stores times in UTC by default, and will convert any local time representations into this form. Applications that must operate or report on some unmodified local time value may store the time zone alongside the UTC timestamp, and compute the original local time in their application logic.

What is $project in MongoDB?

The $project takes a document that can specify the inclusion of fields, the suppression of the _id field, the addition of new fields, and the resetting of the values of existing fields. Alternatively, you may specify the exclusion of fields. The $project specifications have the following forms: Form. Description.


1 Answers

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); 
like image 177
chridam Avatar answered Sep 22 '22 03:09

chridam