MongoDB aggregate Timezone for date add

Question

I have a problem of MongoDB's aggregate of timezone is UTC. I have looked for solutions from many other existing issues, but it is still not working. My code as follows:

MongoDB version : 2.2

Data

{ "_id" : ObjectId("52a3c9df46c6a9627eeb0337"), "Counting" : { "id" : "b1a93dfda46c47848f9862031300d24c", "group" : "Salary", "user_id" : "4d4ad2d37a464ad09d9aca2fee4c760c", "subGroup" : "e–ae3?", "bank_id" : "97e0fecc322b49b48c4eb3c8425fea77", "fee" : 646, "isIncome" : "true", "payment" : "", "consumeDate" : ISODate("2013-08-15T16:00:00Z"), "createDate" : ISODate("2013-12-08T01:22:39.008Z"), "bank_name" : "9edb6897-cdb8-4ce4-8f08-f5792cfa83d9" } }

{ "_id" : ObjectId("52a3c9df46c6a9627eeb0338"), "Counting" : { "id" : "33b341fc71314daebe851397c5cbaa40", "group" : "Salary", "user_id" : "cb9e06649cf943e5b368f6b05fc126c6", "subGroup" : "e–ae3?", "bank_id" : "e8da8cdae3ae495ca76f873fb3460b6d", "fee" : 647, "isIncome" : "true", "payment" : "", "consumeDate" : ISODate("2013-02-28T16:00:00Z"), "createDate" : ISODate("2013-12-08T01:22:39.016Z"), "bank_name" : "6913b48a-1a95-48c5-81f5-6920031358d7"} }

{ "_id" : ObjectId("52a3c9df46c6a9627eeb033a"), "Counting" : { "id" : "f0d41ed9f29f47e7b68a05c378cf939d", "group" : "Salary", "user_id" : "847cadbf55f84615af3ee63922446b54", "subGroup" : "e–ae3?", "bank_id" : "f45d62b5e62f4b7fa8172870cd992f19", "fee" : 623, "isIncome" : "true", "payment" : "", "consumeDate" : ISODate("2013-04-18T16:00:00Z"), "createDate" : ISODate("2013-12-08T01:22:39.152Z"), "bank_name" : "30dd169e-723e-4748-93cd-2d7a45b4a3b7"} }


db.Product.aggregate([{
"$group": {
    "_id": {
        "tyear": {
        "$year": [{
            "$add": ["$Counting.consumeDate", 28800000]
        }]
        },
        "tMonth": {
            "$month": [{
                "$add": ["$Counting.consumeDate", 28800000]
            }]
        },
        "tDate": {
            "$dayOfMonth": [{
                "$add": ["$Counting.consumeDate", 28800000]
            }]
        },
    },
    "count": {
        "$sum": "$Counting.fee"
    }
} }])

Error Message :

"errmsg" : "exception: $add does not support dates"

Reference How to agregate by year-month-day on a different timezone

score 2 · Accepted Answer · answered Nov 19 '14 at 04:19

I'd recommend doing this in two-steps as a project then a group.

var millisecondsFromUTC = 8 * 60 * 60 * 1000; //PST is -8 hours from UTC

db.Product.aggregate([
    { $project : { 
        consumeDateLocal: {
            $subtract : [ "$Counting.consumeDate", millisecondsFromUTC ] 
        },
        fee: '$Counting.fee"  } },
    { $group: {
        _id: {
            "tyear": { $year: "$consumeDateLocal" },
            "tMonth": { "$month": "$consumeDateLocal" },                
            "tDate": { "$dayOfMonth": "consumeDateLocal" }
        },
        count: {
            $sum: "$fee"
        }
    } } ], ...);

score 0 · Answer 2 · answered Dec 30 '16 at 02:53

I do it like this.

millisecondsFromUTC = 8 * 3600 * 1000
Db.collection.aggreagte([
  {$match: query},
  {
    $group: {
    _id:  {
      $dateToString: {
         format: "%Y-%m-%d", 
         date: {$add: ["$date", millisecondsFromUTC]}
    },
    click: {$sum: '$click'},
    money: {$sum: {$divide: ['$money', 10000]}},
    pv: {$sum: '$pv'},
    req: {$sum: '$req'},
    date: {$last: '$date'}
  }
}]

MongoDB aggregate Timezone for date add

2 Answers2