Mongo project error in aggregation

Question

Mongo project is giving error when date is in format like YYYY-01-01

Date in Mongo

[pd] => MongoDate Object
    (
        [sec] => 946665000
        [usec] => 0
    )
[pn] => JP2000285176A

PHP date returns

date('Y-m-d',946665000) => 2000-01-01

MongoDB aggregation

db.patents.aggregate({'$match':{'pn':'JP2000285176A'}},{'$project':{'pn':1,'pd':{'$year':'$pd'}}})

Result

{
"result" : [
    {
        "_id" : ObjectId("530dce78b25d9d526f44e104"),
        "pd" : 1999,
        "pn" : "JP2000285176A"
    }
],
"ok" : 1
}

Aggregation returns 1999 instead of 2000

Update

Unresolved issue

Timezone support in date operators at query time

Can you print the value of "$pd" (entire date object) instead of trying to extract the year, so we know the complete date that's stored in MongoDB? — Anand Jayabalan, Feb 27 '14 at 13:05

score 4 · Accepted Answer · edited May 23 '17 at 10:31

4

I think this is a timezone related issue.

See How to agregate by year-month-day on a different timezone for a possible solution.

edited May 23 '17 at 10:31

Community

1
1

answered Feb 27 '14 at 13:22

Yuriy Manoylo

111
4

Fr0zenFyr · Answer 2 · 2014-02-28T09:16:46.120

Like you updated in your question, this issue is already discussed in SERVER-6310 which will be resolved in version 2.7 desired.

Now, because all the time is stored in UTC in Mongodb, you can use $project to shift the time to your timezone. To add time there is $add and to subtract time there is $subtract. Note that the addition/subtraction has to be done in milliseconds[hours*60*60*1000].

For example, in your case, since you are from India[+5:30], you can add 5:30 hrs to UTC[or GMT].

db.patents.aggregate(
           {'$match':{'pn':'JP2000285176A'}},
           {'$project':{'pn':1,'pd':{'$year':{$add:['$pd',5.5*60*60*1000]}}}}
)

#REF

Mongo project error in aggregation

Date in Mongo

MongoDB aggregation

Update

2 Answers2