$dateFromString (aggregation)

Definition

• $dateFromString

New in version 3.6.

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:

FieldDescriptiondateStringThe date/time string to convert to a date object. SeeDate formore information on date/time formats.

Note

If specifying the timezone option to the operator, do not includetime zone information in the dateString.

formatOptional. The date format specification of thedateString. The format can be any expressionthat evaluates to a string literal,containing 0 or more format specifiers. For a list ofspecifiers available, seeFormat Specifiers.

If unspecified, $dateFromString uses"%Y-%m-%dT%H:%M:%S.%LZ" as the default format.

New in version 4.0.

timezoneOptional. The time zone to use to format the date.

Note

If the dateString argument is formatted like‘2017-02-08T12:10:40.787Z’, in which the ‘Z’ at the end indicates Zulutime (UTC time zone), you cannot specify the timezone argument.

<timezone> allows for the following options and expressionsthat evaluate to them:

• an Olson Timezone Identifier,such as "Europe/London" or "America/New_York", or
• a 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, seeExpressions.onErrorOptional. If $dateFromString encounters an error whileparsing the given dateString, it outputs the result valueof the provided onError expression.This result value can be of any type.

If you do not specify onError, $dateFromStringthrows an error if it cannot parse dateString.onNullOptional. If the dateString provided to$dateFromString is null or missing, it outputsthe result value of the provided onNullexpression.This result value can be of any type.

If you do not specify onNull and dateString is nullor missing, then $dateFromString outputs null.

See also

$toDate and $convert

Behavior

{ $dateFromString: {    dateString: "2017-02-08T12:10:40.787"} }

{ $dateFromString: {     dateString: "2017-02-08T12:10:40.787",     timezone: "America/New_York"} }

{ $dateFromString: {     dateString: "2017-02-08"} }

{ $dateFromString: {     dateString: "06-15-2018",     format: "%m-%d-%Y"} }

{ $dateFromString: {     dateString: "15-06-2018",     format: "%d-%m-%Y"} }

Format Specifiers

The following format specifiers are available for use in the<formatString>:

Examples

Converting Dates

Consider a collection logmessages that contains the followingdocuments 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 valueto 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 fieldto 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 ahard 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 fieldto 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 anaggregation expression to the optionalonError parameter.

For example, given a collection dates with the followingdocuments:

{ "_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 inits 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 anaggregation expression to the optionalonNull parameter.

For example, given a collection dates with the followingdocuments:

{ "_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 $dateFromStringreturn 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") }