HaBo HaBo - 3 months ago 50
Bash Question

MongoDB shell script using projection to format date and get local time

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

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.