MongoDB: Convert Date String (mm/dd/yyyy) to Unix timestamp

Question

just practicing my MongoDB queries and I've hit a wall with a field data type.

I'm currently using Robomongo as GUI for accessing the production database.

My document structure looks like:

Is there a MongoDB operator or way/method to convert the date field value, currently in mm/dd/yyyy format, to a Unix timestamp so we can perform filter operations?

Answer 1

You can iterate all your items and update one by one with the conversion to Date. Here is an example to convert your date from mm/dd/yyyy to ISODate :

db.test.find().forEach( function(res){

      if (typeof(res.date)=="string"){
        var arr = res.date.split("/");
        res.date = new Date(arr[2], arr[0] - 1, arr[1]);
        db.test.save(res)
      }
    }
)

For Unix timestamp (millis from epoch), you can call getTime() from Date :

db.test.find().forEach( function(res){

      if (typeof(res.date)=="string"){
        var arr = res.date.split("/");
        res.date = new Date(arr[2], arr[0] - 1, arr[1]).getTime();
        db.test.save(res)
      }
    }
)

Note that these dates will be converted into UTC format, so you may want to change temporarily your timezone before doing your conversion

You can also use bulk update if you want to optimize update performance

You can also just convert your date to yyyy-mm-dd which will preserve sorting (check this post). The following will decompose your date field into day,month and year, set date field with the new format and write output in a new collection named test2 :

db.test.aggregate([{
    $project: {
        startTime: 1,
        endTime: 1,
        date: {
            $let: {
                vars: {
                    year: { $substr: ["$date", 6, 10] },
                    month: { $substr: ["$date", 0, 2] },
                    dayOfMonth: { $substr: ["$date", 3, 2] }
                },
                in : { $concat: ["$$year", "-", "$$month", "-", "$$dayOfMonth"] }
            }
        }
    }
},{
    $out :"test2"
}])