Reputation: 2647
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
Reputation: 1279
Mongo stores the dates in UTC, so this is the procedure to get them in other zone
Basically it is this: shift input(s) to $match(UTC), and then normalize to your timezone.
Upvotes: 0
Reputation: 167
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
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
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
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
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
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
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