2

I have a collection comprising of per minute data with fields : date(datatype-ISODate) and clicks(datatype-long). I need to group and sum clicks per day.

db.collectionname.aggregate([
    {'$group':{'_id':{date:{$dayOfYear:'$date'}},clicks:{$sum:'$clicks'}}}])

this gives me day of year wise clicks, BUT i want the day in timestamp(for further use in other proccesses).

I have tried using "$substr" to group with only date part not time(works well), but even in that i cannot convert the date to timestamp.

db.collectionname.aggregate([
        {'$group':{'_id':{date:{$substr:["$date",0,10]}},clicks:{$sum:'$clicks'}}}])

MySQL had a "UNIX_Timestamp()" function to convert date to timestamp , anything similar in MongoDB???

like ISODate(2016-03-30T00:00:00.000Z) to 1459276200000 (13 digits i.e. milliseconds) OR 2016-03-30 to 1459276200000 .

This is a different question, as here I am asking for a solution to convert the date-(2016-03-30) to timestamp. Other questions either have a pre-created field for this date or are using any ISODate not specified to 00.00.00.000 time.

cartman619
  • 552
  • 4
  • 17
  • Using "math" on `Date` objects is the way to actually return a `Date`, as opposed to what the ["Date Aggregation Operators"](https://docs.mongodb.org/manual/reference/operator/aggregation-date/) actually do. That is what the [example](http://stackoverflow.com/a/32137234/5031275) there is showing. – Blakes Seven Mar 30 '16 at 11:57
  • @BlakesSeven: Thanks for quick reply. The example you linked gives back ISODate and I require a millisecond timestamp. Any way to convert it ? – cartman619 Mar 30 '16 at 12:04
  • Sure. Don't [`$add`](https://docs.mongodb.org/manual/reference/operator/aggregation/add/) another BSON Date. The ["Accepted Answer"](http://stackoverflow.com/a/32137234/5031275) there does **exactly** that, and just returns a numeric "timestamp" rather than a BSON Date that will cast directly into a `Date` object in any language, via the driver. – Blakes Seven Mar 30 '16 at 12:09
  • 1
    Had to use new Date('1970-01-01' ) to get timestamp.Thanks man. – cartman619 Mar 30 '16 at 12:20
  • `new Date(0)` is basically the same thing for an "epoch" date. Which is the key with [`$subtract`](https://docs.mongodb.org/manual/reference/operator/aggregation/subtract/) to make a `Date` a "timestamp". You're welcome. And FYI, it's "Mamn"! Such a "Guy" centric world here :( – Blakes Seven Mar 30 '16 at 12:24

0 Answers0