$dateFromParts (aggregation)

$dateFromParts (aggregation)

Definition

$dateFromParts

New in version 3.6.

Constructs and returns a Date object given the date’s constituentproperties.

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 inISO week dateformat 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 datefields when constructing your $dateFromParts inputdocument.

FieldRequired/OptionalDescriptionyearRequired if not using isoWeekYearCalendar year. Can be any expression that evaluates to a number.

Value range: 0-9999isoWeekYearRequired if not using yearISO Week Date Year. Can be any expression that evaluates to a number.

Value range: 0-9999monthOptional. 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 thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.isoWeekOptional. 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 thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.dayOptional. 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 thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.isoDayOfWeekOptional. 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 thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.hourOptionalHour. 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 thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.minuteOptionalMinute. Can be any expression that evaluates to a number.

Defaults to 0.

Value range: 0-59Starting in MongoDB 4.0, if the number specified is outside thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.secondOptionalSecond. 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 thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.millisecondOptionalMillisecond. 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 thisrange, $dateFromParts incorporates the difference inthe date calculation. See Value Range for examples.timezoneOptional<timezone> can be any expression that evaluates to a string whosevalue 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, seeExpressions.

Behavior

Value Range

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

Value is Greater than the Range

Consider the following $dateFromParts expression wherethe month field value is 14, which is 2 months greater than themaximum 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 1and setting the month to 2 to return:

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

Value is Less than the Range

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

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

The expression calculates the date by decreasing the year by 1and 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 offsetif 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 DSToffset for the Olson Timezone Identifier. The example uses the$hour and $minute operators to return thecorresponding 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 toconstruct 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")
}