get first day of month in mongo. convert 'yyyy-mm-dd-hh-mm-ss.SSS' into timestamp of 'yyyy-mm-01-00-00-00.000'

Question

[
  {
    "rating": 4,
    "createdAt": ISODate("2016-08-08T15:32:41.262+0000")
  },
  {
    "rating": 4,
    "createdAt": ISODate("2016-08-08T15:32:41.262+0000")
  },
  {
    "rating": 3,
    "createdAt": ISODate("2016-07-01T15:32:41.262+0000")
  },
  {
    "rating": 5,
    "createdAt": ISODate("2016-07-01T15:32:41.262+0000")
  }
]

This is my dataset.

I use this to get the month number.

[
    { 
        "$project": {
            "createdAtMonth": { "$month": "$createdAt" },
            "rating": 1
        }
    },
    {
         "$group": {
             "_id": "$createdAtMonth",
             "average": { "$avg": "$rating" },
             "month": { "$first": "$createdAtMonth" }
         }
    }
]

I need to get the start date of that month in timestamp. How can I do that?

Expected output:

[{
    "_id": 8,
    "average": 4,
    "month": 8,
    "time": 1469989800000
}, {
    "_id": 7,
    "average": 4,
    "month": 7,
    "time": 1467311400000
}]

date might not be correct in this output. but basis on the input, I should get two object, one for july and one for august

Answer 1

One way to achieve it is to split the task into 2 subtasks

1. Convert timestamp of yyyy-mm-dd-hh-mm-ss.SSS to timestamp of yyyy-mm-dd-00-00-00.000. This could be done using $mod arithmetic aggregation operator.
2. Convert timestamp of yyyy-mm-dd-00-00-00.000 to timestamp of yyyy-mm-01-00-00-00.000. This could be done by subtracting (dd - 1) * 24 * 60 * 60 * 1000 from the timestamp of yyyy-mm-dd-00-00-00.000.

Aggregation pipeline

[
  {
    $project:
      {
        "createdAtMonth": { $month: "$createdAt" },
        "createdAt": 1,
        "rating": 1
      }
  },
  {
    $group:
      {
        "_id": "$createdAtMonth",
        "createdAt": { $first: "$createdAt" },
        "average": { "$avg": "$rating" }
      }
  },
  {
    $project:
      {
        "_id": 0,
        "month": "$_id",
        "average": 1,
        "DD_1": { $subtract: [ { $dayOfMonth: "$createdAt" }, 1 ] },
        "t_stamp": { $subtract: [ "$createdAt", new Date("1970-01-01") ] }
      }
  },
  {
    $project:
      {
        "month": 1,
        "average": 1,
        "time":
          {
            $subtract:
              [
                { $subtract: [ "$t_stamp", { $mod: [ "$t_stamp", 24 * 60 * 60 * 1000 ] } ] },
                { $multiply: [ "$DD_1", 24 * 60 * 60 * 1000 ] }
              ]
          }
      }
  }
]

Sample

{ "_id": 1, "rating": 4, "createdAt": ISODate("2016-08-08T15:32:41.262Z") }
{ "_id": 2, "rating": 3, "createdAt": ISODate("2016-08-08T15:32:41.262Z") }
{ "_id": 3, "rating": 3, "createdAt": ISODate("2016-07-01T15:32:41.262Z") }
{ "_id": 4, "rating": 5, "createdAt": ISODate("2016-07-01T15:32:41.262Z") }

Results

{ "average": 4, "month": 7, "time": 1467331200000 }
{ "average": 3.5, "month": 8, "time": 1470009600000 }

You can use online Epoch & Unix Timestamp convertor to verify that

1467331200000 is timestamp of Fri, 01 Jul 2016 00:00:00 GMT

1470009600000 is timestamp of Mon, 01 Aug 2016 00:00:00 GMT