MongoDB: Using $dateFromString to convert string to date

Introduction

Data manipulation often requires the conversion of strings into date objects. In MongoDB, dates are a fundamental data type which is extensively used for timestamps, logging, and carrying out date arithmetic. The $dateFromString aggregation operator provides a seamless way to convert date and time in string format to MongoDB’s native date format. In this tutorial, we’ll explore how to use $dateFromString through various examples that range from basic to advanced. You’ll learn how to apply this operator to manipulate and query pieces of date-based information effectively.

Getting Started with $dateFromString

At its simplest, $dateFromString takes a string that represents a date and converts it into a MongoDB date object. To use it in your aggregation pipeline, you’ll embed it within your $project or $addFields stage. Here’s its basic syntax:

{
    $dateFromString: {
        dateString: 'YYYY-MM-DD', // date in string format
        format: 'optional format', // specifies the format of dateString
        timezone: 'optional timezone', // adjusts the time value for time zone
        onNull: 'what to do if null' // actions performed if dateString is null or missing
    }
}

Let’s look at the most straightforward example:

db.events.aggregate([
    {
        $addFields: {
            eventDate: {
                $dateFromString: {
                    dateString: '$eventDateString'
                }
            }
        }
    }
]);

In this example, the field eventDateString is converted from a string to a date object and stored as eventDate.

Working with Different Date Formats

MongoDB’s $dateFromString operator allows you to work with a variety of date formats. To specify the format of your date string, use the format field:

db.sales.aggregate([
    {
        $project: {
            saleDate: {
                $dateFromString: {
                    dateString: '$saleDateString',
                    format: '%Y-%m-%d %H:%M:%S'
                }
            }
        }
    }
]);

This pipeline will parse the saleDateString from a string like ‘2021-03-15 08:30:00’ into a MongoDB date object.

Example with a Different Date Structure

Consider a situation where your date string is organized differently:

db.logData.aggregate([
    {
        $project: {
            timestamp: {
                $dateFromString: {
                    dateString: '$logDate',
                    format: '%d/%m/%Y %H:%M:%S'
                }
            }
        }
    }
]);

The given format matches the structure ’15/03/2021 8:30:00′, and the output will be a MongoDB date object representing that same moment in time.

Dealing with Timezones

When working with global applications, it’s important to take note of timezones. $dateFromString has a timezone parameter that allows you to specify the timezone of your dateString:

db.userActions.aggregate([
    {
        $project: {
            actionDate: {
                $dateFromString: {
                    dateString: '$actionTimeString',
                    format: '%Y-%m-%dT%H:%M:%S.%LZ',
                    timezone: 'America/New_York'
                }
            }
        }
    }
]);

This will convert the time in your string relative to the New York Timezone.

Handling Null or Missing Values

There might be scenarios where the dateString could be null or missing. $dateFromString caters to this situation through the onNull parameter :

db.reservations.aggregate([
    {
        $project: {
            reservationDate: {
                $dateFromString: {
                    dateString: '$reservationTimeString',
                    onNull: 'No Reservation Date'
                }
            }
        }
    }
]);

If reservationTimeString is null or missing, reservationDate will be set to the string ‘No Reservation Date’.

Advanced Usage: Compound Document Fields

Sometimes, datetime components might be spread across various fields. You can concatenate these fields to form a dateString:

db.concerts.aggregate([
    {
        $addFields: {
            fullDate: {
                $dateFromString: {
                    dateString: {
                        $concat: ['$dateField', ' ', '$timeField']
                    }
                }
            }
        }
    }
]);

This compound field fullDate will provide you with a single date object based on individual date and time strings.

Conclusion

Throughout this guide, we’ve seen how $dateFromString can be effectively utilized in MongoDB to convert string representations of dates to actual date objects. This powerful operator can be indispensable when dealing with a variety of date formats, time zones, and null cases. With these examples and principles in mind, you should be well-equipped to handle date conversions in your MongoDB queries.