MongoDB $dateToString

Posted on by Ian

In MongoDB, the $dateToString aggregation pipeline operator converts a given date object to a string.

The $dateToString operator accepts either a Date, a Timestamp, or an ObjectId.

You can specify a format to use for the result by providing a format specification. The format specification can be any string literal, containing 0 or more format specifiers.

The format specification is optional from MongoDB version 4.0, when featureCompatibilityVersion is set to 4.0 or higher. Earlier versions require the format specification.

You can optionally use the timezone parameter to specify the timezone to use.

You can also use the onNull parameter to specify what to return if the date is null or missing.

Example

Suppose we have a collection called pets with the following document:

{
	"_id" : ObjectId("600631c7c8eb4369cf6ad9c8"),
	"name" : "Fetch",
	"born" : ISODate("2020-12-31T23:30:15.123Z")
}

We can run the following code to return a date string from the born field in that document.

db.pets.aggregate(
   [
     {
       $project: {
         _id: 0,
          dateString: { $dateToString: { format: "%Y-%m-%dT%H:%M:%S.%LZ", date: "$born" } }
       }
     }
   ]
)

Result:

{ "dateString" : "2020-12-31T23:30:15.123Z" }

This example uses %Y-%m-%dT%H:%M:%S.%LZ as the format specification. This happens to be the default format specification, but in this case we explicitly specified it. We can see that the date string is returned using the specified format.

Here, I used dateString as the field name to return, but this could have been anything (such as formattedDate, theDate etc).

The _id field is returned by default when using projections in MongoDB, but in this example I explicitly hid the _id field using _id: 0.

See MongoDB $dateToString Format Specifiers for a list of format specifiers available.

Default Output

As mentioned, the format specification in the previous example is the default format specification.

If you’re using MongoDB version 4.0 or above, with featureCompatibilityVersion set to 4.0 or higher (see how to view your current featureCompatibilityVersion and how to set it), you can omit the format specification if you want the date to be formatted using the above format.

Therefore, we could rewrite the previous. example to this:

db.pets.aggregate(
   [
     {
       $project: {
         _id: 0,
          dateString: { $dateToString: { date: "$born" } }
       }
     }
   ]
)

Result:

{ "dateString" : "2020-12-31T23:30:15.123Z" }

Specify a Timezone

You can specify a timezone to use for the output of the $dateToString operator.

The timezone can be specified using either the Olson timezone identifier (e.g. "Europe/London", "GMT") or the UTC offset (e.g. "+02:30", "-1030").

Olson Timezone Identifier

Here’s an example that outputs the date string in three different timezones, each using the Olson timezone IDs:

db.pets.aggregate(
   [
     {
       $project: {
         _id: 0,
          UTC: { $dateToString: { format: "%Y-%m-%dT%H:%M", date: "$born", timezone: "UTC" } },
          Honolulu: { $dateToString: { format: "%Y-%m-%dT%H:%M", date: "$born", timezone: "Pacific/Honolulu" } },
          Auckland: { $dateToString: { format: "%Y-%m-%dT%H:%M", date: "$born", timezone: "Pacific/Auckland" } }
       }
     }
   ]
).pretty()

Result:

{
	"UTC" : "2020-12-31T23:30",
	"Honolulu" : "2020-12-31T13:30",
	"Auckland" : "2021-01-01T12:30"
}

UTC Offset

Here’s an example that uses the UTC offset.

db.pets.aggregate(
   [
     {
       $project: {
         _id: 0,
          UTC: { $dateToString: { format: "%Y-%m-%dT%H:%M", date: "$born", timezone: "+00:00" } },
          Honolulu: { $dateToString: { format: "%Y-%m-%dT%H:%M", date: "$born", timezone: "-10:00" } },
          Auckland: { $dateToString: { format: "%Y-%m-%dT%H:%M", date: "$born", timezone: "+12:00" } }
       }
     }
   ]
).pretty()

Result:

{
	"UTC" : "2020-12-31T23:30",
	"Honolulu" : "2020-12-31T13:30",
	"Auckland" : "2021-01-01T11:30"
}

ISO Week Date Format

There are a few format specifiers that enable you to output the date string using the ISO 8601 format.

In particular, you can use:

Format SpecifierOutput
%GYear in ISO 8601 format
%u Day of week number in ISO 8601 format (1-Monday, 7-Sunday)
%VWeek of Year in ISO 8601 format

Suppose we have a collection called cats with the following document:

{
	"_id" : ObjectId("6008c9a5c8eb4369cf6ad9cc"),
	"name" : "Scratch",
	"born" : ISODate("2021-01-03T23:30:15.123Z")
}

We can run the following code to extract the ISO date fields from the born field in that document.

Here’s an example to demonstrate:

db.cats.aggregate(
   [
     {
       $project: {
         _id: 0,
          isoYear: { $dateToString: { format: "%G", date: "$born" } },
          isoDayOfWeek: { $dateToString: { format: "%u", date: "$born" } },
          isoWeekOfYear: { $dateToString: { format: "%V", date: "$born" } }
       }
     }
   ]
)

Result:

{ "isoYear" : "2020", "isoDayOfWeek" : "7", "isoWeekOfYear" : "53" }

In contrast, below is the same example, but using the non-ISO week date parts.

db.cats.aggregate(
   [
     {
       $project: {
         _id: 0,
          year: { $dateToString: { format: "%Y", date: "$born" } },
          dayofweek: { $dateToString: { format: "%w", date: "$born" } },
          weekofyear: { $dateToString: { format: "%U", date: "$born" } }
       }
     }
   ]
)

Result:

{ "year" : "2021", "dayofweek" : "1", "weekofyear" : "01" }

We can see that the result is completely different.

The onNull Parameter

The onNull parameter can be used to specify what to return if the date is null or doesn’t exist.

The value provided to the onNull parameter can be any valid expression.

Here’s an example:

db.pets.aggregate(
   [
     {
       $project: {
          _id: 0,
          dateString: { $dateToString: { date: null, onNull: "No date supplied" } }
       }
     }
   ]
)

Result:

{ "dateString" : "No date supplied" }

In this case, the date was null and so the output document includes the string that I provided for the onNull parameter.

Return the Date Parts from an ObjectId

You can use $dateToString to return a date string from an ObjectId.

ObjectId values are 12 byte hexadecimal values that consist of:

  • A 4 byte timestamp value, representing the ObjectId’s creation, measured in seconds since the Unix epoch.
  • A 5 byte is a random value
  • A 3 byte incrementing counter, initialised to a random value.

To recap, our first document looks like this:

{
	"_id" : ObjectId("600631c7c8eb4369cf6ad9c8"),
	"name" : "Fetch",
	"born" : ISODate("2020-12-31T23:30:15.123Z")
}

This document contains an ObjectId. We can therefore use $dateToString to return a date string, based on the date that our document was created (or more specifically, when the _id field’s ObjectId value was created).

Example:

db.pets.aggregate(
   [
     {
       $project: {
          timestamp: { $toDate: "$_id" },
          dateString: { $dateToString: { format: "%d-%m-%Y", date: "$_id" } }
       }
     }
   ]
).pretty()

Result:

{
	"_id" : ObjectId("600631c7c8eb4369cf6ad9c8"),
	"timestamp" : ISODate("2021-01-19T01:11:35Z"),
	"dateString" : "19-01-2021"
}

In this case, I decided to return just the date part (not the time part). I also switched the order of the days, months, and year to demonstrate that you can certainly do this if required.

I also used the $toDate aggregation pipeline operator to return the timestamp portion of the ObjectId.