[SERVER-16347] Support for date aggregation operator for creating dates from parts Created: 28/Nov/14  Updated: 06/Dec/22  Resolved: 17/Aug/17

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Alan Spencer Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 4
Labels: expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-28613 Add the $dateToParts and $dateFromPar... Closed
is duplicated by SERVER-9626 Aggregation Framework needs to suppor... Closed
Related
is related to SERVER-30523 dateFromParts should not reject "out-... Closed
Assigned Teams:
Query
Sprint: Query 12 (04/04/16), Query 13 (04/22/16)
Participants:

 Description   

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
        }
    }
    }
])



 Comments   
Comment by David Storch [ 17/Aug/17 ]

derick, yep good call. Closing as a dup of SERVER-28613.

Comment by Derick Rethans [ 16/Aug/17 ]

As SERVER-28613 is now done, shall we close this as duplicate?

Comment by David Storch [ 09/Jun/17 ]

This work is currently planned as part of a larger effort to add time zone support to the aggregation framework. Please watch SERVER-28613, which tracks the addition of new $dateToParts and $dateFromParts aggregation expressions.

Comment by Asya Kamsky [ 04/Feb/16 ]

Was made duplicate of a ticket that's too general, uncoupling to allow separate triage.

Generated at Thu Feb 08 03:40:45 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.