Working with $dateAdd and $dateSubtract in MongoDB (with examples)

Introduction

In the evolving landscape of database technologies, MongoDB consistently introduces enhanced features to deal with various data types easily, one of which is the date type. Dealing with dates in databases is often vital since many business operations rely on date and time calculations like session expiry, subscription renewals, or report generation times. MongoDB provides powerful aggregation operators to manipulate dates: $dateAdd and $dateSubtract.

Basic Date Arithmetic in MongoDB

The basic function of these two operators is to add or subtract a specified amount of time to a date. Let’s start with a simple example where we add 5 days to the current date.

db.events.aggregate([ 
  { 
    $project: { 
      newDate: { 
        $dateAdd: { 
          startDate: new Date(), 
          unit: "day", 
          amount: 5 
        } 
      } 
    } 
  } 
]);

You should see the updated date 5 days in the future in the newDate field.

Subtracting Time from Dates

Now, let’s look at subtracting time from a given date. The following example subtracts two weeks from a date stored in the eventDate field.

db.events.aggregate([
  {
    $project: {
      previousDate: {
        $dateSubtract: {
          startDate: '$eventDate',
          unit: 'week',
          amount: 2
        }
      }
    }
  }
]);

The output should show the previousDate that is exactly two weeks before the eventDate.

Working with Different Time Units

MongoDB allows the use of different units for date arithmetic including year, quarter, month, week, day, hour, minute, second, and even millisecond. For instance, to add 30 minutes to the current time:

db.events.aggregate([ 
  { 
    $project: { 
      newDate: { 
        $dateAdd: { 
          startDate: new Date(), 
          unit: "minute", 
          amount: 30 
        } 
      } 
    } 
  } 
]);

This modifies the current time by adding 30 minutes. Similar arithmetic can be applied using any of the supported time units.

Advanced Date Manipulation

For more advanced date manipulations, these expressions can be nested, and multiple units of time can be combined to achieve the desired result. As a comprehensive example, imagine adding 2 years, 3 months, and 5 days to a particular date from a field called startDate.

db.events.aggregate([
  {
    $project: {
      adjustedDate: {
        $dateAdd: {
          startDate: {
            $dateAdd: {
              startDate: {
                $dateAdd: {
                  startDate: '$startDate',
                  unit: 'year',
                  amount: 2
                }
              },
              unit: 'month',
              amount: 3
            }
          },
          unit: 'day',
          amount: 5
        }
      }
    }
  }
]);

Another more practical usage might involve calculating deadlines for tasks by excluding non-working days like weekends or public holidays.

Handling Time Zones

When dealing with international databases, time zone handling becomes critical. Both $dateAdd and $dateSubtract can take an optional timezone parameter. Here’s an example:

db.events.aggregate([
  {
    $project: {
      newDateInTimezone: {
        $dateAdd: {
          startDate: new Date(),
          unit: "hour", 
          amount: 5, 
          timezone: "-04:00" 
        }
      }
    }
  }
]);

This code adds 5 hours to the current time, adjusted to the timezone offset of UTC-04:00.

Error Handling

Be aware that if your startDate is null or if any input is invalid, the operators will return null. Therefore, it is wise to include data validation or handle possible null values accordingly in your queries.

Conclusion

With the $dateAdd and $dateSubtract operators, time-sensitive data operations become more manageable and agile. By mastering these operators, along with MongoDB’s other aggregation capabilities, developers can effortlessly implement complex date and time-based logic within their applications.