• Reference >
• Operators >
• Aggregation Pipeline Operators >
• $dateFromParts (aggregation)

$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:

copy

{
    $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:

copy

{
    $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: 0-9999
isoWeekYear	Required if not using year	ISO Week Date Year. Can be any expression that evaluates to a number. Value range: 0-9999
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 Starting in MongoDB 4.0, 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 Starting in MongoDB 4.0, 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 Starting in MongoDB 4.0, 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 Starting in MongoDB 4.0, 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 Starting in MongoDB 4.0, 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 Starting in MongoDB 4.0, 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 Starting in MongoDB 4.0, 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 Starting in MongoDB 4.0, 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

Value Range

Starting in MongoDB 4.0, if the value specified for fields other than year, isoYear, and timezone is outside the valid range, $dateFromParts carries or subtracts the difference from other date parts to calculate the date.

Value is Greater than the Range

Consider the following $dateFromParts expression where the month field value is 14, which is 2 months greater than the maximum value of 12 months(or 1 year):

copy

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

The expression calculates the date by increasing the year by 1 and setting the month to 2 to return:

copy

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

Value is Less than the Range

Consider the following $dateFromParts expression where the month field value is 0, which is 1 month less than the minimum value of 1 month:

copy

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

The expression calculates the date by decreasing the year by 1 and setting the month to 12 to return:

copy

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

Time Zone

When using an Olson Timezone Identifier in the <timezone> field, MongoDB applies the DST offset if applicable for the specified timezone.

For example, consider a sales collection with the following document:

copy

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

The following aggregation illustrates how MongoDB handles the DST offset for the Olson Timezone Identifier. The example uses the $hour and $minute operators to return the corresponding portions of the date field:

copy

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

The operation returns the following result:

copy

{
   "_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:

copy

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:

copy

{
  "_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")
}

←   $cos (aggregation) $dateToParts (aggregation)  →

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.