Change String date to ISO date in MongoDB?

Question

In MongoDB, I have a document with a field called "date" and that is in array.

  {
"_id" : ObjectId("587627a2125a730f78a20859"),
"data" : [
    {
        "Number" : "359983007479839",
        "date" : "2016-02-10T21:56:33.000Z"
    }
]

after that I run this script:

db.dummy.find().forEach(function(doc){
doc.mongodbdate = ISODate(doc.mongodbdate);
db.dummy.save(doc);
})

And its giving me below output;

{
"_id" : ObjectId("588724ba2746360c04a51e4b"),
"data" : [
    {
        "Number" : "359983007479839",
        "mongodbdate" : "2016-02-12T18:01:06.000Z"
    }
],
"mongodbdate" : ISODate("2017-01-24T15:26:24.537+05:30")
}

I tried this also:

var bulk = db.dummy.initializeUnorderedBulkOp(),
count = 0;

db.dummy.find().forEach(function(doc) {
bulk.find({ "_id": doc._id }).updateOne({
    "$set": { "mongodbdate": ISODate(doc.mongodbdate) }
})
count++;
if (count % 1000 == 0) {
    // Execute per 1000 operations and re-init
    bulk.execute();
    bulk = db.dummy.initializeUnorderedBulkOp();
}
})

its throws Error: "message" : "invalid ISO date"

I want to convert that string date into ISO date.I have changed with some code but its adding new date with ISO format but I want to update which is already available no need to insert new one. One solution I got on stackoverflow also, but that is adding new field in my document i think it is because of array,I want to update my existing one.

score 1 · Answer 1 · answered Jan 23 '17 at 07:49

1

You can use dateutil module (sudo pip install python-dateutil)

Following is the sample code which convert the ISO string into datatime object and then insert it into mongo database.

import datetime
import pymongo
import dateutil.parser

def getDatetimeFromISO(s):
    d = dateutil.parser.parse(s)
    return d

conn = pymongo.MongoClient()
db = conn.sampleDB
collection = db.test
post = {
    "user" : "test1",
    "date" : getDatetimeFromISO("2016-02-10T21:56:33.000Z")
}

collection.insert_one(post)
conn.close()

In MongoDB

> db.test.find().pretty()
{
    "_id" : ObjectId("5885b47156addb199a07bf26"),
    "date" : ISODate("2016-02-10T21:56:33Z"),
    "user" : "test1"
}

You can update the string dates to ISO format in mongoDB in similar fashion.

answered Jan 23 '17 at 07:49

Priyank Chheda

521
5
16

without using pymongo how I can do? – TB.M Jan 23 '17 at 08:57
You have to update in MongoDB directly, right? Then you need some interface which will connect your programming language to mongodb. In Python, that happens to be `pymongo` (there are others but trust me, pymongo is way easy to learn than others). – Priyank Chheda Jan 23 '17 at 09:23
I used this script; but this is creating new fields in document that also out of the array...its not updating... db.dummy.find().forEach(function(doc) { doc.mongodbdate=new Date(data.mongodbdate); db.dummy.save(doc); }) – TB.M Jan 23 '17 at 10:13
It is just a sample code to demonstrate the solution. You need to use `update` command for updating the document. Please read about mongodb and pymongo. – Priyank Chheda Jan 23 '17 at 10:29
if I am updating also..that is creating new one outside the array...its not updating its creating new one. – TB.M Jan 24 '17 at 06:43
are you okay with python implementation in pymongo? – Priyank Chheda Jan 24 '17 at 10:48
no I dnt want to implement pymongo.I want to run this with java script only which I can run as I mention in my question. – TB.M Jan 25 '17 at 04:36
Give me some time. I will update the answer with Javascript implementation. – Priyank Chheda Jan 25 '17 at 04:58
you got any solution ? – TB.M Jan 25 '17 at 10:03

score 1 · Answer 2 · answered Jun 27 '17 at 16:41

You just need to modify your code to loop over the array as well.

db.dummy.find().forEach(function (doc) {
  doc.data.forEach(function (currentValue, index) {
    doc.data[index].mongodbdate = ISODate(currentValue.mongodbdate);
  });
  db.dummy.save(doc);
})

score 1 · Answer 3 · answered May 17 '22 at 11:46

Starting from version 4.0, $toDate aggregation is possible to convert fields.

General syntaxe :

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

first parameter is the filter. Find more in official docs.

Change String date to ISO date in MongoDB?

3 Answers3

Linked