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

# $dateSubtract (expression operator)

## Definition

**New in version 5.0**

Decrements a [`Date()`](https://www.mongodb.com/docs/manual/reference/mongodb-extended-json.md#mongodb-bsontype-Date) object by a specified number of time units.

The [`$dateSubtract`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datesubtract.md#mongodb-expression-exp.-dateSubtract) expression has the following syntax:

```javascript
{
   $dateSubtract: {
      startDate: <Expression>,
      unit: <Expression>,
      amount: <Expression>,
      timezone: <tzExpression>
   }
}
```

Returns a `Date()`. The `startDate` can be any expression that resolves to type Date, Timestamp or ObjectId. No matter which data type is used as input, the value returned will be a `Date()` object.

| Field | Required/Optional | Description |
| --- | --- | --- |
| `startDate` | Required | The beginning date, in UTC, for the subtraction operation. The `startDate` 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` | Required | The `unit` used to measure the `amount` of time subtracted from the `startDate`. The `unit` is an [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) that resolves to one of the following strings: `year`; `quarter`; `week`; `month`; `day`; `hour`; `minute`; `second`; `millisecond` IMPORTANT: Starting in MongoDB 8.3, if `unit` is not `"millisecond"` and the input date is before `ISODate("1970-01-01T00:00:00Z")`, the result is one second greater than in previous versions of MongoDB. |
| `amount` | Required | The number of `units` subtracted from the `startDate`. The `amount` is an [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) that resolves to an integer or long. The `amount` can also resolve to an integral decimal and or a double if that value can be converted to a long without loss of precision. |
| `timezone` | Optional | The timezone to carry out the operation. `<tzExpression>` must be a valid [expression](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) that resolves to a string formatted as either an [Olson Timezone Identifier](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) or a [UTC Offset](https://en.wikipedia.org/wiki/List_of_UTC_time_offsets): Olson Timezone Identifier: for example, `"America/New_York"`, `"Europe/London"`, `"GMT"`; UTC Offset: for example, `"+04:45"`, `"-0530"`, `"+03"` If you omit `timezone`, the result is displayed in `UTC`. |

For more information on expressions and types see [Expressions](https://www.mongodb.com/docs/manual/reference/mql/expressions.md#std-label-aggregation-expressions) and [MongoDB BSON Types.](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-bson-types)

## Behavior

### Time Measurement

MongoDB follows prevaling database usage and works with time in UTC. The `dateSubtract` expression always takes a `startDate` in UTC and returns a result in UTC. If the `timezone` is specified, the calculation will be done using the specified `timezone`. The timezone is especially important when a calculation involves Daylight Savings Time (DST).

If the `unit` is a `month`, or larger the operation adjusts to account for the last day of the month. Subtracting one `month` on the last day of March, for example, demonstrates the "last-day-of-the-month" adjustment.

```javascript
{
   $dateSubtract:
      {
         startDate: ISODate("2021-03-31T12:10:05Z"),
         unit: "month",
         amount: 1
      }
}
```

Notice that the date returned, `ISODate("2021-02-28T12:10:05Z")`, is the 28th and not the 31st since February has fewer days than March.

### Time Zone

When using an Olson Timezone Identifier in the `<timezone>` field, MongoDB applies the DST (Daylight Savings Time) offset if applicable for the specified timezone.

For example, consider a `sales` collection with the following document:

```javascript&#xA;
 db.sales.insertOne(
  {
   "_id" : 1,
   "item" : "abc",
   "price" : 10,
   "quantity" : 2,
   "date" : ISODate("2014-01-01T08:15:39.736Z")
  }
)
```

The following aggregation illustrates how MongoDB handles the DST offset for the Olson Timezone Identifier. The example uses the [`$hour`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/hour.md#mongodb-expression-exp.-hour) and [`$minute`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/minute.md#mongodb-expression-exp.-minute) operators to return the corresponding portions of the `date` field:

```javascript
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:

```javascript
{
   "_id": 1,
   "nycHour" : 5,
   "nycMinute" : 24,
   "gmtHour" : 10,
   "gmtMinute" : 24,
   "nycOlsonHour" : 6,
   "nycOlsonMinute" : 24
}
```

## Examples

### Subtract A Fixed Amount

Consider a collection of system connection times like these:

```javascript
db.connectionTime.insertMany(
  [
     {
        custId: 457,
        login: ISODate("2020-12-25T19:04:00"),
        logout: ISODate("2020-12-28T09:04:00")
     },
     {
        custId: 457,
        login: ISODate("2021-01-27T05:12:00"),
        logout: ISODate("2021-01-28T13:05:00")
     },
     {
        custId: 458,
        login: ISODate("2021-01-22T06:27:00"),
        logout: ISODate("2021-01-31T11:00:00")
     },
     {
        custId: 459,
        login: ISODate("2021-02-14T20:14:00"),
        logout: ISODate("2021-02-17T16:05:00")
     },
     {
        custId: 460,
        login: ISODate("2021-02-26T02:44:00"),
        logout: ISODate("2021-02-18T14:13:00")
        }
  ]
)
```

Due to a service issue you need to subtract 3 hours from each of the January 2021 logout times. You can use `$dateSubtract` in an aggregation pipeline to decrement the `logoutTime`.

```javascript
db.connectionTime.aggregate(
   [
      {
         $match:
            {
               $expr:
                  {
                     $eq:
                        [
                            { $year: "$logout" },
                              2021
                        ]
                  },
               $expr:
                  {
                     $eq:
                        [
                            { $month: "$logout" },
                              1
                        ]
                   }
             }
       },
       {
          $project:
             {
                logoutTime:
                   {
                      $dateSubtract:
                         {
                            startDate: "$logout",
                            unit: "hour",
                            amount: 3
                         }
                   }
              }
        },
        {
           $merge: "connectionTime"
        }
   ]
)
```

Two similar comparisons are made in the [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) stage. First the [`$year`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/year.md#mongodb-expression-exp.-year) and [`$month`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/month.md#mongodb-expression-exp.-month) operators extract the year and month, respectively, from the `logoutTime` Date object. Then the month and year are checked to see if they match the selection targets. Since "January" is encoded as "1", [`$expr`](https://www.mongodb.com/docs/manual/reference/operator/query/expr.md#mongodb-query-op.-expr) is true when the year and month are equal ([`$eq`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/eq.md#mongodb-expression-exp.-eq)) to "2021" and "1".

The [`$project`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/project.md#mongodb-pipeline-pipe.-project) stage uses `$dateSubtract` to subtract 3 hours from the `logoutTime` of each selected dcoument.

Finaly, the [`$merge`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#mongodb-pipeline-pipe.-merge) stage updates the collection, writing the new `logoutTime` for the modified documents.

**Note:**

Unlike [`$out`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#mongodb-pipeline-pipe.-out), the `$merge` stage only updates the matched documents and preserves the rest of the collection. For more details see: [$out compared with $merge.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#std-label-out-merge-comparison)

The resulting documents look like this:

```javascript
{
 "_id" : ObjectId("603dd94b044b995ad331c0b5"),
 "custId" : 457,
 "login" : ISODate("2020-12-25T19:04:00Z"),
 "logout" : ISODate("2020-12-28T09:04:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b6"),
 "custId" : 457,
 "login" : ISODate("2021-01-27T05:12:00Z"),
 "logout" : ISODate("2021-01-28T13:05:00Z"),
 "logoutTime" : ISODate("2021-01-28T10:05:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b7"),
 "custId" : 458,
 "login" : ISODate("2021-01-22T06:27:00Z"),
 "logout" : ISODate("2021-01-31T11:00:00Z"),
 "logoutTime" : ISODate("2021-01-31T08:00:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b8"),
 "custId" : 459,
 "login" : ISODate("2021-02-14T20:14:00Z"),
 "logout" : ISODate("2021-02-17T16:05:00Z")
}
{
 "_id" : ObjectId("603dd94b044b995ad331c0b9"),
 "custId" : 460,
 "login" : ISODate("2021-02-26T02:44:00Z"),
 "logout" : ISODate("2021-02-18T14:13:00Z")
}
```

### Filter by Relative Dates

You want to send a survey to clients who have used your service in the past week. The `$dateSubtract` expression can create a range filter relative to the time the query is executed.

```javascript
db.connectionTime.aggregate(
   [
      {
         $match:
            {
               $expr:
                  {
                     $gt:
                        [
                           "$logoutTime",
                            {
                               $dateSubtract:
                                  {
                                     startDate: "$$NOW",
                                     unit: "week",
                                     amount: 1
                                  }
                            }
                        ]
                  }
             }
      },
      {
         $project:
            {
               _id: 0,
               custId: 1,
               loggedOut:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d",
                           date: "$logoutTime"
                        }
                  }
            }
      }
   ]
)
```

The built in aggregation variable [`$$NOW`](https://www.mongodb.com/docs/manual/reference/aggregation-variables.md#mongodb-variable-variable.NOW) returns the current datetime in [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) format. The [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) stage uses the value in [`$$NOW`](https://www.mongodb.com/docs/manual/reference/aggregation-variables.md#mongodb-variable-variable.NOW) to get today's date. Then the comparison expression ([`$expr`](https://www.mongodb.com/docs/manual/reference/operator/query/expr.md#mongodb-query-op.-expr)) filters the collection using greater than ([`$gt`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/gt.md#mongodb-expression-exp.-gt)) and `$dateSubtract` to match documents that have a `logoutTime` in the past week.

The [`$project`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/project.md#mongodb-pipeline-pipe.-project) stage uses the [`$dateToString`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetostring.md#mongodb-expression-exp.-dateToString) expression to convert the dates to a more readable format. Without the conversion, MongoDB returns the date in [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) format and assumes a UTC timezone.

The output shows two customers have logged out in the last week.

```javascript
{ "custId" : 459, "loggedOut" : "2021-02-17" }
{ "custId" : 460, "loggedOut" : "2021-02-18" }
```

### Adjust for Daylight Savings Time

All dates are stored internally in UTC time. When a `timezone` is specified, `$dateSubtract` uses local time to carry out the calculations. The results are displayed in UTC.

You have customers in several timezones and you want to see what effect daylight savings time might have on your billing periods if you bill by `day` or by `hour`.

Create this collection of connection times:

```javascript
db.billing.insertMany(
   [
      {
         location: "America/New_York",
         login: ISODate("2021-03-14T10:00:00-0500"),
         logout: ISODate("2021-03-14T18:00:00-0500")
      },
      {
         location: "America/Mexico_City",
         login: ISODate("2021-03-14T10:00:00-00:00"),
         logout: ISODate("2021-03-15T08:00:00-0500")
      }
   ]
)
```

First subtract 1 day, then subtract 24 hours from the `login` dates in each document.

```javascript
db.billing.aggregate(
   [
      {
         $project:
            {
               _id: 0,
               location: 1,
               start:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date: "$login"
                        }
                  },
               days:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                    {
                                       startDate: "$login",
                                       unit: "day",
                                       amount: 1,
                                       timezone: "$location"
                                    }
                              }
                        }
                  },
               hours:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                 {
                                    startDate: "$login",
                                    unit: "hour",
                                    amount: 24,
                                    timezone: "$location"
                                 }
                              }
                        }
                  },
               startTZInfo:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date: "$login",
                           timezone: "$location"
                        }
                  },
               daysTZInfo:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                    {
                                       startDate: "$login",
                                       unit: "day",
                                       amount: 1,
                                       timezone: "$location"
                                    }
                              },
                           timezone: "$location"
                        }
                  },
               hoursTZInfo:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateSubtract:
                                    {
                                       startDate: "$login",
                                       unit: "hour",
                                       amount: 24,
                                       timezone: "$location"
                                    }
                              },
                           timezone: "$location"
                        }
                  },
            }
      }
   ]
).pretty()
```

The [`$dateToString`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datetostring.md#mongodb-expression-exp.-dateToString) expression reformats the output for readability. Results are summarized here:

| Field | New York | Mexico City |
| --- | --- | --- |
| Start | 2021-03-14 15:00 | 2021-03-14 15:00 |
| Start, TZ Info | 2021-03-14 11:00 | 2021-03-14 04:00 |
| 1 Day | 2021-03-13 16:00 | 2021-03-13 15:00 |
| 1 Day, TZInfo | 2021-03-13 11:00 | 2021-03-13 09:00 |
| 24 Hours | 2021-03-13 15:00 | 2021-03-13 15:00 |
| 24 Hours, TZInfo | 2021-03-13 10:00 | 2021-03-13 09:00 |

The chart highlights several points:

- Unformatted dates are returned in UTC. The `$login` for New York is UTC -5, however the `start`, `days`, and `hours` rows display the time in UTC.

- March 14th is the start of DST in New York, but not in Mexico. The calculated time is adjusted when a location switches to DST and crosses from one `day` to the next.

- DST modifies the length of the `day`, not the `hour`. There is no DST change for `hours`. There is an only an adjustment for DST when the measurement `unit` is `day` or larger and the computation crosses a clock change in the specified `timezone`.

**See also:**

[`$dateAdd`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/dateadd.md#mongodb-expression-exp.-dateAdd), [`$dateDiff`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/datediff.md#mongodb-expression-exp.-dateDiff)