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

User · Answer

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

User · Answer

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     gt  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

User · Answer

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 gt  Date   Fri Jun 08 2012 13 53 03 GMT 0100  IST  training PRIMARY gt  new Date   ISODate  2012-06-08T12 53 06 831Z    training PRIMARY gt  var start   new Date  21 May 2012 16 35 33 -0400            gt  doesn t work training PRIMARY gt  start ISODate  0NaN-NaN-NaNTNaN NaN NaNZ    training PRIMARY gt  var start   new Date  21 May 2012 16 35 33 -0400            gt  doesn t work     training PRIMARY gt  start ISODate  0NaN-NaN-NaNTNaN NaN NaNZ    training PRIMARY gt  var start   new Date  21 May 2012 16 35 33 -0400            gt  works training PRIMARY gt  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

User · Answer

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

User · Answer

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      lt -- option available only in version 4 0  and newer                                            Using MongoDB versions  gt   2 6 and  lt  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  gt   2 6 and  lt  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  gt  0  db collection bulkWrite bulkOps

[mongodb] Converting string to date in mongodb

Examples related to mongodb

Examples related to mongodb-query

Examples related to aggregation-framework