$dateFromParts (aggregation)

Definition

$dateFromParts

New in version 3.6.

Constructs and returns a Date object given the date's constituent properties.

The $dateFromParts expression has the following syntax:

{
    $dateFromParts : {
        'year': <year>, 'month': <month>, 'day': <day>,
        'hour': <hour>, 'minute': <minute>, 'second': <second>,
        'millisecond': <ms>, 'timezone': <tzExpression>
    }
}

You can also specify your constituent date fields in ISO week date format using the following syntax:

{
    $dateFromParts : {
        'isoWeekYear': <year>, 'isoWeek': <week>, 'isoDayOfWeek': <day>,
        'hour': <hour>, 'minute': <minute>, 'second': <second>,
        'millisecond': <ms>, 'timezone': <tzExpression>
    }
}

The $dateFromParts takes a document with the following fields:

Important

You cannot combine the use of calendar dates and ISO week date fields when constructing your $dateFromParts input document.

Field	Required/Optional	Description
year	Required if not using isoWeekYear	Calendar year. Can be any expression that evaluates to a number. Value range: 1-9999 If the number specified is outside this range, $dateFromParts errors. The lower bound for this value is 1.
isoWeekYear	Required if not using year	ISO Week Date Year. Can be any expression that evaluates to a number. Value range: 1-9999 If the number specified is outside this range, $dateFromParts errors. The lower bound for this value is 1.
month	Optional. Can only be used with year.	Month. Can be any expression that evaluates to a number. Defaults to 1. Value range: 1-12 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
isoWeek	Optional. Can only be used with isoWeekYear.	Week of year. Can be any expression that evaluates to a number. Defaults to 1. Value range: 1-53 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
day	Optional. Can only be used with year.	Day of month. Can be any expression that evaluates to a number. Defaults to 1. Value range: 1-31 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
isoDayOfWeek	Optional. Can only be used with isoWeekYear.	Day of week (Monday 1 - Sunday 7). Can be any expression that evaluates to a number. Defaults to 1. Value range: 1-7 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
hour	Optional	Hour. Can be any expression that evaluates to a number. Defaults to 0. Value range: 0-23 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
minute	Optional	Minute. Can be any expression that evaluates to a number. Defaults to 0. Value range: 0-59 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
second	Optional	Second. Can be any expression that evaluates to a number. Defaults to 0. Value range: 0-59 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
millisecond	Optional	Millisecond. Can be any expression that evaluates to a number. Defaults to 0. Value range: 0-999 If the number specified is outside this range, $dateFromParts incorporates the difference in the date calculation. See Value Range for examples.
timezone	Optional	<timezone> can be any expression that evaluates to a string whose value is either: an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form: +/-[hh]:[mm], e.g. "+04:45", or +/-[hh][mm], e.g. "-0530", or +/-[hh], e.g. "+03". For more information on expressions, see Expressions.

Behavior

{ $dateFromParts: { 'year' : 2017, 'month' : 14, 'day': 1, 'hour' : 12  } }

ISODate("2018-02-01T12:00:00Z")

{ $dateFromParts: { 'year' : 2017, 'month' : 0, 'day': 1, 'hour' : 12  } }

ISODate("2016-12-01T12:00:00Z")

{
   "_id" : 1,
   "item" : "abc",
   "price" : 20,
   "quantity" : 5,
   "date" : ISODate("2017-05-20T10:24:51.303Z")
}

db.sales.aggregate([
{
   $project: {
      "nycHour": {
         $hour: { date: "$date", timezone: "-05:00" }
       },
       "nycMinute": {
          $minute: { date: "$date", timezone: "-05:00" }
       },
       "gmtHour": {
          $hour: { date: "$date", timezone: "GMT" }
       },
       "gmtMinute": {
          $minute: { date: "$date", timezone: "GMT" } },
       "nycOlsonHour": {
          $hour: { date: "$date", timezone: "America/New_York" }
       },
       "nycOlsonMinute": {
          $minute: { date: "$date", timezone: "America/New_York" }
       }
   }
}])

{
   "_id": 1,
   "nycHour" : 5,
   "nycMinute" : 24,
   "gmtHour" : 10,
   "gmtMinute" : 24,
   "nycOlsonHour" : 6,
   "nycOlsonMinute" : 24
}

Example

The following aggregation uses $dateFromParts to construct three date objects from the provided input fields:

db.sales.aggregate([
{
   $project: {
      date: {
         $dateFromParts: {
            'year' : 2017, 'month' : 2, 'day': 8, 'hour' : 12
         }
      },
      date_iso: {
         $dateFromParts: {
            'isoWeekYear' : 2017, 'isoWeek' : 6, 'isoDayOfWeek' : 3, 'hour' : 12
         }
      },
      date_timezone: {
         $dateFromParts: {
            'year' : 2016, 'month' : 12, 'day' : 31, 'hour' : 23,
            'minute' : 46, 'second' : 12, 'timezone' : 'America/New_York'
         }
      }
   }
}])

The operation returns the following result:

{
  "_id" : 1,
  "date" : ISODate("2017-02-08T12:00:00Z"),
  "date_iso" : ISODate("2017-02-08T12:00:00Z"),
  "date_timezone" : ISODate("2017-01-01T04:46:12Z")
}