HaBo HaBo - 4 months ago 51
Bash Question

Format date value in MongoDB shell FIND script

Here is my Mongo Shell Script

db.MyCollection.find({ "ProjectID" : 999 } }, {
"_id" : 0,
"DueDate" : 1
}).sort({ "CreatedDate" : -1 })


Result

{
"DueDate" : ISODate("2016-02-20T08:00:00.000Z")
}


Expected Result:

{
"DueDate" : "2016-02-20 08:00 AM"
}


Tried following this article

db.MyCollection.aggregate(
[
{
$project: {
yearMonthDay: { $dateToString: { format: "%Y-%m-%d", date: "$DueDate" } },
time: { $dateToString: { format: "%H:%M:%S:%L", date: "$DueDate" } }
}
}
]
)


That give following null result

{
"_id" : ObjectId("578e3bb48c305af06d30147e"),
"yearMonthDay" : null,
"time" : null
}


How can I get this work, I also would like to add other fields to the projection with out any formatting for those

Answer

The aggregation pipeline you are running has the wrong $dateToString format. You need to run this pipeline to get the right result:

db.MyCollection.aggregate([
    { "$match": { "ProjectID" : 999 } },
    { "$sort": { "CreatedDate": -1 } },
    {
        "$project": {
            "_id": 0,
            "DueDate": {
                "$dateToString": { 
                    "format": "%Y-%m-%d %H-%M", 
                    "date": "$DueDate"
                }
            }
        }
    }
])

which happens to be the date formatted version of the mongo shell query:

db.MyCollection.find(
    { "ProjectID" : 999 } 
    { "_id" : 0, "DueDate" : 1 }
}).sort({ "CreatedDate" : -1 })