Convert Date to MongoDB ISO Date

Question

I have a date that is in this format in my MongoDB database.

{
      "_id": ObjectId("5682e899f326d45c821d8b9b"),
      "City": "Atlanta",
      "Date": "12/5/02",
      "Temp": 78.5,
}

I would like to convert this to ISO date so I could sort on date in my database. What would be the best way to do this with an update command with PyMongo or JavaScript.

Do you want to update your documents by changing `Date` to ISODate? — styvane, Dec 29 '15 at 20:45
you need to read documents, add date object and then save them again — Valijon, Dec 29 '15 at 21:03

score 1 · Answer 1 · answered Dec 29 '15 at 21:15

Mongo has moved on quite a bit since I last used it but as far as I know, as has been suggested elsewhere you are going to have to load and update each record in turn.

You have two problems. The first is that your date format is ambiguous. The example date you give will have different values depending on the locale of the system used to parse it as a date. In the US it is (I think) December the 5th 2002. On my side of the atlantic though it will parse as 12th May 2002. Funtimes.

Assuming you know the intented format of each date then in node you could use moment.js to parse each date to a fixed format for example

let date = moment(document.Date, "D/M/YY");

and then output as an ISO date using

document.Date = date.toISOString();

or if you are being good, and doing it non-destructively

document.ISODate = date.toISOString();

although now you have two dates to look after...

score 0 · Answer 2 · answered Dec 29 '15 at 23:18

0

You will need to iterate over the entire result set, and update each document individually like so:

var f = function(e) {
    var arr = e['Date'].split("/");

    //assuming "Date" field was formatted as "YY/MM/DD"
    var date_obj = new Date(arr[0], arr[1], arr[2]);

    db.your_collection.update({_id: e['_id']}, {$set: {date_obj: date_obj}});    
}


db.your_collection.find().forEach(f)

If you want to remove your old field, you can run the following:

db.your_collection.update({}, {$unset: {Date: 1}}, {multi:true});

But don't do that until you are sure your dates were converted correctly.

answered Dec 29 '15 at 23:18

Martin Konecny

57,827
19
139
159

1

Using bulk operations would make this more efficient. – Markus W Mahlberg Dec 30 '15 at 11:19
@MarkusWMahlberg - how would Bulk operations work if the update `date_obj` value is different for each document? – Martin Konecny Dec 30 '15 at 20:19
Of the top of my head for 2.6: `var bulk = db.c.initializeUnorderedBulkOp(); var f = function(e){... var d = ...; bulk.find(query).update(upDoc);}; db.c.find(query).forEach(f); bulk.execute()`. Accordingly for 3.0 – Markus W Mahlberg Dec 31 '15 at 00:39

score -1 · Answer 3 · edited May 23 '17 at 12:15

-1

I think, you should first retrieve the records and use a loop to change all. Then you should parse the data to construct a Date object to convert to mongo date.

var value = "12/5/02";
var parts = value.split("/");
var dt = new Date("20"+parts[2], parseInt(parts[1])-1, parts[0]);

If you are using nodejs, check this too.

Edit: I assumed 12=day, 5=month.

edited May 23 '17 at 12:15

Community

1
1

answered Dec 29 '15 at 20:37

abeyaz

3,034
1
16
20

Did you run your code against the example date provided? I don't think it does what you were hoping it would – Andrew Dec 29 '15 at 20:44
@Andy why not do you think ? – abeyaz Dec 29 '15 at 20:47
Because when I run it it comes out with an incorrect date. – Andrew Dec 29 '15 at 20:49
Are you checking it in pure javascript or in mongodb ? – abeyaz Dec 29 '15 at 20:59
It works in web page. I ll also check in mongodb console. – abeyaz Dec 29 '15 at 22:16
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/99259/discussion-between-andy-and-ardabeyazoglu). – Andrew Dec 29 '15 at 22:46
I edited the answer to be correct. The last argument must be parts[0]. – abeyaz Dec 29 '15 at 22:59

Convert Date to MongoDB ISO Date

3 Answers3