home
Hero image for: How to convert string fields to date fields in MongoDB

How to convert string fields to date fields in MongoDB

By Eduardo García ● CTO | December 17th, 2019

It's normal that we migrate Date fields from other database engines or maybe import information from CSV files. At the end of any migration, one of the most common mistakes is store dates as strings, instead of a Date field that could be understood for your new database engine.

In terms of MongoDB, storing dates as string limit us to use Date functions to filter or process that information in a proper way. Here I want to show you how to solve that issue and don't get crazy in the process.

Imagine that you have a collection of documents named "time" with a property called "date", the following script will traverse all documents in that collection to update each document to replace the property date as a string using the Date format.

db.getCollection('time').find({})
    .forEach( function(item){
        if (typeof(item.date) == "string"){
            print(item.date);
            item.date = new Date(item.date);
            db.getCollection('time').save(item);
        }
    }
);

You can run first the script with the save part commented out and use the print to debug that the script is doing what do you expect of it.

As a minor change, you can choose to use ISODate instead of Date as you can see below.

db.getCollection('time').find({})
    .forEach( function(item){
        if (typeof(item.date) == "string"){
            print(item.date);
            //item.date_updated = new ISODate(item.date_updated);
            //db.items.save(item)
        }
    }
);

Now you can use the normal Date functions to select or filter your data, here an example.

db.getCollection('time').find({
	date: { $lte: new Date('11/15/2017') }
});

I hope this blog post helps you to handle your data in Mongo DB easier and help you with the transition to Mongo DB.





Related Posts