Aggregation Framework Expression $year/$month/$dayOfMonth return wrong value.

[songhe yang]

I have a collection, detail as follows:

e.g: userlog document:

{ "uselog_id" : 53994879, "user_id" : 277797, "visit_time" :
ISODate("2012-02-10T16:00:03Z"), "_id" :
ObjectId("4f379e0401319ab316000014") }

command:

db.runCommand({aggregate: "userlog", pipeline: [{$project: {user_id:
1, visit_date: {$year: "$visit_time"}, _id: 0}}, {$match: {user_id:
277797}}]})


I will get the result:

> db.runCommand({aggregate: "userlog", pipeline: [{$project: {user_id: 1, visit_date: {$year: "$visit_time"}, _id: 0}}, {$match: {user_id: 277797}}]})
{
"result" : [
{
"user_id" : 277797,
"visit_date" : 1970
}
],
"ok" : 1
}

Why $year return 1970, it should be 2012 ?

BTW: $month, $dayOfMonth also have wrong return value.

[Scott Hernandez]

The field name is mispelled. Should be visit_time not visit_date.

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[songhe yang]

I extract the year from "visit_time", give it a new field name
"visit_date", so it should not be the problem of here.

On Feb 12, 10:24 pm, Scott Hernandez <scotthernan...@gmail.com> wrote:
> The field name is mispelled. Should be visit_time not visit_date.

[songhe yang]

I used the same method as "http://groups.google.com/group/mongodb-user/
browse_thread/thread/df0df0ce5d0ac395" to test, i will get the
following result:

e.g: "visit_time" : ISODate("2012-02-10T16:00:03Z")

db.runCommand({
aggregate: 'userlog',
pipeline: [
{

$group: {
_id: {
OriginationYear: { $year: "$visit_time" },
OriginationMonth: { $month: "$visit_time" },
OriginationDay: { $dayOfMonth: "$visit_time" }
},
Total: { $sum: 1 }
}
}
]
});

{
"result" : [
{
"_id" : {
"OriginationYear" : 1970,
"OriginationMonth" : 1,
"OriginationDay" : 22
},
"Total" : 38673
},
{
"_id" : {
"OriginationYear" : 1970,
"OriginationMonth" : 1,
"OriginationDay" : 21
},
"Total" : 127599
}
],
"ok" : 1
}

The year, month and day are not correct.

On Feb 12, 10:24 pm, Scott Hernandez <scotthernan...@gmail.com> wrote:

> The field name is mispelled. Should be visit_time not visit_date.

[Scott Hernandez]

Sorry, I clearly misread.

I tried on 2.1.0 (osx64 bit) and it worked fine:

> db.runCommand({aggregate: "userlog", pipeline: [{$project: {user_id:

... 1, visit_date: {$year: "$visit_time"}, _id: 0}}, {$match: {user_id:
... 277797}}]})
{
"result" : [
{
"user_id" : 277797,
"visit_date" : 2012
}
],
"ok" : 1

[songhe yang]

I am come from China, if it has any affect about the time zone?

[Scott Hernandez]

What os/platform are you testing on?

[songhe yang]

linux 2.6.32-38-generic 32bit on my local machine.

I can test it again on 64 bit linux server later.

[songhe yang]

Hi Scott,

I find that the reversion of mongodb is "mongodb-linux-
i686-2012-01-09", so i upgrade to the latest mongodb revision "mongodb-
linux-i686-2.1.0", then on my 32 bit linux machine i got the following
result about test case:

{ ok: 1,
result:
[ { count: 1540, _id: '2012-2-10' },
{ count: 12042, _id: '2012-2-11' },
[length]: 2 ] }


But it is still not expect result, because all of document's
visit_time is 2012-2-11.

> db.userlog.find().count()
166272
> db.userlog.find({visit_time: {$gte: new Date(2012,1,11)}}).count()
166272

Thanks

[songhe yang]

64bit linux still have the same problem.

Step#1: userlog document

> db.userlog.find()
{ "_id" : ObjectId("4f379e0401319ab316000001"), "uselog_id" :
53994860, "user_id" : 236246, "username" : "不变的点点承诺", "visit_time" :
ISODate("2012-02-10T16:00:00Z"), "visit_ip" : "114.216.76.4",
"report_title" : "行情趋势如何", "report_url" : "cube.data.taobao.com/s/
market_trend#cid:50010850|start:2012-01-11|end:2012-02-09|dt:2",
"report_path" : "女装/女士精品>连衣裙", "report_scale" : "category", "com_id" :
236246 }

Step#2: confirm visit_time is 2012-2-11

> db.userlog.find({visit_time: {$gte: new Date(2012, 1, 11)}})
{ "_id" : ObjectId("4f379e0401319ab316000001"), "uselog_id" :
53994860, "user_id" : 236246, "username" : "不变的点点承诺", "visit_time" :
ISODate("2012-02-10T16:00:00Z"), "visit_ip" : "114.216.76.4",
"report_title" : "行情趋势如何", "report_url" : "cube.data.taobao.com/s/
market_trend#cid:50010850|start:2012-01-11|end:2012-02-09|dt:2",
"report_path" : "女装/女士精品>连衣裙", "report_scale" : "category", "com_id" :
236246 }

Step#2: run pipeline case, but get visit_day 10
> db.runCommand({aggregate: "userlog", pipeline: [{$project: {user_id: 1, visit_year: {$year: "$visit_time"}, visit_month: {$month: "$visit_time"}, visit_day: {$dayOfMonth: "$visit_time"}, _id: 0}}]})
{
"result" : [
{
"user_id" : 236246,
"visit_year" : 2012,
"visit_month" : 2,
"visit_day" : 10
}
],
"ok" : 1
}


So it should be the problem $dayOfMonth, maybe the timezone has the
affect of it.

[Randolph Tan]

Hi,

I tried this in my 64-bit Ubuntu and I was not able to reproduce the
1970 problem. I also set my timezone to Beijing and that didn't help.
This is what I did:

> db.userlog.insert({ "uselog_id" : 53994879, "user_id" : 277797, "visit_time" :

ISODate("2012-02-10T16:00:03Z"), "_id" :

ObjectId("4f379e0401319ab316000014") } ) //copy and pasted from your
post
> db.runCommand({aggregate: "userlog", pipeline: [{$project: {user_id: 1, visit_date: {$year: "$visit_time"}, _id: 0}}, {$match: {user_id: 277797}}]}) // copy and pasted again

{
"result" : [
{
"user_id" : 277797,
"visit_date" : 2012
}
],
"ok" : 1
}

Can you

By the way, I don't understand what was the problem in your newer
post. The result you showed us looks correct.

ISODate("2012-02-10T16:00:00Z"),

{
"user_id" : 236246,
"visit_year" : 2012, // 2012-
"visit_month" : 2, // 02-
"visit_day" : 10 // 10
}

Thanks!

[songhe yang]

I changed the mongodb to newest reversion 2.1 and tested again, the
year and the month are right, but the day of
ISODate（"2012-02-10T16:00:00Z） is 11 at beijing which I got 10 using
$dayOfMonth .

I store Beijing time 2012-02-11 00:00:00 in mongodb, it will show as
ISODate("2012-02-10T16:00:00 ")

[Randolph Tan]

How did you save your time? Can you show us an example of how you were
doing your insert? And if you are not using the shell, can you also
tell us which driver version you are using?

Thanks!

[songhe yang]

Sure, i can use the shell to save a document which only contains
visit_time, then query it back:


xuanhouysh@localhost:~$ mongo
MongoDB shell version: 2.1.0
connecting to: test
> db.userlog.insert({visit_time: new Date(2012, 1, 11)})
> db.userlog.find()
{ "_id" : ObjectId("4f3f13cfb6d98fa879f55c0b"), "visit_time" :

ISODate("2012-02-10T16:00:00Z") }

> exit
bye

Hopeful this will help you.

> > > > > >> >> > BTW:  $month, $dayOfMonthalso have wrong return value.

[Randolph Tan]

Oh, I see now. The reason why you are seeing that discrepancy is
because the javascript month stores the month in 0-11 (0 for January).
So when you say new Date(2012, 1, 14), it's actually means Feb 14. You
can try this to see what I mean:

var now = new Date();
now.getMonth(); // this will printout month now - 1

And for the discrepancy in day, you are absolutely right that it has
to do with time zone. This is because Mongo converts time created from
Date into UTC based time.

Hope that helps :)