String Datatype to Date datatype conversion

Working with Data
1

Requirement: MongoDB Version 4.2 collections are holding data (imported via JSON files). For one column having String datatype and format as : 08/12/1977 09:45:34 AM, which need to be converted into DATE data type and to the format : ISODate : (“1977-08-12T09:45:34.000+0000”)

Either via PyMongo or directly via Mongo aggregation/conversion utilities usage can serve the purpose. Any help is highly appreciated.

Best Regards
Kesav

2

One option is to write a script that loops to

  • Retrieve the document you want to update

  • Convert that date to ISO (could use Python’s datetime package doing something like:

    from datetime import datetime
    mydate = datetime.strptime(‘08/12/1977 09:45:34 AM’, ‘%d/%m/%Y %I:%M:%S %p’)
    mydate = mydate.isoformat()

  • Update the document with the new date

For details on how to retrieve and update documents, see this Python Quick Start.

If you’re using 4.2 or later, another option is to use the aggregation pipeline for update operations. You could run something like:

db.nameOfYourCollection.updateMany( 
  {whateverFilterParamsYouWantHere}, 
  [
    {
     $set:  { 
       date: {
         $convert: { 
           input: "$date",
           to: "date" 
         } 
       }
      }
     }
  ]
)

See https://docs.mongodb.com/manual/tutorial/update-documents-with-aggregation-pipeline and https://docs.mongodb.com/manual/reference/operator/aggregation/convert/#convert-to-date for more details.

1 Like
3

Thanks a lot for the response and will give a try.

1 Like
4

Did it correctly convert time to utc by reducing 5 and a half hours?