How to aggregate by year-month-day on a different timezone

Question

I have a MongoDB whom store the date objects in UTC. Well, I want to perform aggregation by year,month day in a different timezone (CET).

doing this, works fine for UTC:

    BasicDBObject group_id = new BasicDBObject("_id", new BasicDBObject("year", new BasicDBObject("$year", "$tDate")).
                append("month", new BasicDBObject("$month", "$tDate")).
                append("day", new BasicDBObject("$dayOfMonth", "$tDate")).
                append("customer", "$customer"));

    BasicDBObject groupFields = group_id.
            append("eventCnt", new BasicDBObject("$sum", "$eventCnt")); 

    BasicDBObject group = new BasicDBObject("$group", groupFields);

or, if you use the command line (not tested, I only tested the java version):

{
    $group: {
        _id: {
            "year": {
                "$year", "$tDate"
            },
            "month": {
                "$month", "$tDate"
            },
            "day": {
                "$dayOfMonth", "$tDate"
            },
            "customer": "$customer"
        },
        "eventCount": {
            "$sum": "$eventCount"
        }
    }
}

How do I convert these dates into CET inside the aggregation framework?

For example '2013-09-16 23:45:00 UTC' is '2013-09-17 00:45:00 CET', this is a different day.

Answer 1

I'm not an expert on CET and its relation to UTC, but the following code (for the shell) should do a proper conversion (adding an hour) to a MongoDB date type:

db.dates.aggregate(
  {$project: {"tDate":{$add: ["$tDate", 60*60*1000]}, "eventCount":1, "customer":1}}
)

If you run that project command before the rest of your pipeline, the results should be in CET.

Answer 2

You can provide the timezone to the date operators starting in 3.6.

Replace the timezone with your timezone.

{
  "$group":{
    "_id":{
      "year":{"$year":{"date":"$tDate","timezone":"America/Chicago"}},
      "month":{"$month":{"date":"$tDate","timezone":"America/Chicago"}},
      "dayOfMonth":{"$dayOfMonth":{"date":"$tDate","timezone":"America/Chicago"}}
    },
    "count":{"$sum":1}
  }
}

Answer 3

After searching for hours, this is the solution that worked for me. It is also very simple. Just convert the timezone by subtracting the timezone offset in milliseconds.

25200000 = 7 hour offset // 420 min * 60 sec * 1000 mili

$group: {
    _id = { 
        year: { $year : [{ $subtract: [ "$timestamp", 25200000 ]}] }, 
        month: { $month : [{ $subtract: [ "$timestamp", 25200000 ]}] }, 
        day: { $dayOfMonth : [{ $subtract: [ "$timestamp", 25200000 ]}] }
    },
    count = { 
        $sum : 1
    }
};

Answer 4

Use for example moment.js to dertmine the current timezone offset for CET but this way you get the summer&winter offsets

var offsetCETmillisec = moment.tz.zone('Europe/Berlin').offset(moment())* 60 * 1000;

  $group: {
    _id: {
      'year': {'$year': [{ $subtract: [ '$createdAt', offsetCETmillisec ]}] },
      'month': {'$month': [{ $subtract: [ '$createdAt', offsetCETmillisec ]}] },
      'day': {'$dayOfMonth': [{ $subtract: [ '$createdAt', offsetCETmillisec ]}] }
    },
    count: {$sum: 1}
  }
}

Answer 5

MongoDB's documentation suggests that you save the timezone offset alongside the timestamp:

var now = new Date();
db.data.save( { date: now,
                offset: now.getTimezoneOffset() } );

This is of course not the ideal solution – but one that works, until we have in MongoDb's aggregation pipeline a proper $utcOffset function.