• Reference >
• Operators >
• Aggregation Pipeline Operators >
• $dateTrunc (aggregation)

$dateTrunc (aggregation)

Definition

$dateTrunc

New in version 5.0.

Truncates a date.

$dateTrunc syntax:

{
   $dateTrunc: {
      date: <Expression>,
      unit: <Expression>,
      binSize: <Expression>,
      timezone: <tzExpression>,
      startOfWeek: <Expression>
   }
}

Field	Required / Optional	Description
date	Required	The date to truncate, specified in UTC. The date can be any expression that resolves to a Date, a Timestamp, or an ObjectID.
unit	Required	The unit of time, specified as an expression that must resolve to one of these strings: year quarter week month day hour minute second Together, binSize and unit specify the time period used in the $dateTrunc calculation.
binSize	Optional	The numeric time value, specified as an expression that must resolve to a positive non-zero number. Defaults to 1. Together, binSize and unit specify the time period used in the $dateTrunc calculation.
timezone	Optional	The timezone for the $dateTrunc calculation, specified as an expression that must resolve to a string that contains one of these values: Olson Timezone Identifier UTC Offset If no timezone is provided, the $dateTrunc calculation is performed in UTC. Format Examples Olson Timezone Identifier America/New_York Europe/London GMT UTC Offset +/-[hh]:[mm] (example, +04:45) +/-[hh][mm] (example, -0530) +/-[hh] (example, +03)
startOfWeek	Optional	The start of the week. Used when unit is week. Defaults to Sunday. startOfWeek is an expression that must resolve to one of these case insensitive strings: monday (or mon) tuesday (or tue) wednesday (or wed) thursday (or thu) friday (or fri) saturday (or sat) sunday (or sun)

See also

• Expressions
• BSON Types

Behavior

$dateTrunc:

• Returns null if:
  • any of the input fields except startOfWeek is missing or set to null, or
  • if unit is week and startOfWeek is missing or set to null.
• Uses the :wikipedia:`proleptic Gregorian calendar <Proleptic_Gregorian_calendar>` for dates preceding the year 1583.
• Accounts for Daylight Savings Time, but does not account for :wikipedia:`leap seconds <Leap_second>`.

binSize and unit Fields

Together, binSize and unit specify the time period used in the $dateTrunc calculation.

For example:

• If binSize is 1 and unit is hours, the time period is one hour. For the date 2021-03-20T11:30:05Z, $dateTrunc returns 2021-03-20T11:00:00Z.
• If binSize is 2 and unit is hours, the time period is two hours. For the date 2021-03-20T11:30:05Z, $dateTrunc returns 2021-03-20T10:00:00Z.

$dateTrunc:

• Divides the time for the $dateTrunc calculation into binSize time periods in the specified time unit.

  The time periods start at a reference date, which is determind by unit. If unit is:

  • A string other than week, $dateTrunc uses a reference date of 2000-01-01T00:00:00.00Z. For example, if binSize is 10 and unit is year, example time periods are:
    • 2000-01-01T00:00:00.00Z
    • 2010-01-01T00:00:00.00Z
    • 2020-01-01T00:00:00.00Z
  • Equal to week, $dateTrunc uses a reference date that is set to the earliest first day of the week that is greater than or equal to 2000-01-01. The first day is set using startOfWeek (the default is Sunday).

• Returns the lower boundary of the time period that the date is in. The boundary is returned as an ISODate. If the binSize field is 1, $dateTrunc sets the least significant parts (as determined by unit) of the returned ISODate to 0 and keeps the rest of the ISODate the same.

If unit is:

• year: $dateTrunc returns the ISODate for the start of January 1 for the year in date.

• quarter: $dateTrunc returns the ISODate for the start of the first day of the calendar quarter in date.

  The quarters are:

  • January to March
  • April to June
  • July to September
  • October to December

• month: $dateTrunc returns the ISODate for the start of the first day of the month in date.

• week: $dateTrunc returns the ISODate for the start of the startOfWeek day in date. The default for startOfWeek is Sunday.

• day: $dateTrunc returns the ISODate for the start of the day in date.

• hour: $dateTrunc returns the ISODate for the start of the hour in date.

• minute: $dateTrunc returns the ISODate for the start of the minute in date.

• second: $dateTrunc returns the the ISODate for start of the second in date.

unit and startOfWeek Fields

If unit is:

• A string other than week, startOfWeek is ignored.
• Equal to week and startOfWeek is:
  • Specified: $dateTrunc uses startOfWeek as the first day of the week for the calculation.
  • Omitted: $dateTrunc uses Sunday as the start of the week for the calculation.

Examples

Create a cakeSales collection that contains cake sales in the states of California (CA) and Washington (WA):

copy

db.cakeSales.insertMany( [
   { _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"),
     state: "CA", price: 13, quantity: 120 },
   { _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"),
     state: "WA", price: 14, quantity: 140 },
   { _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"),
     state: "CA", price: 12, quantity: 145 },
   { _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"),
     state: "WA", price: 13, quantity: 104 },
   { _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"),
     state: "CA", price: 41, quantity: 162 },
   { _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"),
     state: "WA", price: 43, quantity: 134 }
] )

The cakeSales collection is used in the following examples.

Truncate Order Dates in a $project Pipeline Stage

This example uses $dateTrunc in a $project stage to truncate the cake sales orderDate values to two weeks:

copy

db.cakeSales.aggregate( [
   {
      $project: {
         _id: 1,
         orderDate: 1,
         truncatedOrderDate: {
            $dateTrunc: {
               date: "$orderDate", unit: "week", binSize: 2,
               timezone: "America/Los_Angeles", startOfWeek: "Monday"
            }
         }
      }
   }
] )

In the example:

• $project includes the _id, orderDate, and truncatedOrderDate fields in the output.
• $dateTrunc truncates the orderDate field to a 2 binSize week unit time period in the America/Los_Angeles timezone with startOfWeek set to Monday.

In this example output, the truncated orderDate is shown in the truncatedOrderDate field:

[
   {
      _id: 0,
      orderDate: ISODate("2020-05-18T14:10:30.000Z"),
      truncatedOrderDate: ISODate("2020-05-11T07:00:00.000Z")
   },
   {
      _id: 1,
      orderDate: ISODate("2021-03-20T11:30:05.000Z"),
      truncatedOrderDate: ISODate("2021-03-15T07:00:00.000Z")
   },
   {
      _id: 2,
      orderDate: ISODate("2021-01-11T06:31:15.000Z"),
      truncatedOrderDate: ISODate("2021-01-04T08:00:00.000Z")
   },
   {
      _id: 3,
      orderDate: ISODate("2020-02-08T13:13:23.000Z"),
      truncatedOrderDate: ISODate("2020-02-03T08:00:00.000Z")
   },
   {
      _id: 4,
      orderDate: ISODate("2019-05-18T16:09:01.000Z"),
      truncatedOrderDate: ISODate("2019-05-13T07:00:00.000Z")
   },
   {
      _id: 5,
      orderDate: ISODate("2019-01-08T06:12:03.000Z"),
      truncatedOrderDate: ISODate("2019-01-07T08:00:00.000Z")
   }
]

Truncate Order Dates and Obtain Quantity Sum in a $group Pipeline Stage

This example uses $dateTrunc in a $group stage to truncate the cake sales orderDate values to six months and return the sum of the quantity values:

copy

db.cakeSales.aggregate( [
   {
      $group: {
         _id: {
            truncatedOrderDate: {
               $dateTrunc: {
                  date: "$orderDate", unit: "month", binSize: 6
               }
            }
         },
         sumQuantity: { $sum: "$quantity" }
      }
   }
] )

In the example:

• $group has the _id field set to the truncatedOrderDate field to group the cakeSales documents, and returns the sum of the quantity values for each group using $sum.
• $dateTrunc truncates the orderDate field to a 6 binSize month unit time period.

In this example output, the truncated orderDate is shown in the truncatedOrderDate field and the quantity sum is shown in the sumQuantity field:

[
   {
      _id: { truncatedOrderDate: ISODate("2020-01-01T00:00:00.000Z") },
      sumQuantity: 224
   },
   {
      _id: { truncatedOrderDate: ISODate("2021-01-01T00:00:00.000Z") },
      sumQuantity: 285
   },
   {
      _id: { truncatedOrderDate: ISODate("2019-01-01T00:00:00.000Z") },
      sumQuantity: 296
   }
]

←   $dateToString (aggregation) $dayOfMonth (aggregation)  →

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.