$dateSubtract

The $dateSubtract operator subtracts a specified time unit from a date. It's useful for calculating past dates or intervals in aggregation pipelines.

Syntax

{
  $dateSubtract: {
    startDate: <dateExpression>,
    unit: "<unit>",
    amount: <number>,
    timezone: "<timezone>" // optional
  }
}

Parameters

Parameter Description
startDate The date expression to subtract from.
unit The time unit to subtract (for example, "day", "hour").
amount The amount of the time unit to subtract.
timezone (Optional) Timezone for date calculation.

Examples

Let's understand the usage with sample json from stores dataset.

{
  "_id": "e6410bb3-843d-4fa6-8c70-7472925f6d0a",
  "name": "Relecloud | Toy Collection - North Jaylan",
  "location": {
    "lat": 2.0797,
    "lon": -94.4134
  },
  "staff": {
    "employeeCount": {
      "fullTime": 7,
      "partTime": 4
    }
  },
  "sales": {
    "salesByCategory": [
      {
        "categoryName": "Educational Toys",
        "totalSales": 3299
      }
    ],
    "revenue": 3299
  },
  "promotionEvents": [
    {
      "eventName": "Massive Markdown Mania",
      "promotionalDates": {
        "startDate": {
          "Year": 2024,
          "Month": 9,
          "Day": 21
        },
        "endDate": {
          "Year": 2024,
          "Month": 9,
          "Day": 29
        }
      },
      "discounts": [
        {
          "categoryName": "Remote Control Toys",
          "discountPercentage": 6
        },
        {
          "categoryName": "Building Sets",
          "discountPercentage": 21
        }
      ]
    }
  ],
  "company": "Relecloud",
  "city": "North Jaylan",
  "lastUpdated": {
    "$timestamp": {
      "t": 1733313006,
      "i": 1
    }
  },
  "storeOpeningDate": "2024-09-05T11:50:06.549Z"
}

Example 1: Subtract seven days

The example calculates the date one week before the lastUpdated field. The query uses $dateSubtract to calculate the date exactly seven days before the storeOpeningDate timestamp.

db.stores.aggregate([
  {
    $match: { _id: "e6410bb3-843d-4fa6-8c70-7472925f6d0a" }
  },
  {
    $project: {
      _id: 0,
      dateOneWeekAgo: {
        $dateSubtract: {
          startDate: "$storeOpeningDate",
          unit: "day",
          amount: 7
        }
      }
    }
  }
])

The query returns the date that is exactly seven days before the storeOpeningDate timestamp.

{
  "dateOneWeekAgo": "2024-08-29T11:50:06.549Z"
}

Example 2: Subtract two hours with timezone

This example subtracts two hours, considering the China/Shang_hai timezone. // ERROR ON TIMEZONE

db.stores.aggregate([
  {
    $project: {
      _id: 0,
      dateTwoHoursAgo: {
        $dateSubtract: {
          startDate: "$metadata.lastUpdated",
          unit: "hour",
          amount: 2,
          timezone: "China/Shang_hai"
        }
      }
    }
  }
])

Related content