Working with Timezone in MongoDB

MongoDB stores times in UTC by default, and will convert any local time representations into this form. Applications that must operate or report on some unmodified local time value may store the time zone alongside the UTC timestamp, and compute the original local time in their application logic.

Create Sample Collection & Data

> db.createCollection('DateSample');
{ "ok" : 1 }
> db.DateSample.insert({item:1,MyDate:new Date()});
WriteResult({ "nInserted" : 1 })
> db.DateSample.find();
{ "_id" : ObjectId("5fa1f7466aec771fa5273dfe"), "item" : 1, "MyDate" : ISODate("2020-11-04T00:35:18.825Z") }

Solution 1 for MongoDB 3.6+:

> db.DateSample.aggregate(
    {$project: 
         { datewithIST: 
            {$dateToString:              
                {date:"$MyDate",
                 timezone:"+0530",
                 format:"%Y-%m-%dT%H:%M:%S.%LZ"
                }
             },
         asis:{date:"$MyDate"}
         }
     }
   );
{ "_id" : ObjectId("5fa1f7466aec771fa5273dfe"), "datewithIST" : "2020-11-04T06:05:18.825Z", "asis" : { "date" : ISODate("2020-11-04T00:35:18.825Z") } }

Solution 2 for MongoDB 3.6+:

> db.DateSample.aggregate(
    {$project: 
         { datewithIST: 
            {$dateToString:              
                {date:"$MyDate",
                 timezone:"Asia/Kolkata",
                 format:"%Y-%m-%dT%H:%M:%S.%LZ"
                }
             },
         asis:{date:"$MyDate"}
         }
     }
   );
{ "_id" : ObjectId("5fa1f7466aec771fa5273dfe"), "datewithIST" : "2020-11-04T06:05:18.825Z", "asis" : { "date" : ISODate("2020-11-04T00:35:18.825Z") } }

Error you might get:

can’t convert from BSON type string to Date

db.collection.aggregate(
…     { $match : { "type" : "Review"}},
…     { $group : {
…         _id: {
…             year : { $year : "$created" },
…             month : { $month : "$created" },
…             day : { $dayOfMonth : "$created" },
…         },
…         count: { $sum: 1 }
…     }},
…     { $sort : { _id : 1}}
… );
assert: command failed: {
        "errmsg" : "exception: can’t convert from BSON type String to Date",
        "code" : 16006,
        "ok" : 0
} : aggregate failed

Solutions:

1. The best thing to do is to resolve the data type inconsistency at the application layer so that data is entered into the database in ISO format which can then be easily worked with. This would require the existing data set to be changed to [date] data type.
2. For immediate analysis of the data use regex and substring to extract the date portions

db.collection.aggregate(
    { $match : { "created" : {$in : [/2014-10/]}}},
    { $group : {
        _id: {
            year :  { $substr : ["$created", 0, 4 ] },   
            month : { $substr : ["$created", 5, 2 ] },                                      
            day :   { $substr : ["$created", 8, 2 ] },        
        },
        count: { $sum: 1 }
    }
              },
    { $sort : { _id : -1}}
);

Reference

Model Time Data - MongoDB Manual

$dateToString (aggregation) - MongoDB Manual