String to date in MongoDB either shell or PyMongo

Question

I'm very new to MongoDB and its queries but at the moment, I can't change a 'column' in a collection from string to date.

My database and collection are both called thdr. I want to change the column 'PSTNG_DATE' to a date format. At the moment the format is '19.10.2017'.

I went through the threads here and tried a couple of stuff, but it doesn't seem to work. I understand that I can either change it through the MongoDB Shell or using PyMongo.

I very much appreciate your help and guidance, thanks a lot.

** UPDATE ** That's what I have at the moment and it gives me the syntax error

db = db.getSiblingDB('thdr');
var requests = [];
db.thdr.find().forEach(doc => { 
    var date = yourFunctionThatConvertsStringToDate(doc.PSTNG_DATE);
    requests.push( { 
        'updateOne': {
            'filter': { '_id': doc._id },
            'update': { '$set': {  
                "PSTNG_DATE": date
            } }
        }
    });
    if (requests.length === 500) {
        db.thdr.bulkWrite(requests);
        requests = [];
    }
});

if(requests.length > 0) {
    db.thdr.bulkWrite(requests);
}

Possible dupe of https://stackoverflow.com/questions/10942931/converting-string-to-date-in-mongodb — JohnnyHK, Oct 19 '17 at 12:54

Alex P. · Accepted Answer · 2017-10-19T15:01:34.690

1

You can do that with a bulkWrite:

db = db.getSiblingDB('yourDatabaseName');
var requests = [];
db.yourCollectionName.find().forEach(doc => { 
var strDate = doc.PSTNG_DATE; 
var dateParts = strDate.split("."); 
var date = new Date(dateParts[2], (dateParts[1] - 1), dateParts[0]);
    requests.push( { 
        'updateOne': {
            'filter': { '_id': doc._id },
            'update': { '$set': {  
                "PSTNG_DATE": date
            } }
        }
    });
    if (requests.length === 500) {
        db.yourCollectionName.bulkWrite(requests);
        requests = [];
    }
});

if(requests.length > 0) {
     db.yourCollectionName.bulkWrite(requests);
}

You'd have to update each document. So instead of doing an update on each document you can add the updateOne() collection method to an array (requests) and execute them in a bulkWrite(...) Load the script as described here https://docs.mongodb.com/manual/reference/method/load/ directly on your mongodb server and execute there. Hope this helps.

FYI: Inside the find() you can actually add a query if you like (it's even prefered to. Especially if you search for an indexed field). That would reduce the load and the amount of documents to be replaced at once.

edited Oct 19 '17 at 15:01

answered Oct 19 '17 at 12:36

Alex P.

3,073
3
22
33

changed the db name to thdr, pasted the script into a .js file and used the load method in the mongo shell. I get a syntax error. `> load(C:\Users\me\Desktop\asd\update2.js) 2017-10-19T15:53:15.917+0200 E QUERY [thread1] SyntaxError: malformed Unicode character escape sequence @(shellhelp1):1:24 error2:SyntaxError: malformed Unicode character escape sequence @(shellhelp1):1: 24` – Mark Wellings Oct 19 '17 at 13:55
You are forgetting this " maybe? load("C:\Users\me\Desktop\asd\update2.js") – Alex P. Oct 19 '17 at 14:02
i've updated the post with the modified .js using " didnt do the trick – Mark Wellings Oct 19 '17 at 14:14
yourFunctionThatConvertsStringToDate was an example name of the function you will have to create of course. Let me update my answer for you, so it's clearer. – Alex P. Oct 19 '17 at 14:18
Jeah, thought so.. Well to be honest no idea how to do that. – Mark Wellings Oct 19 '17 at 14:23
There are plenty of code snippets out there https://stackoverflow.com/questions/1576753/parse-datetime-string-in-javascript. You'd just have to do it the other way around: var strDate = "2015.03.25"; var dateParts = strDate.split("."); var date = new Date(dateParts[0], (dateParts[1] - 1), dateParts[2]); – Alex P. Oct 19 '17 at 14:30
Just take the new update from the answer. I thought your question was how to update a field in mongodb. The actual conversion of the date is not even mongodb related, it's just plain programming and for that there are many examples. – Alex P. Oct 19 '17 at 14:43
I wanted to update a field in a collection which has string values which are supposed to be date values. I tried the load message, updating the DB and Collection but still gives me an error. Feel like I'm wasting your time.. – Mark Wellings Oct 19 '17 at 14:49
sorry I saw your date field wrong. Thought it was yyyy.mm.dd but it is dd.mm.yyyy. I just executed the script in a test collection and it worked. I'll update the answer. The array of the date was just the other way around. It should be working now, for you too. – Alex P. Oct 19 '17 at 15:01
Impressive mate, thanks for your help. Really great spirit, helping newbees out. thanks again – Mark Wellings Oct 19 '17 at 15:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/157074/discussion-between-mark-wellings-and-alex-p). – Mark Wellings Oct 19 '17 at 15:25

String to date in MongoDB either shell or PyMongo

1 Answers1