Group by Date with Local Time Zone in MongoDB

user_7425764 0
Viewed 7605

I am new to mongodb. Below is my query.

Model.aggregate()
            .match({ 'activationId': activationId, "t": { "$gte": new Date(fromTime), "$lt": new Date(toTime) } })
            .group({ '_id': { 'date': { $dateToString: { format: "%Y-%m-%d %H", date: "$datefield" } } }, uniqueCount: { $addToSet: "$mac" } })
            .project({ "date": 1, "month": 1, "hour": 1, uniqueMacCount: { $size: "$uniqueCount" } })
            .exec()
            .then(function (docs) {
                return docs;
            });

The issue is mongodb stores date in iso timezone. I need this data for displaying area chart.

I want to group by date with local time zone. is there any way to add timeoffset into date when group by?

mongoose aggregation-framework node.js mongodb javascript
3 Answers

November 2017 saw the release of MongoDB v3.6, which included timezone-aware date aggregation operators. I would encourage anyone reading this to put them to use rather than rely on client-side date manipulation, as demonstrated in Neil's answer, particularly because it is way easier to read and understand.

Depending on the requirements, different operators might come in handy, but I've found $dateToParts to be the most universal/generic. Here's a basic demonstration using OP's example:

project({
  dateParts: {
    // This will split the date stored in `dateField` into parts
    $dateToParts: {
      date: "$dateField",
      // This can be an Olson timezone, such as Europe/London, or
      // a fixed offset, such as +0530 for India.
      timezone: "+05:30"
    }
  }
})
.group({
  _id: {
    // Here we group by hour! Using these date parts grouping
    // by hour/day/month/etc. is trivial - start with the year
    // and add every unit greater than or equal to the target
    // unit.
    year: "$dateParts.year",
    month: "$dateParts.month",
    day: "$dateParts.day",
    hour: "$dateParts.hour"
  },
  uniqueCount: {
    $addToSet: "$mac"
  }
})
.project({
  _id: 0,
  year: "$_id.year",
  month: "$_id.month",
  day: "$_id.day",
  hour: "$_id.hour",
  uniqueMacCount: { $size: "$uniqueCount" }
});

Alternatively, one might wish to assemble the date parts back to a date object. This is also very simple with the inverse $dateFromParts operator:

project({
  _id: 0,
  date: {
    $dateFromParts: {
      year: "$_id.year",
      month: "$_id.month",
      day: "$_id.day",
      hour: "$_id.hour",
      timezone: "+05:30"
    }
  },
  uniqueMacCount: { $size: "$uniqueCount" }
})

The great thing here is that all the underlying dates remain in UTC and any returned dates are also in UTC.

Unfortunately, it seems that grouping by more unusual arbitrary ranges, such as half-day, might be harder. I haven't given it much thought however.

user_37445740

Maybe this will help someone coming to this question.

There is property "timezone" in $dateToString object.

For example:

$dateToString: { format: "%Y-%m-%d %H", date: "$datefield", timezone: "Europe/London" }
user_91860350
Related