In MongoDB, the $dateFromString aggregation pipeline operator converts a date/time string to a date object.
Example
Suppose we have a collection called foo with the following documents:
{ "_id" : 1, "bar" : "2020-12-31T23:30:25.123" }
{ "_id" : 2, "bar" : "2020-12-31" }
{ "_id" : 3, "bar" : "2020-12-31T23:30" }
All documents contain a date/time string.
We can run the following code to return a date object from the bar fields in those documents.
db.foo.aggregate([
{
$project: {
date: {
$dateFromString: {
dateString: '$bar'
}
}
}
}
])Result:
{ "_id" : 1, "date" : ISODate("2020-12-31T23:30:25.123Z") }
{ "_id" : 2, "date" : ISODate("2020-12-31T00:00:00Z") }
{ "_id" : 3, "date" : ISODate("2020-12-31T23:30:00Z") }
All date/time strings have been converted to a date object.
I’ve also changed the field name from bar to date.
Specify a Format
You can provide an optional format argument to specify the format of the date/time string that’s being provided. The format specification can be any string literal, containing 0 or more format specifiers.
The format parameter is available from MongoDB version 4.0.
The default format is %Y-%m-%dT%H:%M:%S.%LZ, which is what the previous example uses.
Suppose we insert the following document into our collection:
{ "_id" : 4, "bar" : "07/08/2020" }
In this case, the date could be the 7th day of the 8th month, or the 8th day of the 7th month, depending on the locale being used.
We can use a format specification to specify exactly which one it should be.
Example:
db.foo.aggregate([
{ $match: { _id: 4 } },
{
$project: {
date: {
$dateFromString: {
dateString: '$bar',
format: "%m/%d/%Y"
}
}
}
}
])Result:
{ "_id" : 4, "date" : ISODate("2020-07-08T00:00:00Z") }In this case, we specified that it’s the 8th day of the 7th month.
Here it is again, but this time we swap the day and month around.
db.foo.aggregate([
{ $match: { _id: 4 } },
{
$project: {
date: {
$dateFromString: {
dateString: '$bar',
format: "%d/%m/%Y"
}
}
}
}
])Result:
{ "_id" : 4, "date" : ISODate("2020-08-07T00:00:00Z") }
This time it is interpreted as the 7th day of the 8th month.
See MongoDB $dateFromString Format Specifiers for a list of valid format specifiers.
ISO Week Date Format
There are a few format specifiers that enable you to specify dates using 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 document that looks like this:
{ "_id" : 5, "bar" : "7-8-2020" }
We could interpret that date as being the 7th day of the ISO week, followed by the 8th ISO week of the year, followed by the year.
Like this:
db.foo.aggregate([
{ $match: { _id: 5 } },
{
$project: {
date: {
$dateFromString: {
dateString: '$bar',
format: "%u-%V-%G"
}
}
}
}
])Result:
{ "_id" : 5, "date" : ISODate("2020-02-23T00:00:00Z") }
Specify a Timezone
You can specify a timezone to use with the $dateFromString 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.foo.aggregate([
{ $match: { _id: 1 } },
{
$project: {
utc: {
$dateFromString: {
dateString: '$bar',
timezone: "UTC"
}
},
honolulu: {
$dateFromString: {
dateString: '$bar',
timezone: "Pacific/Honolulu"
}
},
auckland: {
$dateFromString: {
dateString: '$bar',
timezone: "Pacific/Auckland"
}
}
}
}
]).pretty()Result:
{
"_id" : 1,
"utc" : ISODate("2020-12-31T23:30:25.123Z"),
"honolulu" : ISODate("2021-01-01T09:30:25.123Z"),
"auckland" : ISODate("2020-12-31T10:30:25.123Z")
}
UTC Offset
Here’s an example that uses the UTC offset.
db.foo.aggregate([
{ $match: { _id: 1 } },
{
$project: {
"date+00:00": {
$dateFromString: {
dateString: '$bar',
timezone: "+00:00"
}
},
"date-10:00": {
$dateFromString: {
dateString: '$bar',
timezone: "-10:00"
}
},
"date+12:00": {
$dateFromString: {
dateString: '$bar',
timezone: "+12:00"
}
}
}
}
]).pretty()Result:
{
"_id" : 1,
"date+00:00" : ISODate("2020-12-31T23:30:25.123Z"),
"date-10:00" : ISODate("2021-01-01T09:30:25.123Z"),
"date+12:00" : ISODate("2020-12-31T11:30:25.123Z")
}
If you use the timezone parameter, the date string cannot be appended with a Z to indicate Zulu time (UTC timezone). For example, the date string can’t be 2020-12-31T23:30:25.123Z when using the timezone parameter.
Also, don’t include timezone information in the date string when using the timezone parameter.
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.
Suppose we have a document like this:
{ "_id" : 6, "bar" : null }We could use onNull in the following way:
db.foo.aggregate([
{ $match: { _id: 6 } },
{
$project: {
date: {
$dateFromString: {
dateString: '$bar',
onNull: "No valid date was supplied"
}
}
}
}
])Result:
{ "_id" : 6, "date" : "No valid date was supplied" }
In this case, the date was null and so the output document includes the string that I provided for the onNull parameter.
The onError Parameter
You can optionally use the onError parameter to provide an expression to output in the event an error occurs.
Suppose our collection contains the following document:
{ "_id" : 7, "bar" : "21st Dec, 2030" }
Even though there’s a date in the bar field, it’s not a valid date/time string, and it will therefore cause an error if we use dateFromString to try to convert it to a date object.
Example of error:
db.foo.aggregate([
{ $match: { _id: 7 } },
{
$project: {
date: {
$dateFromString: {
dateString: '$bar'
}
}
}
}
])Result:
Error: command failed: {
"ok" : 0,
"errmsg" : "an incomplete date/time string has been found, with elements missing: \"21st Dec, 2030\"",
"code" : 241,
"codeName" : "ConversionFailure"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1046:12
@(shell):1:1
That’s a nasty looking error.
We can use the onError parameter to make it look nicer:
db.foo.aggregate([
{ $match: { _id: 7 } },
{
$project: {
date: {
$dateFromString: {
dateString: '$bar',
onError: "An error occurred while parsing the date string"
}
}
}
}
])Result:
{ "_id" : 7, "date" : "An error occurred while parsing the date string" }
Seeing as the onNull and onError parameters allow us to return the actual documents, they allow us to return multiple documents, without worrying about one bad document halting the whole operation.
Example:
db.foo.aggregate([
{
$project: {
date: {
$dateFromString: {
dateString: '$bar',
onNull: "The date was either empty or null",
onError: "An error occurred while parsing the date string"
}
}
}
}
])Result:
{ "_id" : 1, "date" : ISODate("2020-12-31T23:30:25.123Z") }
{ "_id" : 2, "date" : ISODate("2020-12-31T00:00:00Z") }
{ "_id" : 3, "date" : ISODate("2020-12-31T23:30:00Z") }
{ "_id" : 4, "date" : ISODate("2020-07-08T00:00:00Z") }
{ "_id" : 5, "date" : ISODate("2020-08-07T00:00:00Z") }
{ "_id" : 6, "date" : "The date was either empty or null" }
{ "_id" : 7, "date" : "An error occurred while parsing the date string" }