MongoDB: Using $dateDiff to find difference between dates (with examples)

Overview

In recent years, MongoDB has significantly enhanced its capabilities, allowing for more complex operations directly within the database. Amongst such improvements, the Aggregation Framework’s $dateDiff operator stands out. Introduced in MongoDB version 5.0, it provides developers with the ability to calculate the difference between two dates without the need to perform such operations in the application layer.

This tutorial will guide you through the process of using the $dateDiff operator in MongoDB, showcasing several examples that progressively increase in complexity. By the end, you’ll have a practical understanding of how to exploit this feature to its full potential.

Introduction to $dateDiff

The basic syntax of the $dateDiff operator is:

{
    $dateDiff: {
        startDate: ,
        endDate: ,
        unit: ,
        timezone: 
    }
}

Where startDate and endDate are the two dates you want to compare, unit represents the unit of time in which you seek the difference (e.g., year, month, day), and timezone (optional) specifies the timezone to use for the date difference calculation.

Basic Examples

Let’s start with a very simple use case. Imagine you have a collection of events, each with a startDate and endDate. You want to find out the number of days each event lasts.

db.events.aggregate([
    {
        $addFields: {
            duration: {
                $dateDiff: {
                    startDate: "$startDate",
                    endDate: "$endDate",
                    unit: "day"
                }
            }
        }
    }
]);

The above aggregation would add a new field duration to each record, representing the number of days between the startDate and endDate.

Handling Time Zones

Dealing with time zones can be tricky in date calculations. Thankfully, $dateDiff offers a straightforward solution. Here’s an example:

db.events.aggregate([
    {
        $addFields: {
            duration: {
                $dateDiff: {
                    startDate: "$startDate",
                    endDate: "$endDate",
                    unit: "hour",
                    timezone: "America/Los_Angeles"
                }
            }
        }
    }
]);

This time we’re calculating the duration in hours, taking into account the Pacific Daylight Time (PDT) timezone.

Conditional Differences

Moving on to a more advanced example, suppose we only want to calculate the difference for events that ended after a specific date:

db.events.aggregate([
    {
        $match: {
            endDate: { $gt: ISODate("2021-01-01T00:00:00Z")}
        }
    },
    {
        $addFields: {
            duration: {
                $dateDiff: {
                    startDate: "$startDate",
                    endDate: "$endDate",
                    unit: "week"
                }
            }
        }
    }
]);

In this pipeline, we introduced a $match stage to filter events, followed by the $addFields stage to calculate the week duration.

Complex Date Manipulations

Let’s leverage $dateDiff in a scenario where we need to work with more complex date manipulations. For instance, we might want to normalize the endDate to the end of the respective month before calculating the duration:

db.events.aggregate([
    {
        $addFields: {
            normalizedEndDate: {
                $dateFromParts: {
                    year: { $year: "$endDate" },
                    month: { $month: "$endDate" },
                    day: 1
                }
            }
        }
    },
    {
        $addFields: {
            duration: {
                $dateDiff: {
                    startDate: "$startDate",
                    endDate: "$normalizedEndDate",
                    unit: "month",
                    timezone: "UTC"
                }
            }
        }
    }
]);

In this pipeline, we’re constructing a new date at the end of the month the event ended, then calculating the duration in months from the start date.

Combining with Other Operators

Combining $dateDiff with other operators can yield even more powerful aggregations. For instance, you can also determine the average duration of months between all events’ dates.

db.events.aggregate([
    {
        $group: {
            _id: null,
            averageDuration: {
                $avg: {
                    $dateDiff: {
                        startDate: "$startDate",
                        endDate: "$endDate",
                        unit: "month"
                    }
                }
            }
        }
    }
]);

This aggregation calculates the average month duration of all the events in the collection, giving you insights at a glance.

Conclusion

To finalize, the $dateDiff operator is a powerful tool in MongoDB’s arsenal that permits you to perform intricate date computations efficiently. The practical examples provided should help jump-start your endeavors into more dynamic and complex querying and serve as a foundation for further exploration of MongoDB’s aggregation framework.