MongoDB

MongoDB $DateAdd and $DateSubtract Operators

MongoDB provides various aggregation pipeline operators for different purposes. Among all of the operators, we will discuss the $dateAdd and the $dateSubtract operators of MongoDB, respectively. The $dateAdd operator increases the date object by a predetermined amount of time units. While the $dateSubtract operator is used to decrease the date objects by the provided amount of time units. The returned result of the $dateAdd and $dateSubtract operators is always a Date object, regardless of the input data type.

How to Use the MongoDB $DateAdd and $DateSubtract Operators

Here, we use the $dateAdd and $dateSubtract operators in MongoDB which return the expected dates according to the given operator. Consider the “OnlineDelivery” collection where we insert a few documents with the insertMany() method. These documents are used over the $dateAdd and $dateSubtract operators to demonstrate their performance in MongoDB.

db.onlineDelivery.insertMany(
  [
     { Id: 05,
    OrderDate: ISODate("2021-12-30"),
    PaymentDate: ISODate("2022-02-19T15:20:00"),
     Location: "America/New_York"   },
     { Id: 09,
      OrderDate: ISODate("2022-01-18"),
     PaymentDate: ISODate("2023-01-04T16:10:00"),
    Location: "America/New_York"},
     { Id: 45,
   OrderDate: ISODate("2020-03-24"),
  PaymentDate:ISODate("2021-05-31T21:00:00"),
   Location: "America/New_York"},
{ Id: 55,
 OrderDate: ISODate("2023-09-20"),
 PaymentDate:ISODate("2023-12-31T21:00:00"),
 Location: "America/New_York"}
  ]
)

After the insertion of the previous documents within the “OnlineDelivery” collection, the output is represented like in the following which acknowledges that the documents are added:

{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("63c298e9ad100b03ead18bee"),
    '1': ObjectId("63c298e9ad100b03ead18bef"),
    '2': ObjectId("63c298e9ad100b03ead18bf0"),
   '3': ObjectId("63c29cefad100b03ead18bf4")
  }
}

Example 1: Using the $DateAdd Operator in MongoDB

The $dateAdd operator is used to configure the future date of the specified original date. Here, the $dateAdd operator is employed in the $project operator field, “DeliveryDate”. We want the expected delivery date of the order date. So, within the $dateAdd operator, we set the required “startDate” parameter with the “OrderDate” field. The “startDate” parameter should be the beginning date in UTC format. After that, we have a “unit” parameter which is specified with the “day” value. The “unit” parameter indicates the time increment which is appended to the “startDate”. Then, we provide the “amount” parameter with the number “4” which adds four days to the “startDate”.

db.onlineDelivery.aggregate(
   [
      {
         $project:
            {
               DeliveryDate:
                  {
                     $dateAdd:
                        {
                           startDate: "$OrderDate",
                           unit: "day",
                           amount: 4
                        }
                  }
            }
       }
])

The OrderDates is incremented by a “4” unit which is displayed in the “DeliveryDate” field. These dates are the expected times to deliver the order.

[
  {
    _id: ObjectId("63c298e9ad100b03ead18bee"),
    DeliveryDate: ISODate("2022-01-03T00:00:00.000Z")
  },
  {
    _id: ObjectId("63c298e9ad100b03ead18bef"),
    DeliveryDate: ISODate("2022-01-22T00:00:00.000Z")
  },
  {
    _id: ObjectId("63c298e9ad100b03ead18bf0"),
    DeliveryDate: ISODate("2020-03-28T00:00:00.000Z")
  },
 {
    _id: ObjectId("63c29cefad100b03ead18bf4"),
    DeliveryDate: ISODate("2023-09-24T00:00:00.000Z")
  }
]

Example 2: Using the $DateAdd Operator in MongoDB to Filter a Range of Date

We use the $dateAdd in a $match operator to build a filter that matches the documents within a range of dates which is determined by a startDate and a time frame which is specified by the $dateAdd operator. Consider the script of MongoDB. We have a $match stage inside the aggregate method. The $match stage is defined with the $exp operator where the conditional expression of $gt is specified. The $gt operator matches the document which satisfies the given condition. We set the $PaymentDate field in the $gt operator which is compared with the date that is obtained from the $dateAdd operator.

We set the “startDate” with the “OrderDate” parameters to the $dateAdd operator. The “unit” is given as “month” and the “amount” to be added in the dates is given as “13”. Here, the documents whose delivery dates are greater than “13” months from the “OrderDate” are passed to the $project operator. Then, the $project operator is deployed with the “Order” field that has the expression of the $dateToString operator to transform the dates into a better readable format.

db.onlineDelivery.aggregate(
    [
       {
          $match:
             {
                $expr:
                   {
                      $gt:
                         [ "$PaymentDate",
                           {
                              $dateAdd:
                                 {
                                    startDate: "$OrderDate",
                                    unit: "month",
                                    amount: 13
                                 }
                            }
                         ]
                   }
             }
        },
 {
           $project:
              {
                 _id: 0,
                OrderDate: 1,
                 Order:
                    {
                        $dateToString:
                           {
                              format: "%Y-%m-%d",
                              date: "$OrderDate"
                           }
                    }
 }
 }
 ])

Upon executing the previous query, we have two records from the $dateAdd operator that satisfies the condition.

[
  {
    OrderDate: ISODate("2020-03-24T00:00:00.000Z"),
    Order: '2020-03-24'
  },
  {
    OrderDate: ISODate("2020-03-24T00:00:00.000Z"),
    Order: '2020-03-24'
  }
]

Example 3: Using the $DateAdd Operator in MongoDB by an Hour

The $dateAdd operator utilizes the time to perform the computations when a timezone is defined. Here, we use the $dateAdd operator to increment the date by an hour. For this, we have to modify the “unit” parameter of the $dateAdd operator. We have given a new “hours” field in the $project operator. The “hours” field is further defined by the “$dateToString” operator for the conversion of the date in string format which is returned by the $dateAdd operator. The $dateAdd operator is specified with the “startDate” where the $OrderDate field is provided. Then, we use the “unit” parameter to set the “hour” there. After that, we set the “amount” parameter to “24” and set the “timezone” with the “$Location” field when the $dateAdd operator increments the date by an hour.

db.onlineDelivery.aggregate(
   [
      {
         $project:
            {
               hours:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d %H:%M",
                           date:
                              {
                                 $dateAdd:
                                 {
                                    startDate: "$OrderDate",
                                    unit: "hour",
                                    amount: 24,
                                    timezone: "$Location"
                                 }
                              }
                        }
                  }
               
            }
      }
   ]
).pretty()

We retrieved the same dates as the original dates because the $dateAdd operator added the dates by hours in the “24” amount number.

  {
    _id: ObjectId("63c298e9ad100b03ead18bee"),
    hours: '2021-12-31 00:00'
  },
  {
    _id: ObjectId("63c298e9ad100b03ead18bef"),
    hours: '2022-01-19 00:00'
  },
  {
    _id: ObjectId("63c298e9ad100b03ead18bf0"),
    hours: '2020-03-25 00:00'
  }
]

Example 4: Using the $DateSubtract Operator in MongoDB to Decrement a Date

In the prior instances of MongoDB, we used the $dateAdd operator to get the expected date in the future. Now, we use the $dateSubtract operator of MongoDB which gets the past expected date from the current given date. The $dateSubtract operator inputs all the parameters the same as the parameters in the $dateAdd operator. Note that we use the $dateSubtract within the “expectedOrderDate” field of the $project operator. There, the $dateSubtract operator inputs the “startDate” parameter with the “$payementDate” field for the subtraction of the date to get the expected date of the order.

Then, we set the “unit” argument with the “month” to represent the time that is taken away from the startDate. Next is the “amount” parameter which is defined with the number “2” to decrease the date with this value.

db.onlineDelivery.aggregate(
   [
      {
         $project:
            {
               expectedOrderDate:
  {
   $dateSubtract:
      {
         startDate: "$PaymentDate",
         unit: "month",
         amount: 2
      }
}
                 
            }
       }
])

The expected dates are returned by the $dateSubtract operator which are the dates two months before the actual dates.

[
  {
    _id: ObjectId("63c298e9ad100b03ead18bee"),
    expectedOrderDate: ISODate("2021-12-19T15:20:00.000Z")
  },
  {
    _id: ObjectId("63c298e9ad100b03ead18bef"),
    expectedOrderDate: ISODate("2022-11-04T16:10:00.000Z")
  },
  {
    _id: ObjectId("63c298e9ad100b03ead18bf0"),
    expectedOrderDate: ISODate("2021-03-31T21:00:00.000Z")
  }
]

Example 5: Using the $DateSubtract Operator in MongoDB to Sort Based on Relative Dates

Additionally, we can establish a range filter depending on the execution time of the query using the $dateSubtract expression. We employ the $match stage in the aggregate method to match the relative dates. The $match stage has the expression which is set with the $expr operator. The $expr operator uses the $gt and $dateSubtract operators to limit the matched documents with a PaymentDate inside the last week. The $dateSubtract operator gives the current ISO format date as the $$NOW variable is specified to the startDate operator. Then, the decrement date that is obtained from the $dateSubtract operator is transformed into the string format by the “$dateToString” which is employed inside the $project operator.

db.onlineDelivery.aggregate(
   [
      {
         $match:
            {
               $expr:
                  {
                     $gt:
                        [
                           "$PaymentDate",
                            {
                               $dateSubtract:
                                  {
                                     startDate: "$$NOW",
                                     unit: "week",
                                     amount: 1
                                  }
                            }
                        ]
                  }
             }
      },
      {
         $project:
            {
               _id: 0,
               Id: 1,
               Payment:
                  {
                     $dateToString:
                        {
                           format: "%Y-%m-%d",
                           date: "$PaymentDate"
                        }
                  }
            }
      }
   ]
)

We have only one record which is retrieved by the $dateSubtract operator.

[ { Id: 55, Payment: '2023-12-31' } ]

Conclusion

The MongoDB $dateAdd and $dateSubtract operators are demonstrated in this guide. We used these operators with examples where the parameter that is associated with each operator is explored. First, we have an example script of the $dateAdd operator to add the dates with a given amount of a number with the timezone. The $dateAdd operator is further deployed to get the future date within a range and obtain the expected date by specifying the hour as a unit. After that, the $dateSubtract operator is explored with the example to get the past dates.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.