2

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:

Robomongo Document Structure

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?

Bertrand Martel
  • 42,756
  • 16
  • 135
  • 159
Albert Bahia
  • 21
  • 1
  • 3

1 Answers1

0

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"
}])
Community
  • 1
  • 1
Bertrand Martel
  • 42,756
  • 16
  • 135
  • 159
  • Thanks @BertrandMartel. This is really helpful however I was looking to keep this within MongoDB as this is part of an aggregate query. If it all possible I'm looking to avoid using Javascript, PHP, etc. to make the conversion. Is there a process using various MongoDB operators to achieve this? – Albert Bahia Dec 30 '16 at 20:59
  • I've updated my post with a way to convert your string date format to `yyyy-mm-dd` which is not what you want in the first place but will preserve sorting compare to `mm-dd-yyyy` – Bertrand Martel Dec 30 '16 at 22:02