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 Specifier | Output |
---|---|
%G | Year in ISO 8601 format |
%u | Day of week number in ISO 8601 format (1-Monday, 7-Sunday) |
%V | Week 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.