Navigation

$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>
} }

The $dateFromString takes a document with the following fields:

Field Description
dateString

The date/time string to convert to a date object. See Date for more information on date/time formats.

Note

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

format

Optional. The date format specification of the dateString. The format 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.

New in version 4.0.

timezone

Optional. 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 Zulu time (UTC time zone), you cannot specify the timezone 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", 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, see Expressions.

If the dateStringExpression or tzExpression evaluate to null, missing or undefined, the result of the $dateFromString expression is null.

See also

$toDate and $convert

Behavior

Example Results
{ $dateFromString: {
    dateString: "2017-02-08T12:10:40.787"
} }
ISODate("2017-02-08T12:10:40.787Z")
{ $dateFromString: {
     dateString: "2017-02-08T12:10:40.787",
     timezone: "America/New_York"
} }
ISODate("2017-02-08T17:10:40.787Z")
{ $dateFromString: {
     dateString: "2017-02-08"
} }
ISODate("2017-02-08T00:00:00Z")
{ $dateFromString: {
     dateString: "06-15-2018",
     format: "%m-%d-%Y"
} }
ISODate("2018-06-15T00:00:00Z")
{ $dateFromString: {
     dateString: "15-06-2018",
     format: "%d-%m-%Y"
} }
ISODate("2018-06-15T00:00:00Z")

Example

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") }

Format Specifiers

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

Specifiers Description Possible Values
%d Day of Month (2 digits, zero padded) 01-31
%G Year in ISO 8601 format 0000-9999
%H Hour (2 digits, zero padded, 24-hour clock) 00-23
%L Millisecond (3 digits, zero padded) 000-999
%m Month (2 digits, zero padded) 01-12
%M Minute (2 digits, zero padded) 00-59
%S Second (2 digits, zero padded) 00-60
%u Day of week number in ISO 8601 format (1-Monday, 7-Sunday) 1-7
%V Week of Year in ISO 8601 format 1-53
%Y Year (4 digits, zero padded) 0000-9999
%z The timezone offset from UTC. +/-[hh][mm]
%Z The minutes offset from UTC as a number. For example, if the timezone offset (+/-[hhmm]) was +0445, the minutes offset is +285. +/-mmm
%% Percent Character as a Literal %