> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

# $dateTrunc (expression operator)

## Definition

**New in version 5.0**

Truncates a date.

[`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) syntax:

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

| Field | Required / Optional | Description |
| --- | --- | --- |
| [date](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date) | Required | The date to truncate, specified in UTC. The [date](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date) can be any [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) that resolves to a [Date](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-document-bson-type-date), a [Timestamp](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-document-bson-type-timestamp), or an [ObjectID.](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-document-bson-type-object-id) |
| [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) | Required | The unit of time, specified as an [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) that must resolve to one of these strings: `year`; `quarter`; `week`; `month`; `day`; `hour`; `minute`; `second` Together, [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) and [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) specify the time period used in the [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) calculation. |
| [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) | Optional | The numeric time value, specified as an [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) that must resolve to a positive non-zero number. Defaults to 1. Together, [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) and [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) specify the time period used in the [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) calculation. |
| [timezone](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-timezone) | Optional | The timezone for the [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) calculation, specified as an [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) that must resolve to a string that contains one of these values: [Olson Timezone Identifier](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones), for example: `America/New_York`, `Europe/London`, `GMT`; [UTC Offset](https://en.wikipedia.org/wiki/List_of_UTC_time_offsets), for example: `+/-[hh]:[mm]` (`+04:45`), `+/-[hh][mm]` (`-0530`), `+/-[hh]` (`+03`) If no [timezone](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-timezone) is provided, the [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) calculation is performed in UTC. |
| [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) | Optional | The start of the week. Used when [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) is `week`. Defaults to `Sunday`. [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) is an [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) 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](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions)

- [MongoDB BSON Types](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-bson-types)

## Behavior

[`$dateTrunc`:](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc)

- Returns `null` if:

  - any of the input fields except [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) is missing or set to `null`, or

  - if [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) is `week` and [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) is missing or set to `null`.

- Uses the [proleptic Gregorian calendar](https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar) for dates preceding the year 1583.

- Accounts for Daylight Savings Time, but does not account for [leap seconds.](https://en.wikipedia.org/wiki/Leap_second)

### `binSize` and `unit` Fields

Together, [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) and [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) specify the time period used in the [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) calculation.

For example:

- If [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) is `1` and [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) is `hour`, the time period is one hour. For the [date](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date) `2021-03-20T11:30:05Z`, [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns `2021-03-20T11:00:00Z`.

- If [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) is `2` and [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) is `hour`, the time period is two hours. For the [date](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date) `2021-03-20T11:30:05Z`, [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns `2021-03-20T10:00:00Z`.

[`$dateTrunc`:](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc)

- Divides the time for the [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) calculation into [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) time periods in the specified time [unit.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit)

  The time periods start at a reference date, which is determined by [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit). If [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) is:

  - A string other than `week`, [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) uses a reference date of `2000-01-01T00:00:00.00Z`. For example, if [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) is `10` and [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-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](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) (the default is Sunday).

- Returns the lower boundary of the time period that the [date](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date) is in. The boundary is returned as an [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate). If the [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) field is `1`, [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) sets the least significant parts (as determined by [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit)) of the returned [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) to `0` and keeps the rest of the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) the same.

If [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) is:

- `year`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for the start of January 1 for the year in [date.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date)

- `quarter`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for the start of the first day of the calendar quarter in [date.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date)

  The quarters are:

  - January to March

  - April to June

  - July to September

  - October to December

- `month`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for the start of the first day of the month in [date.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date)

- `week`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for the start of the [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) day in [date](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date). The default for [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) is Sunday.

- `day`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for the start of the day in [date.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date)

- `hour`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for the start of the hour in [date.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date)

- `minute`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for the start of the minute in [date.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date)

- `second`: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) returns the [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) for start of the second in [date.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-date)

### `unit` and `startOfWeek` Fields

If [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) is:

- A string other than `week`, [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) is ignored.

- Equal to `week` and [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) is:

  - Specified: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) uses [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) as the first day of the week for the calculation.

  - Omitted: [`$dateTrunc`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-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`):

```javascript
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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) in a [`$project`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/project.md#mongodb-pipeline-pipe.-project) stage to truncate the cake sales `orderDate` values to two weeks:

```javascript
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](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) `week` [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-unit) time period in the `America/Los_Angeles` [timezone](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-timezone) with [startOfWeek](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-startOfWeek) set to `Monday`.

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

```javascript
[
   {
      _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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#mongodb-expression-exp.-dateTrunc) in a [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage to truncate the cake sales `orderDate` values to six months and return the sum of the `quantity` values:

```javascript
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`.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sum.md#mongodb-group-grp.-sum)

- `$dateTrunc` truncates the `orderDate` field to a `6` [binSize](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-binSize) `month` [unit](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetrunc.md#std-label-dateTrunc-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:

```javascript
[
   {
      _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
   }
]
```