How to truncate a date in MongoDB (reduce precision)

Introduction

Dealing with dates in databases can sometimes be as challenging as it is essential. In MongoDB, managing date precision is a common necessity that might arise due to various use cases like reporting, data aggregation, or simply cleaning up the data. This tutorial aims to guide you through different methodologies of truncating a date in MongoDB, effectively reducing its precision.

Understanding Date Fields in MongoDB

Before diving into the truncation processes, it is important to understand how MongoDB handles dates. MongoDB stores dates in the BSON format, which provides several date and time related operations. These date objects represent a specific instant in time, expressed as a timestamp and time zone.

Utilizing the $dateTrunc Operator

In MongoDB version 5.0 and later, the $dateTrunc aggregation pipeline operator is introduced. This operator truncates a date to a specified unit of time. The syntax for the $dateTrunc operator looks like this:

{
    $dateTrunc: {
        date: <dateExpression>,
        unit: <truncationUnit>,
        ...additionalOptions
    }
}

The available truncationUnit values include ‘year’, ‘quarter’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, ‘millisecond’. Additional options can be timezone and start of week.

Basic Date Truncation to Start of Day

db.collection.aggregate([
    {
        $project: {
            date: 1,
            truncatedDate: {
                $dateTrunc: {
                    date: "$dateField",
                    unit: "day"
                }
            }
        }
    }
])

This operation will project both the original and truncated date, which is reduced to the start of the day.

Truncation with Time Zone

db.collection.aggregate([
    {
        $project: {
            dateField: 1,
            truncatedDate: {
                $dateTrunc: {
                    date: "$dateField",
                    unit: "month",
                    timezone: "America/New_York"
                }
            }
        }
    }])

By specifying a timezone, you can ensure that the operation considers the appropriate local time for truncation.

Using the $dateToParts and $dateFromParts Operators

If you’re using an earlier version of MongoDB that doesn’t support the $dateTrunc operator, you can combine $dateToParts and $dateFromParts to achieve similar results.

db.collection.aggregate([
    {
        $addFields: {
            dateParts: {
                $dateToParts: { date: "$dateField" }
            }
        }
    },
    {
        $addFields: {
            truncatedDate: {
                $dateFromParts: {
                    year: "$dateParts.year",
                    month: "$dateParts.month",
                    day: "$dateParts.day"
                    // Ignores hour, minute, and second for truncation
                }
            }
        }
    }
])

This code will extract the parts of the date and then construct a new date object with the time set to 00:00:00.

Custom Truncation Using $project and $substr

For even older versions of MongoDB, where the above operators are not available, we fall back on basic string operations. One common approach for date truncation in such scenarios is to convert the date to a string and then manipulate that string.

db.collection.aggregate([
    {
        $project: {
            truncatedDate: {
                $substr: ["$dateField", 0, 10]
                // Extracts only up to the date part, ignores time
            }
        }
    }
])

This method might be rudimentary and lacks timezone awareness but is applicable if you are in a pinch with an older MongoDB database. It truncates the string-represented date to ‘YYYY-MM-DD’ format.

Advanced Scenarios: Truncating to Arbitrary Date Parts

In more complex scenarios, you might want to truncate to a less conventional part of a date. For example, ones that aren’t directly supported by built-in MongoDB operators, such as the nearest 5-minute interval or ten-day period.

// Example aggregation to truncate date to nearest 5-minute interval

// First, convert the date to the number of minutes since Unix epoch
{
    $project: {
        minutes: {
            $add: [
                { $minute: "$dateField" },
                { $multiply: [60, { $hour: "$dateField" }] },
                { $multiply: [1440, { $dayOfYear: "$dateField" }] },
                // ... account for year, considering leap years
            ]
        }
    }
},
// Then, calculate the truncated minutes and convert it back to a date
{
    $project: {
        truncatedDate: {
            $subtract: [
                "$dateField",
                {
                    $multiply: [
                        {
                            $mod: [
                                "$minutes",
                                5 // Truncate to 5-minute intervals
                            ]
                        },
                        60000 // conversion from minutes to milliseconds
                    ]
                }
            ]
        }
    }
}

This is an advanced example where MongoDB’s date arithmetic is used creatively to calculate the interval truncation and then a date is reconstructed with the adjusted time-related fields.

Conclusion

Truncating date values in MongoDB ranges from simple operations using the built-in $dateTrunc operator to more complex maneuvers involving date parts and string manipulation. Hopefully, this tutorial has provided you with the knowledge and confidence to manipulate date precision in MongoDB to suit your specific needs.