$dateFromParts (aggregation)¶

On this page

Definition
Behavior
Example

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:

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:

Field	Required/Optional	Description	Possible Values
`year`	Required if not using `isoWeekYear`	Calendar year.	`0`-`9999`
`isoWeekYear`	Required if not using `year`	ISO Week Date Year.	`0`-`9999`
`month`	Optional. Can only be used with `year`.	Month. Defaults to `1`.	`1`-`12`
`isoWeek`	Optional. Can only be used with `isoWeekYear`.	Week of year. Defaults to `1`.	`1`-`53`
`day`	Optional. Can only be used with `year`.	Day of month. Defaults to `1`.	`1`-`31`
`isoDayOfWeek`	Optional. Can only be used with `isoWeekYear`.	Day of week (Monday `1` - Sunday `7`). Defaults to `1`.	`1`-`7`
`hour`	Optional	Hour. Defaults to `0`.	`0`-`23`
`minute`	Optional	Minute. Defaults to `0`.	`0`-`59`
`second`	Optional	Second. Defaults to `0`.	`0`-`59`
`millisecond`	Optional	Millisecond. Defaults to `0`.	`0`-`999`
`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.	Timezone expression

Important

You cannot combine the use of calendar dates and ISO week date fields when constructing your $dateFromParts input document.

Behavior¶

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

← $cond (aggregation) $dateToParts (aggregation) →