Navigation

$dateFromParts (aggregation)

On this page

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>,
        'milliseconds': <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>,
        'milliseconds': <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):

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