$dateFromParts (aggregation)

On this page

Definition

Behavior
Example

This version of the documentation is archived and no longer supported. View the current documentation to learn how to upgrade your version of MongoDB.

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. Starting in MongoDB 4.4, the lower bound for this value is `1`. In previous versions of MongoDB, the lower bound was `0`.
`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. Starting in MongoDB 4.4, the lower bound for this value is `1`. In previous versions of MongoDB, the lower bound was `0`.
`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

Value Range

Starting in MongoDB 4.4, the supported value range for year and isoWeekYear is 1-9999. In prior versions of MongoDB, the lower bound for these values was 0 and the supported value range was 0-9999.

If the value specified for fields other than year, isoWeekYear, 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):

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

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:

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

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:

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

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:

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

Back

$cosh (aggregation)

$dateToParts (aggregation)