Docs Menu
Docs Home
/
MongoDB Manual
/ / /

$dateFromParts (aggregation)

On this page

  • Definition
  • Behavior
  • Example
$dateFromParts

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 Expression Operators.

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.

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

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

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
}

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

$dateDiff (aggregation)