Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-16347

Support for date aggregation operator for creating dates from parts

    • Type: Icon: New Feature New Feature
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Aggregation Framework
    • Query
    • Query 12 (04/04/16), Query 13 (04/22/16)

      Syntax

      {$newDate: [<year>, <month>, <day>, <hour>, <minutes>, <seconds>, <millis>]}
      

      Examples

      db.coll.insert([
        {_id: 0, year: 2016, month: 1, day: 1, hour: 5, minutes: 0, seconds: 0, millis: 0}
      ]);
      > db.coll.aggregate([{
        $project: {
          _id: 0
          date: {$newDate: [
            "$year",
            "$month",
            "$day",
            "$hour",
            "$minutes",
            "$seconds",
            "$millis"
          ]}
               }
      }]);
      {date: ISODate("2016-01-01T05:00:00:000Z")}
      
      
      // Example 2 - Truncate a date to only year/month/day. Note this can be very useful in a
      // $group, so that you can combine all sales on a given day, or something like that.
      > db.coll.insert([
        {_id: 0, date: new IsoDate()}
      ]);
      > db.coll.aggregate([{
        $project: {
          truncatedDate: {
            $newDate: [
              {$year: "$date"},
              {$month: "$date"},
              {$dayOfMonth: "$date"}
            ]
          }
        }
      }])
      {truncatedDate: ISODate("2016-04-06T00:00:00:000Z")}
      
      // Example 3 - Create a date from milliseconds.
      > db.coll.insert([
        {_id: 0 millis: 1451624400000}
      ]);
      > db.coll.aggregate([{
        $project: {
          date: {$newDate: [1970, 1, 1, 0, 0, 0, "$millis"]}  // "$millis" will overflow.
        }
      }])
      {date: ISODate("2016-01-01T05:23:00:000Z")}
      

      Notes

      • $newDate can accept anywhere from 1 to 7 arguments. If fewer than seven are specified, the remaining values are filled with zero.
      • Values that are too large should carry. For example, day: 0, hour: 24 is equivalent to day: 1, hour: 0
      • Negative values should "subtract". For example, day: 2, hour: -1 is equivalent to day: 1, hour 23

      Errors

      • If an input is not integral.
      • if more than 7 or fewer than 1 elements are passed in

      Old Description
      A new data aggregation operator that returns a date from other forms/composite parts.

      It could look something like:

      { $date: <expression> }
      

      Where <expression> evaluates to a number or a string.

      • If a string conforms to the ISODate format such as "2014-11-28T16:09:53.082Z"
      • If a number, represents milliseconds since the epoch.

      Or it could be from the constituent parts.

      { $date: { year: <expression>, month: <expression>, day: <expression>, hours: <expression>, minutes: <expression>, seconds: <expression>, milliseconds: <expression> } }
      

      This would mean that grouping data by a given calendar day could be (example taken from Stupid date tricks with Aggregation Framework)

      db.coll.aggregate([
          {
              $project: {
                  "type" : 1,
                  "PINGS" : 1,
                  "_id" : 0,
                  date: { $date: {
                      year: {$year: "$time"},
                      month: {$month: "$time"},
                      day: {$day: "$time"},
                      hours: 0,
                      minutes: 0,
                      seconds: 0,
                      milliseconds: 0 } }
              }
          }
          ,
          {"$group" : {
              "_id" : {
                  "type" : "$type",
                  "dt" : "$date"
              },
              "total" : {
                  "$sum" : "$PINGS"
              },
              "cnt" : {
                  "$sum" : 1
              }
          }
          }
      ])
      

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            alan.spencer Alan Spencer
            Votes:
            4 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: