SQL.injection
SQL.injection

Reputation: 2647

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

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.

Upvotes: 26

Views: 13189

Answers (8)

TomoMiha
TomoMiha

Reputation: 1279

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.

Upvotes: 0

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.

Upvotes: 1

s7vr
s7vr

Reputation: 75964

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

Upvotes: 10

helgetan
helgetan

Reputation: 1407

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

Upvotes: 4

Waldo
Waldo

Reputation: 433

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.

Upvotes: 3

Trevor Meier
Trevor Meier

Reputation: 81

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

Upvotes: 7

3rf
3rf

Reputation: 1164

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.

Upvotes: 16

Ali Haider
Ali Haider

Reputation: 102

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

Upvotes: -10

Related Questions