How to obtain Date (without time) with aggregation

[Fumiko]

Hello, I'm new to mongodb.  I apologize if this is somewhere in the doc already.

I'm trying to $project date part only (time = midnight is fine) from ISODate (with timestamp) fields.

Could someone please let me know how to do that?

Thank you so much for your help in advance!

[Jeff Lee]

Hi Fumiko,

I think you want something like:

db.thedates.save({ thedate:new Date() })

db.thedates.aggregate({ $project:{ year:{$year:["$thedate"]} , month:{$month:["$thedate"]} , day:{$dayOfMonth:["$thedate"]} } })

or possibly this if you're trying to group/count by day?

db.thedates.drop()

db.thedates.save({id:1, thedate:new Date('03 March 2012 03:00:00')})

db.thedates.save({id:2, thedate:new Date('03 March 2012 04:00:00')})

db.thedates.save({id:3, thedate:new Date('04 March 2012 03:00:00')})

db.thedates.save({id:4, thedate:new Date('05 March 2012 03:00:00')})

db.thedates.aggregate(

{ $project:{ year:{$year:["$thedate"]} , month:{$month:["$thedate"]} , day:{$dayOfMonth:["$thedate"]} } },

{ $group: { _id:{year:"$year", month:"$month", day:"$day"}, thecount:{$sum:1}}}

)

--
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
See also the IRC channel -- freenode.net#mongodb

[Fumiko]

Hello Jeff,

Thank you for your suggestion!  I should have mentioned this in my previous post.  I already have the parts you mentioned.

db.thedates.aggregate({ $project:{ year:{$year:["$thedate"]} , month:{$month:["$thedate"]} , day:{$dayOfMonth:["$thedate"]} } })

and

db.thedates.aggregate(

{ $project:{ year:{$year:["$thedate"]} , month:{$month:["$thedate"]} , day:{$dayOfMonth:["$thedate"]} } },

{ $group: { _id:{year:"$year", month:"$month", day:"$day"}, thecount:{$sum:1}}}

)

I am aggregating daily that way.  The problem I have is that the field in the collection is in the ISODate with timestamp.  However, if there is such a way, I would like to return the result set with the date value and the count (in this example) in a similar format as follows:

{ "chartDate" : ISODate("2013-01-15T00:00:00.000Z")

  "count" : 286 }

I know I could save date part only to the collection along with the field with timestamp as we insert new data, which makes aggregation simpler.  However, I wonder if there is a way to get date part only while aggregating (probably one of the last steps in the pipeline) without changing the current structure of the collection.

Thank you.

[Wiwwo.com]

Hi there Fumiko!
Why not to convert date toString, and then do a $substr on that field? ;)

Ciao!

[Jeff Lee]

Fumiko,

I could be wrong but I don't think it's possible to do that within the aggregation framework.  SERVER-6195 may be what you want but I believe you need to use map-reduce if you want the data represented that way and can't do the conversion on the client side.

Perhaps someone that is more familiar with aggregation may be able to provide you with a better solution.

Regards

[Fumiko]

Hi Jeff,

I just realized I haven't replied :(  Thank you so much for your feedback.  That's exactly the kind of answer I needed to hear (that it isn't possible and I need to seek other ways to workaround).  Unfortunately, SERVER-6195 isn't what we are looking for.
We ended up having a field called which is a number of days since epoch.  We are using it to group by day as well as group by a number of days.
Thank you, again.

Fumiko

[Sam Kirton]

Hi Fumiko,

I'm 5 years late to the party but I solved the issue with:

Podcast.aggregate([

{

$project: { 

now: new Date()

}

},

{

$project: { 

nowDateOnly: {

$subtract:[

'$now', {

$add: [

{  

$multiply: [{$hour: "$now"},3600000]

},

{

$multiply: [{$minute: "$now"},60000]

},

{

$multiply: [{$second: "$now"},1000]

},

{

$millisecond: "$now"

}

]

}

]

}

}

},

]);