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.

score 16 · Accepted Answer · answered Sep 17 '13 at 15:43

16

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.

answered Sep 17 '13 at 15:43

3rf

1,154
7
15

"exception: $add only supports numeric or date types, not String" :( – SQL.injection Sep 18 '13 at 08:42
String [] date_add_array = {"$t_roundedDateHour", String.valueOf(3600*1000)}; BasicDBObject projectionsFields = new BasicDBObject("tDate", new BasicDBObject("$add", date_add_array).append("customer", 1).append("eventCount",1); – SQL.injection Sep 18 '13 at 08:43
The aggregation framework is configured as a pipeline, which means you can pass in an array of aggregation operations and they will be executed in order, the output of one being used as the input or the next, and so on. In the shell it might look something like: db.collection.aggregate([{$project: ...}, {$group: ...}]); I don't use java a lot, but there's a tutorial on how to use the aggregation pipeline here: http://docs.mongodb.org/ecosystem/tutorial/use-aggregation-framework-with-java-driver/ – 3rf Sep 18 '13 at 14:43
Also, in response to your earlier "exception", you need to be using mongodb's built-in date types for these operations to work, not strings. That exception can happen if even just one of your fields is a string in the entire collection. – 3rf Sep 18 '13 at 14:45
yeah, I know the thing about the data types (for Mongo a string is not the same as a number), that is why I opened another question: http://stackoverflow.com/questions/18868478/mongodb-java-driver-how-to-add-arrays-whith-mixed-data-types – SQL.injection Sep 18 '13 at 14:55
Thanks. The timezone of the browser can be retrieved with (new Date()).getTimezoneOffset() - Difference in minutes to UTC. Eastern hemisphere is negative. – Daniel Flippance Nov 23 '15 at 07:50
This is probably wrong half of the time because of daylight saving. Most countries using CET switch to CEST for the summertime at some random date; the difference then is +2 hours, not +1. See [this](http://www.timeanddate.com/time/zones/cet) for example. – Waldo Feb 26 '16 at 14:37

score 9 · Answer 2 · answered Mar 20 '18 at 15:17

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}
  }
}

score 7 · Answer 3 · answered Nov 19 '14 at 19:06

7

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
    }
};

answered Nov 19 '14 at 19:06

Trevor Meier

81
1
3

3

This is probably wrong half of the time because of daylight saving. Most countries using CET switch to CEST for the summertime at some random date; the difference then is +2 hours, not +1. See [this](http://www.timeanddate.com/time/zones/cet) for example. – Waldo May 25 '16 at 06:39
I'm in search of a solution to this problem right now and haven't found one yet but please do not use this solution as-is, because Daylight savings time will throw this for a loop. – Augie Gardner Dec 27 '17 at 11:51

score 4 · Answer 4 · answered Sep 10 '16 at 08:30

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}
  }
}

score 3 · Answer 5 · answered May 25 '16 at 06:47

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.

score 1 · Answer 6 · answered Mar 17 '19 at 14:13

The solution with timezone is a good one, but in version 3.6 you can also format the output using timezone, so, you get the result ready for use:

{
"$project":{
    "year_month_day": {"$dateToString": { "format": "%Y-%m-%d", "date": "$tDate", "timezone": "America/Chicago"}}
},
"$group":{
    "_id": "$year_month_day",
    "count":{"$sum":1}
}
}

Make sure that your "$match" also considers timezone, or else you will get wrong results.

TomoMiha · Answer 7 · 2019-10-18T14:31:51.927

Mongo stores the dates in UTC, so this is the procedure to get them in other zone

check that mongo saves the dates in UTC, insert some records etc.
get timezone offset with moment-timezone.js eg moment().tz('Europe/Zagreb').utcOffset() functions, for your specified timezone
Prepare $gte and $lte for $match stage (eg user input for dates 1.1.2019 - 13.1.2019.):
- If offset is positive subtract() those seconds in $match stage; If offset is negative add() those seconds in $match stage
Then normalize the dates (because $match stage will return them in UTC) to your zone like this: -if timezone offset is positive add() those seconds in $project stage; -if timezone offset is negative subtract() those seconds in $project stage.
$group goes last, this is important (because we want to group normalized results, and not $match-ed)

Basically it is this: shift input(s) to $match(UTC), and then normalize to your timezone.

score -10 · Answer 8 · answered Sep 17 '13 at 14:08

-10

<?php
    date_default_timezone_set('Asia/Karachi');
    $date=getdate(date("U"));
    $day = $date['mday'];
    $month =$date['mon'];
    $year = $date['year'];
    $currentDate = $year.'-'.$month.'-'.$day;
?>

answered Sep 17 '13 at 14:08

Ali Haider

92
2

how exactly does your "solution" makes '2013-09-16 23:45:00 UTC' be converted as a 2013-09-17 CET? – SQL.injection Sep 17 '13 at 14:46
another thing you don't explain is, how to "include" your code on the mongoDB aggregation framework, because any conversion (or timezone to be set) has to be done **before** the date object be ripped out of the hours, minutes, seconds and milliseconds. – SQL.injection Sep 17 '13 at 14:50

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

8 Answers8

Linked

Related