How to handle date between India Time on Client side and Server date in US Time via mongo

Question

I have an app where the client location will be in India. My application has to aggregate data based on the daterange client has given. So if the client gives 14-Dec-2016 to 21-Dec-2016. It should search on from 14-Dec-2016 00:00:00am to 21-Dec-2016 23:59:59pm. Now as soon as I send my date to my server it get converted to
Dec 13 2016 18:30:00 GMT+0000 (UTC)
Dec 21 2016 18:29:59 GMT+0000 (UTC)
Now I write my aggregation query as

let cursor = Trip.aggregate([{
        $match: {
            startTime: {
                $gte: startDate.toDate(),
                $lte: endDate.toDate()
            },
        }
    },{
        $group: {
            _id: {
                date: {
                    $dayOfMonth: "$startTime"
                },
                month: {
                    $month: "$startTime"
                },
                year: {
                    $year: "$startTime"
                }
            },
            count: {
                $sum: 1
            }
        }
    }]);

Which results in following output

[ { _id: { date: 17, month: 12, year: 2016 }, count: 2 },
  { _id: { date: 16, month: 12, year: 2016 }, count: 2 },
  { _id: { date: 13, month: 12, year: 2016 }, count: 2 } ]

The actual time the trip took place was

"startTime" : ISODate("2016-12-13T20:10:20.381Z")
"startTime" : ISODate("2016-12-13T19:54:56.855Z")

Which actually took place on 14-12-2016 01:40:20am and 14-12-2016 01:24:56am

I want all things to be in one time-range but MongoDB does not allow to store data in any other time range other than UTC and it is getting difficult to manage different times in client-side query and database. How should I go about solving it?

Answer 1

You can approach the following way. You can save the records with offset millis. So your collection will look like something below.

{
    "_id": ObjectId("585a97dcaceaaa5d2254aeb5"),
    "start_date": ISODate("2016-12-17T00:00:00Z"),
    "offsetmillis": -19080000
} {
    "_id": ObjectId("585a97dcaceaaa5d2254aeb6"),
    "start_date": ISODate("2016-11-17T00:00:00Z"),
    "offsetmillis": -19080000
} {
    "_id": ObjectId("585a97dcaceaaa5d2254aeb7"),
    "start_date": ISODate("2016-11-13T00:00:00Z"),
    "offsetmillis": -19080000
}

And you can update the aggregation query to include the offset millis while processing.

aggregate([{
    $match: {
        start_date: {
            $gte: new ISODate("2016-01-01"),
            $lte: new ISODate("2016-12-31")
        },
    }
}, {
    $group: {
        _id: {
            date: {
                $dayOfMonth: {
                    $add: ["$start_date", "$offsetmillis"]
                }
            },
            month: {
                $month: {
                    $add: ["$start_date", "$offsetmillis"]
                }
            },
            year: {
                $year: {
                    $add: ["$start_date", "$offsetmillis"]
                }
            }
        },
        count: {
            $sum: 1
        }
    }
}]);

Sample Response

{ "_id" : { "date" : 12, "month" : 11, "year" : 2016 }, "count" : 1 }
{ "_id" : { "date" : 16, "month" : 11, "year" : 2016 }, "count" : 1 }
{ "_id" : { "date" : 16, "month" : 12, "year" : 2016 }, "count" : 1 }

You can optimize it more but I think this will give you an idea.