$dateFromString (aggregation)
On this page
Definition
$dateFromString
Converts a date/time string to a date object.
The
$dateFromString
expression has the following syntax:{ $dateFromString: { dateString: <dateStringExpression>, format: <formatStringExpression>, timezone: <tzExpression>, onError: <onErrorExpression>, onNull: <onNullExpression> } } The
$dateFromString
takes a document with the following fields:FieldDescriptiondateString
The date/time string to convert to a date object. See Date for more information on date/time formats.
If specifying the
timezone
option to the operator, do not include time zone information in thedateString
.format
Optional. The date format specification of the
dateString
. Theformat
can be any expression that evaluates to a string literal, containing 0 or more format specifiers. For a list of specifiers available, see Format Specifiers.If unspecified,
$dateFromString
uses"%Y-%m-%dT%H:%M:%S.%LZ"
as the default format but accepts a variety of formats and attempts to parse thedateString
if possible.timezone
Optional. The time zone to use to format the date.
If the
dateString
argument is formatted like '2017-02-08T12:10:40.787Z', in which the 'Z' at the end indicates Zulu time (UTC time zone), you cannot specify thetimezone
argument.<timezone>
allows for the following options and expressions that evaluate to them:an Olson Timezone Identifier, such as
"Europe/London"
or"America/New_York"
, ora UTC offset in the form:
+/-[hh]:[mm]
, e.g."+04:45"
, or+/-[hh][mm]
, e.g."-0530"
, or+/-[hh]
, e.g."+03"
, or
The strings
"Z"
,"UTC"
, or"GMT"
For more information on expressions, see Expressions.
onError
Optional. If
$dateFromString
encounters an error while parsing the givendateString
, it outputs the result value of the providedonError
expression. This result value can be of any type.If you do not specify
onError
,$dateFromString
throws an error if it cannot parsedateString
.onNull
Optional. If the
dateString
provided to$dateFromString
isnull
or missing, it outputs the result value of the providedonNull
expression. This result value can be of any type.If you do not specify
onNull
anddateString
isnull
or missing, then$dateFromString
outputsnull
.
Behavior
Example | Results | ||||
---|---|---|---|---|---|
|
| ||||
|
| ||||
|
| ||||
|
| ||||
|
| ||||
|
|
Format Specifiers
The following format specifiers are available for use in the
<formatString>
:
Specifiers | Description | Possible Values |
---|---|---|
| Day of Month (2 digits, zero padded) |
|
| Year in ISO 8601 format |
|
| Hour (2 digits, zero padded, 24-hour clock) |
|
| Millisecond (3 digits, zero padded) |
|
| Month (2 digits, zero padded) |
|
| Minute (2 digits, zero padded) |
|
| Second (2 digits, zero padded) |
|
| Day of week number in ISO 8601 format (1-Monday, 7-Sunday) |
|
| Week of Year in ISO 8601 format |
|
| Year (4 digits, zero padded) |
|
| The timezone offset from UTC. |
|
| The minutes offset from UTC as a number. For example, if the
timezone offset ( |
|
| Percent Character as a Literal |
|
Examples
Converting Dates
Consider a collection logmessages
that contains the following
documents with dates.
{ _id: 1, date: "2017-02-08T12:10:40.787", timezone: "America/New_York", message: "Step 1: Started" }, { _id: 2, date: "2017-02-08", timezone: "-05:00", message: "Step 1: Ended" }, { _id: 3, message: " Step 1: Ended " }, { _id: 4, date: "2017-02-09", timezone: "Europe/London", message: "Step 2: Started"}, { _id: 5, date: "2017-02-09T03:35:02.055", timezone: "+0530", message: "Step 2: In Progress"}
The following aggregation uses $dateFromString to convert the date
value
to a date object:
db.logmessages.aggregate( [ { $project: { date: { $dateFromString: { dateString: '$date', timezone: 'America/New_York' } } } } ] )
The above aggregation returns the following documents and converts each date
field
to the Eastern Time Zone:
{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") } { "_id" : 2, "date" : ISODate("2017-02-08T05:00:00Z") } { "_id" : 3, "date" : null } { "_id" : 4, "date" : ISODate("2017-02-09T05:00:00Z") } { "_id" : 5, "date" : ISODate("2017-02-09T08:35:02.055Z") }
The timezone
argument can also be provided through a document field instead of a
hard coded argument. For example:
db.logmessages.aggregate( [ { $project: { date: { $dateFromString: { dateString: '$date', timezone: '$timezone' } } } } ] )
The above aggregation returns the following documents and converts each date
field
to their respective UTC representations.
{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") } { "_id" : 2, "date" : ISODate("2017-02-08T05:00:00Z") } { "_id" : 3, "date" : null } { "_id" : 4, "date" : ISODate("2017-02-09T00:00:00Z") } { "_id" : 5, "date" : ISODate("2017-02-08T22:05:02.055Z") }
onError
If your collection contains documents with unparsable date strings,
$dateFromString
throws an error unless you provide an
aggregation expression to the optional
onError
parameter.
For example, given a collection dates
with the following
documents:
{ "_id" : 1, "date" : "2017-02-08T12:10:40.787", timezone: "America/New_York" }, { "_id" : 2, "date" : "20177-02-09T03:35:02.055", timezone: "America/New_York" }
You can use the onError
parameter to return the invalid date in
its original string form:
db.dates.aggregate( [ { $project: { date: { $dateFromString: { dateString: '$date', timezone: '$timezone', onError: '$date' } } } } ] )
This returns the following documents:
{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") } { "_id" : 2, "date" : "20177-02-09T03:35:02.055" }
onNull
If your collection contains documents with null
date strings,
$dateFromString
returns null
unless you provide an
aggregation expression to the optional
onNull
parameter.
For example, given a collection dates
with the following
documents:
{ "_id" : 1, "date" : "2017-02-08T12:10:40.787", timezone: "America/New_York" }, { "_id" : 2, "date" : null, timezone: "America/New_York" }
You can use the onNull
parameter to have $dateFromString
return a date representing the unix epoch instead of null
:
db.dates.aggregate( [ { $project: { date: { $dateFromString: { dateString: '$date', timezone: '$timezone', onNull: new Date(0) } } } } ] )
This returns the following documents:
{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") } { "_id" : 2, "date" : ISODate("1970-01-01T00:00:00Z") }