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