MongoDB shell script using projection to format da

Question

Here is the projection I am using

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

My due date value in Mongo is ISODate("2016-10-08T17:00:00.000Z") which in local time is 22:30 PM but using above projection I get the value as 5:00 PM

this ISODate("2016-10-08T17:00:00.000Z").toLocaleString() returns Saturday, October 08, 2016 22:30:00

So how can I apply toLocaleString() in projection and get the result in the above format

Answer 1

You can't directly use "toLocaleString()". However, you can add the offset.

1) Third pipeline is used to add the offset

2) Fourth pipeline is used to format the date

var tzOffset = 5.5 * 1000 * 60 * 60;

db.MyCollection.aggregate( [
   { "$match": { "ProjectID" : 999 } },
   { "$sort": { "CreatedDate": -1 } },
   {          
      $project: {
         localTime: {
            $let: {
               vars: {
                   "localTime": { "$add": [ "$DueDate", tzOffset]

                }
               },
               in: { $add: ["$$localTime"] }
            }
         }
      }
   },
   {          
      $project: {
         "_id": 0, 
         "formattedLocalTime": {
                "$dateToString": { 
                    "format": "%Y-%m-%d %H-%M", 
                    "date": "$localTime"
                }
            }
      }
   }

]);

Input:-

"DueDate" : ISODate("2016-08-11T10:17:09.203Z")
"DueDate" : ISODate("2016-08-11T23:16:09.203Z")

Output:-

"formattedLocalTime" : "2016-08-11 15-47"
"formattedLocalTime" : "2016-08-12 04-46"

Please note the output 2. The next date is populated correctly.